Table and Index fragmentation
You need to use INDEX_STATS view to determine fragmentation. 

Firstly, You need to populate this view with an individual index by using :

SQL&gt; analyze index &lt;your_index&gt; validate structure;


and then look at these values ratio and height :

select round((del_lf_rows/lf_rows)*100,2) ratio, height, lf_blks, lf_rows 
  from index_stats;


If ratio is more than %20 

or height is more than or equal to 4 

or  lf_rows is lower than lf_blks, 

then you need to rebuild your index against fragmentation.



The blevel column of dba_indexes table is my main point of interest. This gives the number of branch levels (including the root node) for a b-tree index, and the column height is closely related to blevel.

If you have access to MOSC, search for the blevel keyword, especially the subject Index Health. The Oracle Community was also suggesting rebuilding indexes for indexes have blevel more than or equal to 4.