Oracle Bitmap Indexes Limitations/Restrictions

Overview

Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type that OLTP systems make much use of, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

Characteristic of Bitmap Indexes

• For columns with very few unique values (low cardinality)

Columns that have low cardinality are good candidates (if the cardinality of a column is <= 0.1 %  that the column is ideal candidate, consider also 0.2% – 1%)

• Tables that have no or little insert/update are good candidates (static data in warehouse)
   
• Stream of bits: each bit relates to a column value in a single row of table

create bitmap index person_region on person (region);

        Row     Region   North   East   West   South
        1       North        1      0      0       0
        2       East         0      1      0       0
        3       West         0      0      1       0
        4       West         0      0      1       0
        5       South        0      0      0       1                            6       North        1      0      0       0 

Advantage of Bitmap Indexes

The advantages of them are that they have a highly compressed structure, making them fast to read and their structure makes it possible for the system to combine multiple indexes together for fast access to the underlying table.

Compressed indexes, like bitmap indexes, represent a trade-off between CPU usage and disk space usage. A compressed structure is faster to read from disk but takes additional CPU cycles to decompress for access - an uncompressed structure imposes a lower CPU load but requires more bandwidth to read in a short time.

One belief concerning bitmap indexes is that they are only suitable for indexing low-cardinality data. This is not necessarily true, and bitmap indexes can be used very successfully for indexing columns with many thousands of different values.

Disadvantage of Bitmap Indexes

The reason for confining bitmap indexes to data warehouses is that the overhead on maintaining them is enormous. A modification to a bitmap index requires a great deal more work on behalf of the system than a modification to a b-tree index. In addition, the concurrency for modifications on bitmap indexes is dreadful. 

Bitmap Indexes and Deadlocks

Bitmap indexes are not appropriate for tables that have lots of single row DML operations (inserts) and especially concurrent single row DML operations. Deadlock situations are the result of concurrent inserts as the following example shows: Open two windows, one for Session 1 and one for Session 2

Session 1	Session 2
create table bitmap_index_demo (   value varchar2(20) );
insert into bitmap_index_demo select decode(mod(rownum,2),0,'M','F')   from all_objects;
create bitmap index   bitmap_index_demo_idx   on bitmap_index_demo(value);
insert into bitmap_index_demo   values ('M'); 1 row created.
	insert into bitmap_index_demo   values ('F'); 1 row created.
insert into bitmap_index_demo   values ('F'); ...... waiting ......
ERROR at line 1: ORA-00060: deadlock detected while waiting for resource	insert into bitmap_index_demo   values ('M'); ...... waiting ......

Why Bitmap not suitable for Frequently Updating Columns of Table 

Bitmap should not be created on a table or column of a table which suffer more DML operations. I will demonstrate some cases where DML operation in one session on Bitmap index column affected or hanged the DML operation on other session. We will see how column with Bitmap index got locked.

First, create a table and create Bitmap index on it.
CREATE TABLE Bitmap_Demo ( EmpId number, sex char2(1));

INSERT INTO Bitmap_Demo
SELECT emp_btmp.NEXTVAL, CASE WHEN DBMS_RANDOM.VALUE(1,10)>5 THEN 'M' ELSE 'F' END 
FROM USER_OBJECTS;


CREATE BITMAP INDEX BTMP_EMP_IND ON BITMAP_DEMO(SEX);

Test case 1:  
Update Sex on EmpID 6 ( Previous Value: M, New Value: M) and check update of sex on another M employee

Session 1:
UPDATE  Bitmap_Demo SET SEX = 'M' where EmpId = 6;

Session 2:
SQL> UPDATE  Bitmap_Demo SET Sex = 'M' WHERE EmpId = 7;

1 row updated.

SQL> UPDATE  Bitmap_Demo SET Sex = 'F' WHERE EmpId = 7;

1 row updated.

SQL> INSERT INTO Bitmap_Demo VALUES(emp_btmp.NEXTVAL, 'F');

1 row created.

SQL> INSERT INTO Bitmap_Demo VALUES(emp_btmp.NEXTVAL, 'M');

1 row created.

It allowed all operations.

Test case 2:  
Update Sex on EmpID 6 ( Previous Value: M; New Value: F) 

Session 1:
UPDATE  Bitmap_Demo SET Sex = 'F' WHERE EmpId = 6;

Session 2:
UPDATE  Bitmap_Demo SET Sex = 'M' WHERE EmpId = 7;
This statement got executed.

INSERT INTO Bitmap_Demo VALUES(emp_btmp.NEXTVAL, 'F');
This statement hanged and waited for ROLLBACK, COMMIT operation on Session1.

UPDATE  Bitmap_Demo SET Sex = 'F' WHERE EmpId = 7;
This statement hanged and waited for ROLLBACK, COMMIT operation on Session1.

INSERT INTO Bitmap_Demo VALUES(emp_btmp.NEXTVAL, 'M');
This statement hanged and waited for ROLLBACK, COMMIT operation on Session1.

Test case 3:  
Insert new employee with Sex M 

Session 1:

INSERT INTO Bitmap_Demo VALUES(emp_btmp.NEXTVAL, 'M');

Session 2:

INSERT INTO Bitmap_Demo VALUES(emp_btmp.NEXTVAL, 'F');

It is allowed to execute.

INSERT INTO Bitmap_Demo VALUES(emp_btmp.NEXTVAL, 'M');

New data insert with same Sex as in session 1. It hanged and waited for ROLLBACK, COMMIT operation on Session1.

UPDATE  Bitmap_Demo SET Sex = 'F' WHERE EmpId = 7;

Previous Sex of EmpId 7 was also M, when tried to change it to F, it hanged.  It hanged and waited for ROLLBACK, COMMIT operation on Session1. 

UPDATE  Bitmap_Demo SET Sex = 'M' WHERE EmpId = 11;

Previous Sex of EmpId 11 was also F, when tried to change it to M, it hanged.  It hanged and waited for ROLLBACK, COMMIT operation on Session1. 

Test Case 4: 
Insert both sex in both sessions: DEADLOCK
 INSERT INTO Bitmap_Demo VALUES(emp_btmp.NEXTVAL, 'M');
 1 row created.

Session 2:
 INSERT INTO Bitmap_Demo VALUES(emp_btmp.NEXTVAL, 'F'); 
 1 row created.

Session 1:
 INSERT INTO Bitmap_Demo VALUES(emp_btmp.NEXTVAL, 'F'); 
...... waiting ......

Session 2:
 INSERT INTO Bitmap_Demo VALUES(emp_btmp.NEXTVAL, 'M'); 
...... waiting ......

Session 1: Immediately after execution of insert M in session 2, error prompt on Session 1
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

This clarified that we should never create BITMAP index on table/columns which are updated frequently like in OLTP applications with multi-user environment.