Home » Oracle SQL » BITMAP Index in Oracle

BITMAP Index in Oracle

Bitmap Indexes are used for indexing columns which have low cardinality.

Before we begin understanding about Bitmap indexes, lets revisit the concept of indexes and why we create them.

An index in Oracle helps us in fetching data from tables much more efficiently. We create index when these conditions are met:

The column contains a lot of rows with wide variety of values.
Column is used in WHERE conditions in queries many times.
The table is large and most of our queries retrieve less than 5% of total rows in table.

When these conditions are met, it makes sense to create an index to fetch these results quickly with the help of index, instead of going through the entire table row by row.

But we may across columns which have very low cardinality, i.e, unique set of values. For example, Gender column which contains only ‘M’ and ‘F’ or US_Citizen column which only contains ‘Y’ or ‘N’.

When we index these columns, the queries still fetch anywhere between 20% to 80% of entire rows in table and it does not make sense to create a regular index. For such cases, we use BITMAP index.

Bitmap Index

Bitmap Index is a special index which converts data in column into bits and stores it in database similarly.

Let’s take Interns Table for example:

When we create Bitmap Index on ‘Gender’ and ‘ US_Citizen’ column this is how it looks in background:

101    Walter  White   9000    200 M   Y
102    Jesse   Pinkman 15000   300 M   Y
103    Gus Fring   12000   100 M   N
104    Jane    Margoli 6000    200 F   Y
105    Skyler  White   14000   50  F   N
106    Saul    Goodman 15000   300 M   N
107    Walter  Badman  14000   250 M   N

--Gender Column
Row No. 1  2  3  4  5  6  7
M       1  1  1  0  0  1  1
F       0  0  0  1  1  0  0

--US Citizen Column
Row No. 1  2  3  4  5  6  7
Y       1  1  0  1  0  0  0
N       0  0  1  0  1  1  1

Oracle converts information in these columns into binary bits and each time we write a query using this column, Oracle just checks the rows for which the condition is TRUE and returns those values.

Syntax

The syntax for creating BITMAP index is as follows:

CREATE BITMAP INDEX index_name
ON table_name(column_1, [column_2..]);

Here,

index_name – It is the name of index that we want to create.

table_name – Name of table on which index needs to be created.

column_1, column_2 – It is the name of column(s) on which we create our bitmap index. The columns need to have low cardinality.

Create Bitmap Index

Let’s create a bitmap index on intern_table using these queries:

CREATE BITMAP INDEX bit_idx ON intern_table(US_citizen);

Each time we query the interns based on their citizenship, Oracle will use index instead of doing a full table scan.

Explain Plan

This can be seen by querying the execution plan for a query fetching data based on ‘US_citizen’ column:

EXPLAIN PLAN FOR 
SELECT 
    *
FROM
    intern_table
WHERE
    us_citizen = 'Y';


SELECT * FROM table (dbms_xplan.display)

Currently, we can only use Bitmap Indexes in Enterprise edition of Oracle and not Express Edition.

Data Dictionary View on Bitmap Index

The following query selects all the important columns from data dictionary views to give us important information about Bitmap indexes at one place.

SELECT ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND lower(ic.table_name) = 'intern_table';