To check for index fragmentation

 — +——————————————————————————+
— | PURPOSE  : To check for index fragmentation. As a rule of thumb if 10-15%  |
— |            of the table data changes, then you should consider rebuilding the index  |
— +——————————————————————————-+


ANALYZE INDEX &&index_name VALIDATE STRUCTURE;


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 ibadness     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 /(DECODE(lf_rows,0,0.01,lf_rows))*100,’999.99999′) ibadness
FROM   index_stats
/


prompt
prompt Consider rebuilding any index if % of Deleted Leaf Rows is > 20%
prompt


undefine index_name
Recent Posts