Understanding Bitmap Index in Oracle database

BITMAP index are different from B-TREE index in the manner they store data.

• B-TREE index stores data in tree format whereas BITMAP index stores data as 2-dimensional arrays.

• B-TREE index are created on high cardinality columns whereas BITMAP index are created on low cardinality columns (Less # Of DISTINCT Values).We can create BITMAP index on high cardinality columns but in that case,it will consume much memory.

• B-TREE index don’t store NULL values, whereas BITMAP index may store NULL values.

BITMAP index can’t handle multiple concurrent updates and it leads to serious DEADLOCK issues in OLTP Systems.

• Create a BITMAP index only if the data is modified by a single process at a time with no parallel updates.

• BITMAP index is only available on Enterprise Edition.

SQL> CREATE BITMAP INDEX emp_bitmap_idx ON emp(deptno);

Index created.

SQL> ANALYZE INDEX emp_bitmap_idx VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT * FROM emp WHERE deptno = 10;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

Execution Plan
----------------------------------------------------------
Plan hash value: 1973842327

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     3 |   261 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | EMP            |     3 |   261 |     1   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | EMP_BITMAP_IDX |       |       |            |          |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPTNO"=10)

Note
-----
   - dynamic sampling used for this statement (level=2)

--Internally,the BITMAP index structure will look like


ROWID                      10         20         30
------------------ ---------- ---------- ----------
AAAUySAABAAAVkpAAA          1          0          0
AAAUySAABAAAVkpAAC          1          0          0
AAAUySAABAAAVkpAAN          1          0          0
AAAUySAABAAAVkpAAD          0          1          0
AAAUySAABAAAVkpAAE          0          1          0
AAAUySAABAAAVkpAAF          0          1          0
AAAUySAABAAAVkpAAG          0          1          0
AAAUySAABAAAVkpAAL          0          1          0
AAAUySAABAAAVkpAAB          0          0          1
AAAUySAABAAAVkpAAH          0          0          1
AAAUySAABAAAVkpAAI          0          0          1
AAAUySAABAAAVkpAAJ          0          0          1
AAAUySAABAAAVkpAAK          0          0          1
AAAUySAABAAAVkpAAM          0          0          1

While BITMAP index is for a single table,BITMAP join index can be created on join of two or more tables.Use BITMAP join index to store the result of a join.