The Role of the INDEX_STATS View in Index Rebuilds
The INDEX_STATS view by default has no rows. You populate this view by executing the analyze index... validate structure command. Once you do this, the INDEX_STATS will supposedly have the necessary data to guide your index rebuild decisions.
Benefits from the INDEX_STATs view
Once you have the view populated, you can use it to look at and compute a number of useful items of information that can help you stay on top of indexing in your database. The key columns you need to pay attention to are the following:
- HEIGHT: Height of the index, which begins at 1 for root only index.
- BLOCKS: Number of blocks allocated to the index.
- LF_ROWS: Number of leaf row entries (includes deleted row entries).
Get Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
