when to use Bitmap Index in Oracle

            Bitmap Index – when to use it?

We all are well aware with the definition of Bitmap Index in Oracle Database. Now question is when to use it!

1. Low cardinality:

When the cardinality of a column is low it's better to use Bitmap Index.(Cardinaity=>maximum different  values a column can hold).B-tree indexes are most effective for high-cardinality data: that is, data with many possible values, such as CUSTOMER_NAME or PHONE_NUMBER.

2. No or little insert/update :

 Updating bitmap indexes take a lot of resources.each index maintained by an INSERT, DELETE, or UPDATE of the indexed keys requires about three times as much resource as the actual DML operation on the table.if you INSERT into a table with three indexes, then it will be approximately 10 times slower than an INSERT into a table with no indexes.

3.Multiple bitmap indexes can be merged :

One good thing about bitmap indexes is multiple bitmap indexes can be merged and the column does not have to selective.Combining bitmap indexes on non-selective columns allows efficient AND and OR operations with a great number of rowids with minimal I/O. More than one column in the table has an index that the optimizer can use to improve performance on a table scan.