Oracle bitmap index example.

Learn What Oracle Bitmap Index is and How to Create it

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

In this tutorial, you'll learn about what Oracle bitmap index is and how to create it.

What is Oracle Bitmap Index?

In Oracle, Bitmap indexes are often used in data warehouses, which are usually containing a vast amount of data. Organizations use data warehouses for business intelligence analysis.

The data stored in the warehouse is typically read using complex queries, but the data are not continually being updated. The data might only be updated at the end of the day, week or another scheduled period.

A candidate for a bitmap index is a column that has the following properties:

  • Is referenced in many queries
  • Contains only a small range of values

Example Range of Values

  • N, S, E, W
  • Y, N
  • 1, 2, 3, 4, 5, 6, 7
  • PLACED, PENDING, SHIPPED, CANCELED

In a Bitmap index, however, a bitmap is used for each key value. The bitmap enables the database to locate a row. Each bit in the bitmap corresponds to a possible rowid. If the bit is set, that means the row with the corresponding rowid contains the key value.

How to Decide Whether to Create a Bitmap Index in Oracle?

Use the following points to guide you when is required to create a Bitmap index in Oracle:

  • The table is containing a large amount of data and whose contents are not modified very often.
  • A bitmap index should be created on columns that contain a small number of distinct values. If the number of distinct values of a column is less than 1 percent of the number of rows in the table, or the values in a column are repeated more than 100 times.
  • A column is being frequently used in the WHERE clause.

For example, a table with 1 million rows, a column with 10,000 distinct values or less is a right candidate for a bitmap index.

Creating a Bitmap Index

The following Oracle DDL statement creates a bitmap index on the order_status column of the customer_orders table.

CREATE BITMAP INDEX bi_order_status ON customer_orders(order_status);

You can find more information about the Oracle Bitmap index in the following Oracle documentation.

See also: