Oracle Bitmap Index Tuning Oracle Tips by Burleson Consulting

Implement Bitmap Indexes

An ad-hoc query system was experiencing slow query performance. The system was read-only except for a 30-minute window at night for data loading. Inspection of the SQL showed complex combinational WHERE clauses like:

WHERE color=’BLU’ and make=’CHEVY’ and year=1997 and doors=2;

Concatenated B-tree indexes were being used and the distinct values for each of these columns numbered less than 200. By replacing the b-tree indexes with bitmap indexes, the overall performance improvement of the system was incredible.

Queries that had taken as much as three seconds to run were reduced to runtimes of under one-tenth of a second.

Be Careful with bitmap indexes!

Bitmap indexes do not update quickly, so the best approach is to only use them in database that update all rows in a nightly “batch”. In this fashion, the bitmap is dropped, changes applied to the table, and the bitmap rebuilt.
 

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.