Table and Index fragmentation Is there any way to check whether a table or index has been fragmented?

How to check if table is fragmented or not?

How to check if index is fragmented?

I searched Google but didn't get satisfactory answers. Some points like explain plan, table fragmentation, index rebuild etc I got. But what should be our first step and how should be resolve. How step by step we can move forward to resolve the issue?
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.