Summary: in this tutorial, you will learn how to use the 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 of the members table:
SELECT * FROM members WHERE gender = 'F';Code language: SQL (Structured Query Language) (sql)The 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 arrays. 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 convert each bit in the bitmap to the corresponding rowid of the members table.
The syntax for creating a bitmap index is quite simple as follows:
CREATE BITMAP INDEX index_name ON table_name(column1[,column2,...]);Code language: SQL (Structured Query Language) (sql)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);Code language: SQL (Structured Query Language) (sql)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()); Code language: SQL (Structured Query Language) (sql)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;Code language: SQL (Structured Query Language) (sql)So how low you can go with the bitmap index? A good practice is any column that 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 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 updates, especially concurrent single-row updates will cause a deadlock.
The following statement creates a new table named bitmap_index_demo:
CREATE TABLE bitmap_index_demo( id INT GENERATED BY DEFAULT AS IDENTITY, active NUMBER NOT NULL, PRIMARY KEY(id) );Code language: SQL (Structured Query Language) (sql)The following statement creates a bitmap index on the active column:
CREATE BITMAP INDEX bitmap_index_demo_active_i ON bitmap_index_demo(active);Code language: SQL (Structured Query Language) (sql)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);Code language: SQL (Structured Query Language) (sql)The following error will occur:
ORA-00060: deadlock detected while waiting for resourceCode language: SQL (Structured Query Language) (sql)In this tutorial, you have learned how to use the Oracle bitmap index to speed up the query.
