Friday, January 20, 2006

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.

2 Comments:

Blogger John, The Seeker said...

Don't be so harsh about bitmap index. There is a good article that addresses most of the issues you've raised in this article
http://technology.amis.nl/blog/?p=1420

9/26/2007 4:44 AM  
Blogger Tani said...

There are many methods of doing every task and while working in databases , there are always contradiction in some methods of writing scripts. As every Database designer has different angle of basis for designing scripts.

sap testing

5/19/2012 1:50 PM  

Post a Comment

<< Home