Index Techniques   «Prev  Next»

When to Use Bitmap Indexes

This page describes aspects of indexing that you must evaluate when considering whether to use bitmap indexing on a given table:
  1. performance,
  2. storage, and
  3. maintenance.

Performance Considerations

Bitmap indexes can substantially improve performance of queries with the following characteristics:
  1. The WHERE clause contains multiple predicates on low- or medium-cardinality columns
  2. The individual predicates on these low- or medium-cardinality columns select a large number of rows
  3. Bitmap indexes have been created on some or all of these low- or medium-cardinality columns
  4. The tables being queried contain many rows

You can use multiple bitmap indexes to evaluate the conditions on a single table. Bitmap indexes are thus highly advantageous for complex ad hoc queries that contain lengthy WHERE clauses. Bitmap indexes can also provide optimal performance for aggregate queries and for optimizing joins in star schemas.

Select appropriate Oracle indexes

  1. Cardinality: The number of distinct values in a column
  2. Bitmap: A binary array method for indexing
  3. B-tree: An Oracle index structure for columns with many distinct values
  4. STAR TRANSFORMATION: An execution plan where intermediate result are kept in memory
  5. STAR: An execution plan where intermediate results are stored in temporary segments
  6. Concatenation: The process of joining column values together

Discussion on tuning with Oracle indexes

  1. When and how do you use STAR index queries over STAR transformations and vice versa?
  2. What tips and suggestions would you offer others in working with concatenated indexes, based on your experience with Oracle indexes?