I found this note on index fragmentation. Its asks to check ration pct_del to pct_used, if its more than 20%, then its fragmented and its a good candidate for rebuilding


Thanks

Note :

Then check INDEX_STATS view particularly columns LF_ROWS
and DEL_LF_ROWS which shows current number of entry slots in
leaf blocks. On the other hand LF_ROWS_LEN and DEL_LF_ROWS_LEN shows
the total number of bytes associated with these entries.

As a rule of thumb, when the number of or spaced used by
deleted entries is greater than 15 - 20% of the total entries
you should consider rebuilding the index.

The script below calculates the ratio of the DEL_LF_ROWS and LF_ROWS

col name heading 'Index Name' format a30
col del_lf_rows heading 'Deleted|Leaf Rows' format 99999999
col lf_rows_used heading 'Used|Leaf Rows' format 99999999
col ratio heading '% Deleted|Leaf Rows' format 999.99999

SELECT name,
del_lf_rows,
lf_rows - del_lf_rows lf_rows_used,
to_char(del_lf_rows / (lf_rows)*100,'999.99999') ratio
FROM index_stats
where name = upper('&index_name');


You should also check the PCT_USED column of INDEX_STATS:

select name, btree_space, used_space, pct_used from index_stats;

and pct_used should not be more than 80%.