Generally we deal with two types of indexes . One is B-Tree Indexes and the other is Bitmap indexes.B-tree indexes have been traditional favorites for Oltp databases.if we classify the tables based on the underlying data, there are two types 1. read write tables (Huge Inserts ,Update and Deletes) 2. Read only tables - Table which are almost having static data. Aggregated tables, Dimensional tables, Fact tables, Pre Aggregation temp tables are some of the examples of Read only tables.
Bitmap indexes are highly compressed and usually take very less space when compared to B-tree indexes and are extensively used in data warehouses.
When a bitmap index can be created on a table?
Bitmap indexes are the most suitable for columns having very few unique values (very low cardinality). Ideally to choose the bitmap indexes as the right choice for a column the cardinality should be less than or equal to 0.05%.
Table should have no insert, update , delete or very few dml statements can act as good choice for bitmap indexes.
create bitmap index Person_state on person (state);
The internal storage format of a bitmap index
Row Region North East West South
1 Ohio 1 0 0 0
2 Texas 0 1 0 0
3 New mexico 0 0 1 0
4 Pennsylvan 0 0 1 0
5 Atlanta 0 0 0 1
6 South caro 1 0 0 0
Advantage of Bitmap Indexes
Due to the nature of highly compressed structure of an bitmap index, Data can be accessed much faster than traditional B-tree indexes.
Bitmap index represent a good trade-off between disk usage and Cpu cost. A bitmap index requires more cpu cycles to decompress but requires less amount of work from disk I/O.
Disadvantages of Bitmap Indexes
Bitmap Indexes are traditionally meant to be developed for data warehouses. A modification to a bitmap index requires more work than a modification to b-tree indexes.
Deadlock’s On bitmap indexes
Bitmap indexes are not suitable for large concurrent single row Dml operations. Though bitmap indexes can be used in this form
insert into <bitmap_index_table> select * from <table> .
If there are 3 or more sessions simultaneously working to insert into a table which is bitmap indexed a dead lock situation can occur with an error message shown below
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
And an other error you can face with bitmap index is
ORA-28611: bitmap index is corrupted - see trace file for diagnostics
Cause: Bitmap index might have corrupted due to validation
Action: Rebuild the Bitmap index. Like B-tree index you cannot build a bitmap index online. Try alter index <bitmap_index_demo> rebuild;
