Bitmap Indexes - Part 4

SQL> select * from bitmapdemotable where runtype=1;

125000 rows selected.

SQL> select * from btreedemotable where runtype=1;

125000 rows selected.

This states that as far as the query is concerned, there is no difference between the query results on the tables that actually uses btree index and bitmap index. We have considered the scenario where we have two distinct values for run type. Now, let us create the similar type of indexes in another column in the same table and check. Also, we will drop the existing indexes on the runtype column.

drop index btreeidx;

drop index bitmapidx;

To create the indexes on runname column:

create index runnamebtree on btreedemotable(runname);

create bitmap index runnamebitmap on bitmapdemotable(runname);

exec DBMS_STATS.gather_index_stats('HARI', 'RUNNAMEBTREE');

exec DBMS_STATS.gather_index_stats('HARI', 'RUNNAMEBITMAP');

SQL> select * from bitmapdemotable where runname='a1000';

SQL> select * from btreedemotable where runname='a1000';

I actually tried to insert into the tables (with index on runname) and found the following:

SQL> insert into btreedemotable select * from btreedemotable;

250000 rows created.

SQL> insert into bitmapdemotable select * from bitmapdemotable;

250000 rows created.

Please notice the huge difference between the tables having different indexes on the similar column.

To conclude:

a)     There is no much difference between b*tree and bitmap as far as the query execution is concerned. Both are exhibiting similar statistics

b)     The difference comes when we try to perform DML operations on the indexes.