When to rebuid an index in Oracle

Observe height in index_stats view after analyzing the particular index. If it is >= 4 , it is good to rebuild index as it indicate a skewed tree structure. Although there is always been an exception to this rule. So observer the height in index_stats time to time and if height remain unchanged . It may be assumed that It is correct .

Observe del_lf_rows and lf_rows in index_stats view after analyzing the particular index. If the percentage (del_lf_rows/lf_rows)*100 > = 20 , It is good to rebuild index as it indicate unbalanced tree because of high update and delete occurred on it.

Example :-
SQL :> analyze index IND_PK validate structure;
SQL :> select name,height,del_lf_rows,lf_rows,(del_lf_rows/lf_rows) *100 from index_stats;
SQL :> alter index IND_PK rebuild;