Understanding Oracle Bitmap Indexes

To fully appreciate where a bitmap join index is helpful (click to read more information about bitmap join indexes), it is important to first understand a bitmap index. Bitmap indexes are most helpful in a data warehouse environment because they are generally great (fast) when you are only selecting data. A bitmap index is smaller than a b-tree index because it stores only the ROWID and a series of bits. In a bitmap index, if a bit is set, it means that a row in the corresponding ROWID (also stored) contains a key value.

For example, consider the EMP table with two new columns: gender and marital status:

 

 

The bitmaps stored may be the following (the actual storage depends on the algorithm used internally, which is more complex than this example):

 

 

As you can tell from the preceding example, finding all of the females by searching for the gender bit set to a ‘1’ in the example would be easy. You can similarly find all of those who are married or even quickly find a combination of gender and marital status. Oracle stores ranges of rows for each bitmap as well, which is why bitmaps don’t do well when you update the bitmap-indexed column (as you can lock an entire range of rows).

You should use b-tree indexes when columns are unique or near-unique; you should at least consider bitmap indexes in all other cases. Although you generally would not use a b-tree index when retrieving 40 percent of the rows in a table, using a bitmap index generally makes this task faster than doing a full table scan. Using an index in this situation is seemingly in violation of the 80/20 or 95/5 rules, which are generally to use an index when retrieving 5–20 percent or less of the data and to do a full table scan when retrieving more. Bitmap indexes are smaller and work differently than b-tree indexes, however. You can use bitmap indexes even when retrieving large percentages (20–80 percent) of a table. You can also use bitmaps to retrieve conditions based on NULLs (because NULLs are also indexed), and can be used for not-equal conditions for the same reason. The best way to find out is to test!

Bitmap Index Caveats

Bitmap indexes do not perform well in a heavy DML (UPDATE, INSERT, DELETE) environment and generally are not used in certain areas of an OLTP environment. There is a heavy cost if you are doing a lot of DML, so be very careful with this. Applying NOT NULL constraints and fixed-length columns allows bitmaps to use less storage, so a good designer is once again worth his or her weight in gold. Use the INDEX_COMBINE hint instead of the INDEX or AND_EQUAL hints for bitmap indexes. Like b-tree indexes, bitmap indexes should be rebuilt (ALTER INDEX . . . REBUILD) if there is a lot of DML (UPDATE, INSERT, DELETE) activity. Bitmaps are very good for multicolumn read-only indexes that together make a reasonably selective value but separately do not. These columns when indexed together, if often used together in a WHERE clause, are a good choice for a bitmap.