ANALYZE INDEX and INDEX_STATS

ANALYZE INDEX myidx1 VALIDATE STRUCTURE;

SELECT * FROM INDEX_STATS;

INDEX_STATS only stores index analysis of the last performed ANALYZE INDEX statement.

You can find valuable information about any index in this table and can be used for various purposes. 

I used it to determine the fragmentation on an index, it can be determined if the index needs to be rebuilt or not.

1. If del_lf_rows is less than the estimated record count in the table

2. if lf_rows is less than the lf_blks

3. if height value is greater than 4

In all the cases above the index needs to be rebuilt....