RelationalDBDesign RelationalDBDesign 


Index Techniques   «Prev  Next»
Lesson 3Speeding Bitmapped Indexes
ObjectiveHow to increase Speedx

Increase Query Speed - Bitmapped Indexes

Speeding queries using bitmapped indexes

Let us examine how Oracle can speed queries using bitmapped indexes.
  1. The query specifies two low cardinality columns in the WHERE clause, region and type.
  2. Oracle scans the region bitmap, quickly building a list of ROWID's for all rows in the East region.
  3. Oracle scans the type bitmapped index and quickly builds a list of ROWID'S for all Partnerships.
  4. Oracle then sorts these two lists of ROWID's and returns rows where the ROW ID appears in both ROWID lists.

Bitmapped Indexes To Speed Queries

Advantages of using bitmapped indexes

Minimal Storage RequirementsIn contrast to B-tree index, a bitmapped index has minimal storage requirements
Improved response timeIn certain situations, a bitmapped index dramatically improves the response time

This is often the case where there are indexes that correspond to several conditions in a WHERE clause. A bitmap index efficiently merges these indexes, and this often dramatically improves the response time.

Improved response time for a bitmap scan

As shown below, access is much faster than a full-table scan or a B-tree index scan.
Because rows which satisfy some ( but not all) conditions are filtered out before the table itself is accessed.

access is much faster than a full-table scan or a B-tree index scan. For large tables it is often hundreds of times faster than traditional indexing methods.

Oracle bitmap Response

When to use a bitmapped index

In spite of the advantages they offer, bitmapped indexes are not applicable to every query. They are most useful when the following conditions are true:
  1. Index columns have few distinct values (less than 20)
  2. The SQL has several predicates involving bitmap indexes in the WHERE clause
  3. The SQL select statement is INDEX ONLY, meaning that reading the index without reading the table will satisfy the query.

In summary, bitmap indexes provide excellent performance, while using less storage. Because of their different performance characteristics, you should create bitmap indexes on low cardinality data, and keep B*tree indexes on high-cardinality data.
The next lesson discusses STAR index queries.

Bitmapped Indexes - Exercise

Before you continue, click the Exercise link below to complete an exercise on how to create a set of index definitions.
Bitmapped Indexes - Exercise