Bitmap/B-Tree Index (Difference, Advantages)

A traditional B-Tree (balanced tree) index stores the key values and pointers in an inverted tree structure. The key to good B-Tree index performance is to build the index on columns having a lot of different values. Oracle describes this as "good selectivity" Oracle is able to quickly bypass rows that do not meet the search criteria when searching through indexes built on columns having a high degree of selectivity.

Conversely, bitmapped indexes perform better when the selectivity of an index is poor. The fewer different values a bitmapped index contains, the better it will perform.

Bitmap indexes, in certain situations, can provide impressive performance benefits. Bitmapped indexes are most appropriate for complex and ad-hoc queries that contain lengthy WHERE clauses on columns that have a limited number of different values (poor selectivity).

Standard B-tree indexes are most effective for columns containing a high number of different values (good selectivity) and bitmapped indexes are most appropriate for columns with a limited number (poor selectivity) of possible values.