Summary: in this tutorial, you will learn how to use Oracle bitmap index for indexing columns with low cardinality.
Introduction to Oracle bitmap index
We will use the
members table created in the
CREATE INDEX tutorial for the demonstration.
The following query finds all female members from the
SELECT * FROM members WHERE gender = 'F';
gender column has two distinct values, F for female and M for male. When a column has a few distinct values, we say that this column has low cardinality.
Oracle has a special kind of index for these types of columns which is called a bitmap index.
A bitmap index is a special kind of database index which uses bitmaps or bit array. In a bitmap index, Oracle stores a bitmap for each index key. Each index key stores pointers to multiple rows.
For example, if you create a bitmap index on the
gender column of the
members table. The structure of the bitmap index looks like the following picture:
It has two separate bitmaps, one for each gender.
Oracle uses a mapping function to converts each bit in the bitmap to the corresponding rowid of the
The syntax for creating a bitmap index is quite simple a follows:
CREATE BITMAP INDEX index_name ON table_name(column1[,column2,...]);
For example, to create a bitmap index for the
gender column, you use the following statement:
CREATE BITMAP INDEX members_gender_i ON members(gender);
Now, if you query members by gender, the optimizer will consider using the bitmap index:
EXPLAIN PLAN FOR SELECT * FROM members WHERE gender = 'F'; SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
The following picture shows the execution plan:
When to use Oracle bitmap indexes
Low cardinality columns
You should use the bitmap index for the columns that have low cardinality. To find the cardinality of a column, you can use the following query:
SELECT column, COUNT(*) FROM table_name GROUP BY column;
So how low you can go with the bitmap index? A good practice is any column which has less than 100 distinct values.
Infrequently updated or read-only tables
Maintaining a bitmap index takes a lot of resources, therefore, bitmap indexes are only good for the read-only tables or tables that have infrequently updates. Therefore, you often find bitmap indexes are extensively used in the data warehouse environment.
Notice that using a bitmap index for a table that has many single row update, especially concurrent single row update will cause a deadlock.
The following statement creates a new table named
CREATE TABLE bitmap_index_demo( id INT GENERATED BY DEFAULT AS IDENTITY, active NUMBER NOT NULL, PRIMARY KEY(id) );
And the following statement creates a bitmap index on the
CREATE BITMAP INDEX bitmap_index_demo_active_i ON bitmap_index_demo(active);
Open two sessions and repeatedly execute one of the following statements in each session:
INSERT INTO bitmap_index_demo(active) VALUES(1); INSERT INTO bitmap_index_demo(active) VALUES(0);
The following error will occur:
ORA-00060: deadlock detected while waiting for resource
In this tutorial, you have learned how to use the Oracle bitmap index for speeding up the query.