Btree indexes vs. Bitmap indexes – the critical difference
- A single B-tree index allows you to access a small amount of data very precisely.
- It is the combination of a subset of the available bitmap indexes that offers the same degree of precision.
You should not be comparing the effectiveness of a bitmap index with the effectiveness of a b-tree index.
(Inevitably it’s a little more subtle than this – you may create some low-precision b-tree indexes to avoid foreign key locking issues,the optimizer can combine b-tree indexes, and so on - but if you start from this basis you will have a rational view about how to use bitmap indexes).
Footnote: remember, also, that bitmap indexes introduce massive concurrency issues and other maintenance overheads – if you see them in an OLTP system it’s very likely that they’re causing problems.
Update 23rd Dec 2009: I’ve written a follow-up article to this note since the point I was trying to make seemed to cause some confusion.