You can run the 'ANALYZE INDEX VALIDATE STRUCTURE' command on the affected indexes - each invocation of this command creates a single row in the INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS'.
Posted by: Sumer Singer
Index fragmentation occurs when a row included in the index is deleted.
You will need to analyze indexes individually to find those deleted indexes, once discovered they can be rebuilt.
To analyze issue the following command :-
analyze index owner.index_name validate structure;
The above command create a single row in 'index_stats' view.
Now find the ratio of del_lf_rows and lf_rows:-
select del_lf_rows * 100 / decode(lf_rows,0,1,lf_rows) from index_stats
where name = 'index_ name'
If it is more than 20% of rows are deleted then the index should be rebuilt.
The index stats table can only hold one record of information at a time, therefore you will need to analyze each index individually.