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.
Required keywords
The unique name of the table
You must use one of these keywords. NOMINIMIZE is the default
Required keywords
ALTER TABLE table_name MINIMIZE/NOMINIZE
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.