Bitmap Index

I often heard people say create a bitmap index when you need an index and when the cardinality of the data is low. How low is considered low is another thing altogether. But one important aspect that people often forget is it only works well in read-only data. If it is a table with lots of insert, update, and delete operation, you end up having to lock many rows at once for each of the DML statement.

Tom Kyte shows an excellent example the affect of this in his book “Expert Oracle Database Architecture”:

Create table t (processed_flag varchar2(1) );

Create bitmap index t_idx on t (processed_flag);

Insert into t values (‘N’);

In another session, run:

Insert into t values (‘N’);

You will see that the second session hangs until the first one does a commit.

This in essence serializes your database operation. And thus your user will ask, “why is the application so slow?” Happy debugging.