Bitmap Indexes - Part 3

Case 2: Comparison between B*Tree and Bitmap

Let us illustrate this point by identifying when a B*Tree can be used and when we should use Bitmap. Let us consider the same example of what we have done it in case 1 and see if there is any difference when we use b*tree instead of bitmap

Let us create a table for creating a b*tree index, as follows:

   create table btreedemotable as select * from bitmapdemotable where 1=0;

   create index btreeidx on btreedemotable(runtype);

Let us create the same procedure, but to populate btreedemotable.

create or replace procedure btreeproc(noofrecords in number) is

counter number;

begin

        counter := 1;

        while (counter <= noofrecords)

        loop

            insert into btreedemotable values (counter, 'a' || counter,mod(counter,2));

            counter := counter + 1;

        end loop;

        commit;

end;

alter session set timed_statistics=true;

alter session set sql_trace=true;

Please note that the row-by-row insert for a b*tree index based table works like a champ as compared to bitmap index based table. Now, we will try to select it from the table and see if there are any differences:

SQL> select * from btreedemotable where runtype=1;

25000 rows selected.

SQL> select * from bitmapdemotable where runtype=1;

25000 rows selected.

Except for the minor changes in the statistics and cost, there seems to be no big difference between the two tables having different index types. Now, let us check for the query that returns 125,000 records.