 |
|
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.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
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: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
 |
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. |
 |
|
