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 O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.