Index Maintenance

CHECKING THE PERCENTAGE OF DELETE LEAF ROWS

To analyze a table to determine the height and deleted leaf rows you can do the following. Be careful, this analyze command will lock the table so do not ever use this when your system is running. In fact, that sort of makes a point – things really should be bad for you to consider this.

ANALYZE INDEX emp_pk VALIDATE STRUCTURE;

SELECT name, 
       height, 
       del_lf_rows, 
       lf_rows, round((del_lf_rows/lf_rows)*100,2) pct_del_lf_rows 
FROM index_stats;

NAME         HEIGHT      DEL_LF_ROWS  LF_ROWS     PCT_DEL_LF_ROWS
-----------  ----------  -----------  ----------  ---------------
EMP_PK       3           163378       1008614     16.2