analyze index index_name validate structure;

select name, trunc(100*(del_lf_rows/lf_rows)) pct_deleted, pct_used from index_stats;

An index should be considered for rebuilding under any of the following conditions:

* The percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3.

* If the ‘HEIGHT’ is greater than 4.

* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.

alter index MATRIX.LXSET_CCE31898_LXSET_INDEX rebuild tablespace ESERVICEPROD_INDEX nologging;

ITPub博客

Identify index fragmentation

最新文章