Bitmap index in a Oracle database is special type of database index which is used to reduce cardinality of columns. The columns with lower cardinality value will result in result in relatively unique values. Consider that there is a column called binary digits; this column will have only two values namely 0 and 1. The binary digits column have two unique values and the cardinality value is also 2.

The data structures in a bitmap index has a two dimensional array created with a column for all the rows present in a table. The column which uses bitmap index will have a distinct value. The product of bitmap index and number of rows present in the table will be represented by the two dimensional array. The bitmap index will be decompressed into the RAM data buffers during the row retrieval value; this process will enable the bitmap index to rapidly scan for matching values. The matching values acquired through the scanning process will be delivered to the database. The matching values are delivered in the form of Row - ID list and Row - ID values. Bitmap indexing is highly preferred when a table has multiple bitmap indexes. Each column which uses bitmap index will have low cardinality which is handy to prove complicated SQL queries. Using bitmap indexes the response time for database is lowered to sub second.

• Related: SQL & .NET Interview questions and Answers

The following SQL query is an example for creating Bitmap Index

CREATE BITMAP INDEX IX_BIKES_BIKE_MAKE
ON BIKE (BIKEMAKE);

Example for Bitmap Index

Consider that we have a database for “Bikes”. The database may contain numerous cardinality columns like bike_color, bike_model and bike_make. Each column of the table will contain more than 100 distinct values so it will omit possibility of using B-tree index and leaves our options only to bitmap index.

Combining several indexes in a query will result will quick response than the traditional query process which scans every row for the matching values. If we want to find a model from the year 2014 then the following query may be used.

select

license_plate_number

from

Bikes

where

color = “Red”

and

make = “Ducati”

and

year = 2014;

Bitmap index is used to service the query given above. During the bitmap index merge, lists like Row - ID and RID are built using the bitmaps. The RID lists are compared using a special merge routine which is used intersecting values. The bitmap size is exponentially related to the number of distinct values present in a table. The use of bitmap index is only recommended static table with materialized views.

What is Stored inside a Bitmap Index?

A bitmap index will contain the index records of unique value present in the column. The number of bits in each record will be similar to the number rows present in the table.

• Read: More Interview Questions

Why High Cardinality Columns are not recommended for Bitmap Indexes?

The columns with high cardinality value are not used for bitmap indexing because the columns will require a new data structure or index record for every unique value. The high cardinality columns will have lot of unique values and will consume a lot of space in the database. Consuming large amount of space will result in inefficient index. Hence bitmap index are not recommended for columns with high cardinality.

Difference Between Bitmap and B - Tree Index

• Bitmap index uses the keyword “Bitmap” while no keyword is used in B-Tree indexes.
• Bitmap index is used in columns with lot of duplicated values while B-tree indexes are mostly preferred for high cardinality columns.
• The internal structure of bitmap index is two dimensional while tree form structure used in b-tree index.