The Secrets Of Oracle Bitmap Indexes

Follows: 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); East 0 1 0

Row West 1 0 2 0 3 1 4

Region South North 0 East 0 West 0 West

North 1 0 0 0

0

1

0

0

0

0

5 6

0 1 0

South

0

North

1

Advantage of Bitmap Indexes The advantages of highly compressed to read and their for the system to together for fast table.

them are that they have a structure, making them fast structure makes it possible combine multiple indexes access to the underlying

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 lowcardinality 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 ......