Oracle Bitmap Index Techniques
Oracle Tips by Burleson Consulting
Oracle bitmap indexes are very different
from standard b-tree indexes. In bitmap structures, a two-dimensional array is
created with one column for every row in the table being indexed. Each column
represents a distinct value within the bitmapped index. This two-dimensional
array represents each value within the index multiplied by the number of rows in
At row retrieval time, Oracle decompresses the bitmap into the RAM
data buffers so it can be rapidly scanned for matching values. These matching
values are delivered to Oracle in the form of a Row-ID list, and these Row-ID
values may directly access the required information.
The real benefit of bitmapped indexing occurs when one table includes multiple
bitmapped indexes. Each individual column may have low cardinality. The creation
of multiple bitmapped indexes provides a very powerful method for rapidly
answering difficult SQL queries.
A bitmap merge operation build ROWID lists
Using this bitmap
merge methodology, Oracle can provide sub-second response time when working
against multiple low-cardinality columns.
Also see these important notes on
Oracle bitmap index maximum distinct values
For example, assume there is a motor vehicle database with numerous
low-cardinality columns such as car_color, car_make, car_model, and car_year.
Each column contains less than 100 distinct values by themselves, and a b-tree
index would be fairly useless in a database of 20 million vehicles.
combining these indexes together in a query can provide blistering response
times a lot faster than the traditional method of reading each one of the 20
million rows in the base table. For example, assume we wanted to find old blue
Toyota Corollas manufactured in 1981:
color = ‘blue’
make = ‘toyota’
year = 1981;
Oracle uses a specialized optimizer method called a bitmapped index merge to
service this query. In a bitmapped index merge, each Row-ID, or RID, list is
built independently by using the bitmaps, and a special merge routine is used in
order to compare the RID lists and find the intersecting values.
As the number if distinct values
increases, the size of the bitmap increases exponentially, such that an
index with 100 values may perform thousands of times faster than a bitmap
index on 1,000 distinct column values.
Also, remember that bitmap indexes are only suitable for
static tables and materialized views which are updated at nigh and rebuilt
after batch row loading. If your tables are not read-only during query
time, DO NOT consider using bitmap indexes!
1 - 7 distinct key values - Queries
against bitmap indexes with a low cardinality are very fast.
8-100 distinct key values - As the
number if distinct values increases, performance decreases
100 - 10,000 distinct key values -
Over 100 distinct values, the bitmap indexes become huge and SQL
performance drops off rapidly.
- Over 10,000 distinct key values - At
this point, performance is ten times slower than an index with only 100
Oracle Bitmap indexes are a very powerful Oracle feature, but they can
You will want a bitmap index when:
1 - Table column is low cardinality - As a ROUGH guide,
consider a bitmap for any index with less than 100 distinct values
select region, count(*) from sales group by region;
The table has LOW DML - You must have low
insert./update/delete activity. Updating bitmapped indexes take
a lot of resources, and bitmapped indexes are best for largely
read-only tables and tables that are batch updated nightly.
Multiple columns - Your SQL queries reference multiple, low
cardinality values in there where clause. Oracle cost-based SQL
optimizer (CBO) will scream when you have bitmap indexes on .
Troubleshooting Oracle bitmap indexes:
Some of the most common problems when implementing bitmap indexes
1. Small table - The CBO may force a
full-table scan if your table is small!
2. Bad stats
- Make sure you always analyze the bitmap with dbms_stats
right after creation:
CREATE BITMAP INDEX
ON index_demo (gender);
exec dbms_stats.gather_index_stats(OWNNAME=>'SCOTT', INDNAME=>'EMP_BITMAP_IDX');
3. Test with a hint - To force the use
of your new bitmap index, just use a Oracle INDEX hint:
select /*+ index(emp emp_bitmap_idx) */
emp.deptno = dept.deptno;