Oracle Bitmap Index Unleashed: Query Optimization Made Easy

Oracle provides this nice data retrieving mechanism for low occurrence of DML operation tables. oracle bitmap index is proposed for low cardinality columns. Those kinds of columns in a query then remind this is one option for very fast queries but be careful when adding this regarding cardinality level. It should be low to take great performance.

oracle bitmap index uses a two-dimensional array for the index and each row index as an array value. Those indexed values match with rowid and rowid directly map with information. Therefore this is completely different from the standard b-tree index. There are a lot of ways to create an index in Oracle in this article and this discusses everything about Oracle index BITMAP.

What is a BITMAP Index in Oracle?

As I explained in the introduction this is suited with columns like department name, Job Title, Gender, Marriage State, manufacturing year, etc. In these examples, there are not many distinct values the columns for the BITMAP index are a great opportunity to improve the speed of your queries.

The below image shows how looks like the bitmap index. Here you can see distinct values in the columns are in one side of the dimension array and each row is represented by a bit value in the index. There for each row needs a column on the other side.

If the bit in the bitmap is 1 then the record matches with type. If not it is 0. This performs well if you have multiple bitmap indexes on a table. Then Oracle provides rapid response for difficult queries.

Create BITMAP Index Oracle

You can create an index by using CREATE BITMAP INDEX statement. You can have multiple columns in the table. If you have multiple Bitmaps such as below Oracle can join or filter them using AND, OR, and NOT operators. If you have multiple conditions this helps in filtering data in quick time.

-- Syntax
CREATE BITMAP INDEX index_name ON Table_Name (Column_name1,Column_Name2);
--

Oracle BITMAP Index Example

First, create a simple BITMAP index on the Customers table for the gender column. It’s the best column for the BITMAP index because there can be two options as a value.

--
CREATE BITMAP INDEX customer_gendor_idx ON Customers (gender);
--

Let’s have a complex example of the BITMAP index. This is about the below columns in your customer table and there are millions of records in the customer table like below.

-- Table Columns   
key             Customer_Id    Varchar (20)      
attribute       Join_Date      DATE
attribute       Customer_Name  Varchar(100)  
attribute       Marrage_State  Varchar (10)    
attribute       Gender         Varchar (10)     
attribute       Country        Varchar (10) 
--

-- 
SELECT Customer_ID,  gender, marrage_state,  country FROM customers;
--

CUSTomer_ID    Gender     Marrage_State        Country
----------    -------    --------------     --------------------- 
       170      M         married              United States 
       180      F         married              India 
       190      M         single               Sri Lanka
      1100      F         married              Canada
      1110      F         married              Australia
      1120      M         single               Pakistan
      1130      M                              Sweden
      1140      M         married              China

First I have to note that DO NOT CREAT BITMAP INDEX on Customer_id. There is the best performance on the B-TREE index on those kinds of UNIQUEE value columns. Let’s have an SQL like the below filtering value by multiple columns but those are low cardinality columns.

-- There are three low cardinality column and those are filter by equal operator
SELECT COUNT(*) FROM customers
WHERE Marrage_State  = 'married' 
AND Gender ='M'
AND (Country = 'India' OR Country ='United States');
--

My second note is BITMAP indexes are allowed NULL values as well but B-TREE indexes are not. Therefore, this will perform well on the COUNT function. It’s because all rows are indexed in BITMAP. If we have the below indexes in the database Let’s see how Oracle filters records by using Oracle bitmap index.

-- Creat index for required column
CREATE BITMAP INDEX customer_gendor_idx ON Customers (gender);
CREATE BITMAP INDEX customer_marrage_idx ON Customers (marrage_State  );
CREATE BITMAP INDEX customer_country_idx ON Customers (Country);
--

According to the image Logical operations are used for filtering out the final dataset. There is no need for a real rowid or any other column value to filter out the final dataset. To identify the additional attributes, use the filtered bitmap and convert them to rowids.

Oracle Bitmap Conversion from rowids

Oracle converts filtered BITMAP to rowids. There is no real value mapping in the query. As I mentioned in the above section all the data filtered first and then filtered dataset maps with rowid. Those conversions happen in the database and then selected rowids map with real information in the database. That’s the reason for improving the performance if there are multiple Oracle index bitmaps.

Drop BITMAP Index syntax in Oracle

delete Oracle bitmap index is straight straightforward thing. Use the DROP INDEX statement for it as you do in the normal delete index.

-- Drop a Bitmap Index 
DROP INDEX customer_gendor_idx;
--

Oracle Index BITMAP When to Use

1. Low Cardinality Columns:  BITMAP indexes perform well on low cardinality columns like Gender, Country, State, and Department. That means if there are few distinct values then that is the best for Oracle bitmap index.
2. Equality Conditions: This is good on equality operator (=) like gender=’M’ those are not much on the range.
3. Data Warehousing: Oracle Bitmap indexes best on low DML tables. If tables are low and frequently use DML operation then that is the one option to select to BITMAP index.
4. Sparse Data: if column values have the same value in different rows this will apply to BITMAP
5. Read-Heavy Workloads: In some cases, database tables are used from a read-only perspective. In those cases, this might improve the performance.

If you table just like DMLs per second, BE CAREFUL and think again before adding the BITMAP index

According to https://www.dba-oracle.com number of distinct values in a column like below performs differently according to the distinct value in the column.

• 1 – 7 distinct key values – Queries against bitmap indexes with a low cardinality are very fast.
• 8-100 distinct key values – As the number of distinct values increases, performance decreases proportionally.

Rebuild BITMAP Index in Oracle

It is very easy to rebuild the BITMAP index by using the ALTER INDEX statement. Rebuild can improve the performance by restructuring the full BITMAP and it helps to reduce the wasted space.

-- Rebuild a Bitmap Index
 ALTER INDEX customer_gendor_idx REBUILD;
--

BITMAP Index Limit in Oracle

• Oracle Database 12c and Earlier: According to the internet 12c and earlier, Oracle supports a maximum of 30 columns in a single bitmap index. This means you can create a bitmap index on up to 30 different columns in a single index structure.
• Oracle Database 18c and Later: Starting from Oracle Database 18c, the limit increased by 2 to 32 columns. You can create a bitmap index on up to 32 different columns in a single index.

How to check the bitmap index in Oracle

Use the below query to check index weather created or not in a specific column.

--
SELECT INDEX_NAME
FROM DBA_INDEXES
WHERE TABLE_OWNER = 'your_schema' 
  AND TABLE_NAME = 'customers'
  AND INDEX_NAME = 'customer_gendor_idx';
--

Summary

BITMAP indexes are good on low cardinality columns also it is perform well if there are multiple BITMAP indexes are on your database. Bitmap Oracle index gives below advantage compared to the B-TREE indexes

• Reduced response time for tables with low distinct values.
• Reduced storage requirements compared to other indexing techniques.
• Dramatic performance gains on CPU and other hardware

Reference Oracle: https://docs.oracle.com/cd/E11882_01/server.112/e25554/indexes.htm#DWHSG8132