Oracle Bitmap Index

Every Index Prupose

Is to provide pointers to a table rows that have a value.

Bitmap Index

In a bitmap index, instead of a list of rowids, Oracle creates a bitmap for each key value of the index.

Each bit of the map corresponds to a possible rowid. If the bit is set to 1, it means that the rowid contains that key value. 

An internal Oracle function converts the position of the bit into the corresponding rowid.

If the number of different values of the index is small, then the index bitmap will be very efficient in the use of physical space.

Example 

Suppose we have the following customers table:

CLIENTE        APELLIDO       REGION
101            PEREZ          NORTE
102            GARCIA         CENTRO
103            LOPEZ          SUR
104            SAN MARTIN     SUR
105            BROWN          CENTRO
106            CANEPA         CENTRO

The region column has low cardinality, since the possible values are very few (NORTH, CENTER, SOUTH). There are only three possible values for the region so a bitmap index would be appropriate for this column. However, a bitmap index for the CLIENT or LAST column is not recommended, given its high cardinality. 

The following would be the representation of the bitmap index for the REGION column. The index has three bitmaps, one for each region.

NORTE    CENTRO    SUR         
1        0         0
0        1         0
0        0         1
0        0         1
0        1         0
0        1         0

Each entry or bit in the bitmap index corresponds to a single row in the customer table. The value of the bit will depend on the corresponding value of the row in the table. For example, for the NORTH region the bitmap has a 1 in the first position. This is because the first row of the customer table has the NORTH value in the REGION column. The bitmap then has all zeros, indicating that the rest of the rows in the table have no clients in the NORTH region.

An SQL statement on this table and with the index bitmap, would be solved in the following way.

select count(*) from CLIENTES

    where REGION in ('NORTE','SUR');

A bitmap index can resolve this statement with great efficiency by counting the amount of ones existing in the resulting bitmap as shown in the following figure:

NORTE   CENTRO   SUR   (NORTE O SUR)
1       0        0      1
0       1        0      0
0       0        1      1
0       0        1      1
0       1        0      0
0       1        0      0

The "NORTH OR SOUTH" column is the resulting bitmap used to access the table.

Additionally, unlike B-tree indices, bitmap indices may include rows with NULL values ​​within the index structure. As for partitioned tables, bitmap indices can be used only if they are local to the partition. Global bitmap indices are not supported for partitioned tables.