Index Enhancements   «Prev  Next»

Lesson 4 Oracle bitmapped index improvements
Objective Describe the bitmap index improvements of Oracle9i.

Oracle Bitmapped Index Improvements

Oracle includes an improvement that allows better performance and storage allocation for bitmapped indexes. You typically use bitmapped indexes when there are many rows with the same value for an index's table that has a low cardinality.

Optimizing Mapping

A bitmapped index includes a bitmap, which indicates the presence or absence of a particular value in a particular row in a table. This bitmapped is mapped back to the ROWID of the row in the table. To optimize this mapping, it helps if the bitmapped index is aware of the maximum number of rows that can exist in any particular block of a table. Having this knowledge allows a bitmapped index to allocate fewer bits for each of its own blocks, which in turn results in fewer bits per block in the bitmapped index.

ALTER TABLE Syntax

You use the ALTER TABLE statement to force a table to limit the number of rows in any particular block. The syntax for the feature is illustrated in the following Diagram.

  1. Required keywords
  2. The unique name of the table
  3. You must use one of these keywords. NOMINIMIZE is the default
  4. Required keywords
ALTER TABLE table_name MINIMIZE/NOMINIZE
  RECORDS_PER_BLOCK;

Minimize Records Per Block
You are allowed to use this statement only if a table does not already have a bitmapped index on it. If a bitmapped index exists for the table, you will have to drop the index and re-create it after you use the ALTER TABLE statement.
For this table modification to work properly, you must run the ALTER TABLE statement with the MINIMIZE option on a table that is not empty. In the next lesson, you will learn about reverse indexes.