Check weather index require to rebuild or not ( Fragmented or not )

Recently I came across the situation to rebuild the index requested by one of my developer. Before rebuilding the index i would like to know weather index fragmented  or not is that require to rebuild this index. Even If rebuild this index can i get any benefit by rebuilding index.  Here is the step by step process to check index  needs to rebuild or not.


How to find index is fragmented?

Step 1) First analyze index 

SQL>analyze index INDEX_NAME validate structure;

Step 2) Determining if the Index Needs to be Rebuilt

SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;

In above query , 

1) If the ratio of deleted leaf rows to total leaf rows is clearly above 20%. Then good candidate for rebuilding.

 Let's rebuild the index and examine the results. 

SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;

Once rebuild that index ratio should be less than <20 div="div" done="done" have="have" job.="job." nbsp="nbsp" so="so" you="you" your="your">

Come to my case the ratio from above query is zero so it wont require to rebuild that index since its not fragmented.

Thanks for visiting my blog.

References:- 

http://dbataj.blogspot.com/2007/11/index-fragmentation-rebuild.html

 
Disclaimer: If you feel this content related to your content  please feel to contact me to remove this information.  I am sharing the knowledge with people whoever in the need which is learning daily basis.