Author: Don Burleson

Oracle bitmap indexes:

Oracle bitmap indexes are very powerful feature of Oracle. They are different from standard b-tree indexes. In Oracle bitmap index 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 the table.

Matching values for bitmaps:

Oracle decompresses the bitmap into the RAM data buffers at row retrieval time so that 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.

Benefits of Bitmap Indexing:

The creation of multiple bitmapped indexes provides a very powerful method for rapidly answering difficult SQL queries. This benefit of bitmapped indexing can be seen when one table includes multiple bitmapped indexes. Each individual column may have low cardinality.

Combining Oracle bitmap indexes in a query:

Assume 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.

However, 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.

Oracle bitmapped index merge:

• Oracle uses a specialized optimizer method called a bitmapped index merge.
• 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.
• Using this methodology, Oracle can provide sub-second response time when working against multiple low-cardinality columns.

When to use Oracle bitmap indexes?

Oracle Bitmap indexes are a very powerful Oracle feature. You will want a bitmap index when table column is low cardinality, or t he table has low DML or y our SQL queries reference multiple, low cardinality values in there where clause.

Full-table scan by CBO:

The CBO may force a full-table scan if your table is small.

dbms_stats:

Make sure you always analyze the bitmap with dbms_stats right after creation:

CREATE BITMAP INDEX
emp_bitmap_idx
ON index_demo (gender);

exec dbms_stats.gather_index_stats(OWNNAME=>'SCOTT', INDNAME=>'EMP_BITMAP_IDX');

Oracle INDEX hint:

Use Oracle INDEX hint to force the use of your new bitmap index.

select /*+ index(emp emp_bitmap_idx) */
   count(*)
from
   emp, dept
where
   emp.deptno = dept.deptno;

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Monitoring Oracle Parallel Queries!!
   Zend Core To Appear - Oracle and Zend’s Partnership
   Great Tips on Table Recovery with RMAN Backup!!
   The Secrets of Protecting Data in your Database Tables!!