Managing Bitmap Index

About Bitmap Index Management

Bitmap indexes can substantially improve performance of queries with the following characteristics:

  • The column has a low cardinality and few distinct values.

  • Bitmapped indexes are especially helpful for complex ad hoc queries with lengthy

  • WHERE clauses or aggregate queries (containing SUM, COUNT, or other aggregate functions).

  • The table has many rows.

  • There are frequent, possibly temporary queries on the table.

  • The environment is data warehouse-oriented (DSS system).

Locking Behavior

Bitmap indexes are not ideal for online transaction processing (OLTP) environments because of their locking behavior. It is not possible to lock a single bitmap position.

The smallest amount of a bitmap that can be locked is a bitmap segment, which can be up to half a data block in size. Changing the value of a row results in a bitmap segment becoming locked, in effect blocking changes on a number of rows.

This is a serious disadvantage when there are many UPDATE, INSERT or >DELETE statements being issued by users. It is not a problem when data is loaded or updated in bulk actions, as in data warehouse systems.

Creating Bitmap Index

You can create the Bitmap Indexes.
This task explains how to create the Bitmap Indexes.
  1. From the menu bar, make sure that you select Tools > Statistics > Classes >
    Compute
    to analyze all the classes to get the correct database statistics information.

  2. Select Tools > Index Management > Drop Indexes > Drop All, and then click Bitmap Indexes to drop all the existing bitmap indexes owned by CISDBA user and reset the corresponding property values from 6 (bitmap) to 0 (no index) in the class properties class and pointer class properties class.

  3. Select Tools >Index Management > Create Indexes > Recommendations to Create, and then click Generate Bitmap Indexes option to generate the index creation analysis report.

  4. Select View Report to see for what properties the Bitmap indexes are recommended.

  5. From the list of properties recommended for bitmap index, select only those properties that are frequently used in search conditions, and then mark the Column Index Type value in the Class Properties class or Pointer Class Properties class to 6 (Bitmap Index)

  6. Repeat step 3 to generate the index creation script.

  7. The bitmap index creation statements are available under Create Indexes tab. You can select any index and click Edit to make necessary changes. Click Create to create the indexes.

  8. To gather statistical information for the newly created indexes, select Tools > Statistics > Indexes > Compute/Estimate.