When to use bitmap indexes has always been a bit of mystery to me. I’ve read lots and lots of stuff on them but somehow the precise implemntation has always been a bit of a black art, and in terms of updating, they seemed to carry a huge burden and slowed our ETL down a lot.
So imagine my excitement when today I finally stumbled across something in the Oracle Database Data Warehousing Guide that not only verified what I’ve found by trial and error over time, but also enlightened me about several other things in the process.
If you’re an Oracle site then there are some great online resources here. If you’re not, then sorry 
So here is the gist of what I’ve learned over the last couple of years backed up by evidence from the vendor themselves.
Bitmap indexes are really useful in a data warehouse environment (but not an OLTP one). They reduce the response time for queries and also take up a fraction of the size compared to traditional B-tree indexes.
They are particularly good for queries that contain multiple conditions in the WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.
- It is usually best to use single-column bitmap indexes, Oracle can combine them
- When creating bitmap indexes, you should use NOLOGGING and COMPUTE statistics
- Bitmap indexes are usually easier to destroy and re-create than to maintain
- Bitmap indexes work best on columns with low numbers of distinct values (cardinality)
- Use B-Tree indexes on unique or near-unique columns
- Consider Join Indexes which might negate the need for physical SQL joins
