Bitmap Index – when to use it?


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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s