Tag: low cardinality index

Oracle Bitmap Index

Common usage of Bitmap Indexes is a data warehousing environment. This implies large amounts of data, high level of ad-hoc queries but a low level of concurrent DLM transactions.

Why to use?

  • reduced response time
  • reduced storage requirements compared to other indexing techniques
  • efficient maintenance during parallel DML and load

Bitmap indexes are typically only a fraction of the size of the indexed data in the table.

How it works?

An index provides pointers to the rows in a table that contain a given key value. A regular index stores a list of rowids for each key corresponding to the rows with that key value. In a bitmap index, a bitmap for each key value replaces a list of rowids.

Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so that the bitmap index provides the same functionality as a regular index. Bitmap indexes store the bitmaps in a compressed way. If the number of distinct key values is small, bitmap indexes compress better and the space saving benefit compared to a B-tree index becomes even better.

Best use case?

From my opinion, bitmaps are most effective on queries with multiple where clauses.  As the bitmaps from bitmap indexes can be combined quickly, it is usually best to use single-column bitmap indexes. This is why the DW environment is the “home” for this type of indexing.

Frequent use cases?

The advantage of bitmap indexes is higher on columns where degree of cardinality (number of distinct values for the indexed column versus the total number of rows in the table) is small. Columns like Status, gender are optimal examples.

However, the datawarehouse environments can also benefit from bitmaps on columns with higher cardinality. This is also mostly due to the combining of bitmaps for quick filtering.

This is due to the fact that AND and OR conditions in the where clause can we resolved faster by performing Boolean operations directly on the bitmaps before converting them to rowids for data retrieval.

Using this bitmap merge methodology, Oracle can provide sub-second response time when working against multiple low-cardinality columns.

Unlike most other indexing, bitmap indexes include rows that have the null values.

Bitmap and Partitioning

You can create bitmap indexes on partitioned tables but they must be local to the partitioned table—they cannot be global indexes.

Example

create table test_tb
( row_id number
, text varchar2(100)
, status char(6));

create bitmap index test_bmpidx on test_tb(status);

On partitioned tables:

create table 
 t2
(c1 char(3) not null
, c2 date not null
, c3 number
, c4 varchar2(100))
partition by range(c2)
interval (numtodsinterval (1,'day'))
 (
 partition empty values less than (to_Date ('03-OCT-2016', 'dd-mon-yyyy'))
 )
;

create bitmap index test_bmpidx on test_tb(status);

 

Restrictions

You cannot create a bitmap join index on a temporary table.