So, how do we tell when an index would benefit from being rebuilt?  There are two Oracle views that provide index statistics, Remote DBA_INDEXES and INDEX_STATS.  The Remote DBA_INDEX view contains statistical information that is placed into the view when the ANALYZE INDEX xxx command is issued.  Unfortunately, the Remote DBA_INDEXES view was designed to provide information to the cost-based SQL optimizer and it does not keep statistics about the internal status of the Oracle indexes.  To see the internal structure for an Oracle index, you must use the ANALYZE INDEX xxx VALIDATE STRUCTURE  SQL command to validate the structure for the index.  This command creates a single row in a view called INDEX_STATS.

    HEIGHT     BLOCKS NAME                              LF_ROWS    LF_BLKS

---------- ---------- ------------------------------ ---------- ----------

LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS

----------- ---------- ---------- ---------- ----------- ---------- -----------

DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE

--------------- ------------- ----------------- ----------- ----------

  PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS

---------- ------------ --------------------

         3       5635 DON_FK_PART                       196103       2382

    3137648       3900       2381         18       41031       3956           7

            112           125             56220     9361008    3178679

        34     1568.824              787.912

The Oracle index_stats view will never contain more than one row.  Therefore, you must
perform the "analyze index xxx validate structure" command and "select * from index_stats" before issuing the next analyze index command.  The script id1.sql provides a method for getting a complete report for all indexes.