Oracle How To ...
Identify index fragmentation
To obtain information about an index:
analyze index <index_name> validate structure;
This populates the table ‘
index_stats’. It should be noted
that this table contains only one row and therefore only one index can be
analysed at a time.
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.
This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.