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); --
https://ennicode.com/create-an-index-in-oracle
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
- 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.
- Equality Conditions: This is good on equality operator (=) like gender=’M’ those are not much on the range.
- 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.
- Sparse Data: if column values have the same value in different rows this will apply to BITMAP
- 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; --
https://ennicode.com/oracle-function-based-index
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