Siebel Analytics Performance Tuning Guide > The Database Servers > Index Selection >

Bitmap Indexes


Oracle supports bitmap indexes which are often useful in many situations where B-tree indexes are not optimal. Namely, for columns which have a large number of duplicate values. For example, a Size column that contains only five distinct values (tiny, small, medium, large, grand).

Oracle's "star transformation algorithm" uses bitmap indexes to join a fact table to its dimensions when they exist on the foreign key constraint columns in a fact table. Actually, this algorithm is robust enough to use a combination of bitmap and B-tree indexes. IBM DB2 takes advantage of this technology in selected situations by dynamically building bitmaps from a single-column B-tree to join tables. Unlike Oracle, however, these are highly specialized cases.

Entries in a bitmap index consist of a search key value and a bitmap which describes rows that contain the search key value. Each bit in the map corresponds with a row in the table, and a bit on signals a row that contains the value in the search key. key value.

Candidates for Bitmap Indexes

Bitmap indexes are most advantageous whenever the cardinality of the index is less than one percent, or lowly-selective. This criterion is nearly the opposite of the guideline for B-Tree indexes.

Look for cases where:

Given this kind of scenario, the server can evaluate the constraints by ANDing the bitmaps and potentially eliminate a large number of rows without ever accessing a row in the table.

The Oracle database server can also generate query execution plans to join a fact table to its dimensions using its star transformation algorithm when bitmap indexes exist on the fact table foreign key reference columns. When this execution plan is the least costly, the server joins the tables using the bitmap indexes.

CAUTION:  Bitmap indexes should be used only for static tables and are not suited for highly volatile tables in online transaction processing systems.

Also, the Oracle optimizer generates query plans only when the cost-based optimizer has been enabled.

To create a bitmap index, use the "bitmap" keyword:

create bitmap index w_srvreq_d_m2 on w_srvreq(area_I)
      nologging tablespace idx pctfree 0 ;

You can analyze a bitmap index just like you do any other index:

analyze index w_srvreq_d_m2 compute statistics ;

Example Bitmap Index

Bitmap indexes are useful for low-selectivity cases. For example, consider the AREA_I column on the W_SRVREQ_D table of a sample database. A simple query against this query shows that this column has a very small domain (few distinct values).

NOTE:  Be sure that you understand your data shape when evaluating the need for indexes. Also be aware that this example is heavily dependent on a particular data shape.

SQL> select area_i, count (*) cnt from w_srvreq_d group by area_i;

AREA_I                               CNT
----------------------          ------------
3rd Party Software                  171,456
Abnormal Usage                       77,988
CD-ROM                              171,717
Disk Drive                          115,379
Ethernet Card                        59,076
Hard Drive                           59,328
Installation                      1,850,376
Internet Registration Request       199,272
Memory                              258,352
Network                             199,850
Operating System                    168,396
Performance                         140,895
Unspecified                       1,368,797
Upgrade                             278,136
Usage                               199,178
User Interface                       276,738

This table contains only 16 distinct values that occur in 5,594,934 rows. Clearly, this is not a candidate for a B-Tree index. Such an index would more likely degrade rather than improve query performance. This column is, nevertheless, an excellent candidate for a bitmap index. With a cardinality of 1 / 350,000 (0.00028%), it is nearly perfect for a bitmap index.

TIP:  Bitmap indexes are most effective when a query constrains a set of columns that have bitmap indexes or a combination of single-column bitmap and B-Tree indexes. When these conditions are ANDed, then the server can AND the bitmaps rather than data fetched from individual rows.

When Bitmap Indexes Should Not Be Created

Bitmap indexes should not be created in the following cases:

Indexes incur several costs and you need to balance their cost against their actual benefits. Use a performance monitor to evaluate their actual benefits.


 Siebel Analytics Performance Tuning Guide 
 Published: 18 April 2003