Here is the syntax:
analyze index CUSTOMER_LASTNAME_IND validate structure;
It populates the SYS.INDEX_STATS view only. The SYS.INDEX_STATS view can be accessed with the public synonym INDEX_STATS. The INDEX_STATS view will hold information for one index at a time: it will never contain more than one row. Therefore you need to query this view before you analyze next index.
Let’s look at the data inside INDEX_STATS:
select name as IndexName,
height,
lf_rows,
del_lf_rows
from index_stats;
Here are the results of that query:
INDEXNAME HEIGHT LF_ROWS DEL_LF_ROWS
————————- ———- ———- —————
CUSTOMER_LASTNAME_IND 2 5237 2130
The following INDEX_STATS columns are especially useful:
height refers to the maximum number of levels encountered within the index.
lf_rows refers to the total number of leafs nodes in the index.
del_lf_rows refers to the number of leaf rows that have been marked deleted as a result of table DELETEs.
The second rule is that the deleted leaf rows amount (del_lf_rows) should be less than 20 percent of total number of leaf rows (lf_rows). A high number of deleted leaf rows shows that the table has been subjected to heavy DELETE activity. As a result, the index tree becomes unbalanced and the index is a good candidate for rebuilding.
In this example, del_lf_rows equals 2130, lf_rows equals 5237; the ratio is:
(2130*100) / 5237 = 40.7 percent
This index is simply “begging” for rebuilding!