| 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.
ALTER TABLE table_name MINIMIZE/NOMINIZE RECORDS_PER_BLOCK;
| ALTER TABLE | Required keywords. |
| table_name | The unique name of the table. |
| MINIMIZE/NOMINIMIZE | You must use one of these keywords. NOMINIMIZE is the default. |
| RECORDS_PER_BLOCK | Required keywords. |
Oracle Systems
Many Oracle systems store rows as small and then expand them with SQL UPDATE statements causing the rows to fragment into multiple blocks.
To prevent the inevitable row migration that results from extreme row size expansion, I set PCTFREE to a high value whenever rows are stored small, and later expanded.
To prevent the inevitable row migration that results from extreme row size expansion, I set PCTFREE to a high value whenever rows are stored small, and later expanded.
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.
