When to Use Bitmap Indexes

Question: Which aspects of indexing must you evaluate when considering whether to use bitmap indexing on a given table?
When evaluating whether to use bitmap indexing on a given table in Oracle, several key aspects need to be considered. A bitmap index is a type of database index that uses bitmaps and can provide significant performance benefits in the right context, especially in data warehousing environments. However, their application in other scenarios can be detrimental. The following aspects should be assessed:

1. Cardinality: Bitmap indexes are particularly suitable for low cardinality columns, i.e., columns with a small number of distinct values relative to the total number of rows in the table. If the column has high cardinality, a B-tree index is generally more efficient.
2. Data Distribution: Bitmap indexes are effective when the distribution of data in the column is uneven, i.e., when certain values occur much more frequently than others. Bitmap indexes can compactly represent and rapidly filter based on these frequently occurring values.
3. Concurrency and Write Operations: Bitmap indexes are not ideal in situations where there are frequent concurrent write operations (INSERT, UPDATE, DELETE) due to the locking issues. These indexes can lock many rows during updates, which can degrade performance in OLTP systems with high concurrency.
4. Read-Intensive Workloads: Bitmap indexes are best suited for read-intensive workloads, such as data warehousing and reporting systems, where queries are complex, and data is loaded in bulk rather than updated frequently.
5. Complex Queries: Bitmap indexes can significantly speed up complex queries involving multiple conditions. They allow the database to quickly combine multiple bitmap indexes using bitwise operations, which can be significantly faster than the corresponding operations with B-tree indexes.
6. Storage Space: Bitmap indexes usually require less storage space compared to B-tree indexes for low cardinality columns, which can be an important consideration if space is a constraint.
7. Index-Only Access Paths: Consider if queries on the table could benefit from index-only access paths, where the query can be resolved entirely using the index without accessing the actual table. Bitmap indexes can often support such access paths.
8. Data Warehouse Schema Design: If your schema is a star schema typical of a data warehouse, bitmap indexes on the fact table's foreign key columns can be beneficial, especially when star transformation is used.

Choosing the right indexing strategy requires a deep understanding of your data and the nature of your workload. Regularly review and tune your indexing strategy based on changes in data volume, distribution, and query patterns to ensure optimal database performance.
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.

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.

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