The Role of the INDEX_STATS View in Index Rebuilds
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).