While analyzing a performance issue, i observed several insert and some update sessions waiting on TX enqueue. Further digging showed that the table involved had a BITMAP index.
It was an OLTP system and the table in question was a sort of QUEUE table having large concurrency. The DBA considered a BITMAP index to be more suitable in this case as the column (some sort of STATUS) had low cardinality. Which turned out to be a really bad decision in this case!
There are several notes and articles on the web unwrapping the myths and facts of BITMAP indexes.
The following article by Jonathan Lewis – Understanding Bitmap indexes is a must read.
So are these from Richard Foote – Bitmap Index Degradation Since 10g (Fix You), Bitmap Index Degradation After DML Prior To 10g (Beauty and the Beast)
