7/29/2019 Oracle Bitmap Index Techniques

1/2

Oracle Bitmap Index TechniquesOracle 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 tablebeing 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. At row retrieval time, Oracle decompresses the bitmap into the RAMdata 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 ofmultiple bitmapped indexes provides a very powerful method for rapidly answering

difficult SQL queries.

For example, assume there is a motor vehicle database with numerous low-cardinalitycolumns 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 readingeach one of the 20 million rows in the base table. For example, assume we wanted to find

old blue Toyota Corollas manufactured in 1981:

selectlicense_plat_nbr

fromvehicle

wherecolor = blue

andmake = toyota

and

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 RIDlists and find the intersecting values.

Using this methodology, Oracle can provide sub-second response time when workingagainst multiple low-cardinality columns:Oracle Bitmap indexes are a very powerful Oracle feature, but they can be tricky!

You will want a bitmap index when:

1 - Table column is low cardinality - As a ROUGH guide, consider a bitmap for anyindex with less than 100 distinct values

select region, count(*) from sales group by region;2 - 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.

7/29/2019 Oracle Bitmap Index Techniques

2/2

3 - 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 include:

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 INDEXemp_bitmap_idxON 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 OracleINDEX hint:select /*+ index(emp emp_bitmap_idx) */

count(*)from

emp, deptwhere

emp.deptno = dept.deptno;