Oracle Bitmap Index

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.