The Secrets of Oracle Bitmap Indexes

Overview

Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type that OLTP systems make much use of, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

Characteristic of Bitmap Indexes

For columns with very few unique values (low cardinality)

Columns that have low cardinality are good candidates (if the cardinality of a column is <= 0.1 % that the column is ideal candidate, consider also 0.2% – 1%)

Tables that have no or little insert/update are good candidates (static data in warehouse)

Stream of bits: each bit relates to a column value in a single row of table

create bitmap index person_region on person (region);

        Row     Region   North   East   West   South
        1       North        1      0      0       0
        2       East         0      1      0       0
        3       West         0      0      1       0
        4       West         0      0      1       0
        5       South       0      0      0       1
        6       North        1      0      0       0

Advantage of Bitmap Indexes

The advantages of them are that they have a highly compressed structure, making them fast to read and their structure makes it possible for the system to combine multiple indexes together for fast access to the underlying table.

Compressed indexes, like bitmap indexes, represent a trade-off between CPU usage and disk space usage. A compressed structure is faster to read from disk but takes additional CPU cycles to decompress for access - an uncompressed structure imposes a lower CPU load but requires more bandwidth to read in a short time.

One belief concerning bitmap indexes is that they are only suitable for indexing low-cardinality data. This is not necessarily true, and bitmap indexes can be used very successfully for indexing columns with many thousands of different values.

Disadvantage of Bitmap Indexes

The reason for confining bitmap indexes to data warehouses is that the overhead on maintaining them is enormous. A modification to a bitmap index requires a great deal more work on behalf of the system than a modification to a b-tree index. In addition, the concurrency for modifications on bitmap indexes is dreadful.

Bitmap Indexes and Deadlocks

Bitmap indexes are not appropriate for tables that have lots of single row DML operations (inserts) and especially concurrent single row DML operations. Deadlock situations are the result of concurrent inserts as the following example shows: Open two windows, one for Session 1 and one for Session 2

Session 1 Session 2

create table bitmap_index_demo (
value varchar2(20)
);

insert into bitmap_index_demo
select decode(mod(rownum,2),0,'M','F')
from all_objects;

create bitmap index
bitmap_index_demo_idx
on bitmap_index_demo(value);

insert into bitmap_index_demo
values ('M');
1 row created.

insert into bitmap_index_demo
values ('F');
1 row created.

insert into bitmap_index_demo
values ('F');
...... waiting ......

ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

insert into bitmap_index_demo
values ('M');
...... waiting ......