To describe how to determine whether an index is fragmented and
therefore need rebuilding.
SCOPE & APPLICATION
For DBAs requiring to know when to rebuild their indexes.
[NOTE:77574.1] Guidelines on when to Rebuild an Index
Indexes will become fragmented with inserts and deletes because
of the way indexes are implemented. When a row is deleted,
Oracle will not reuse the index space. Pctused for indexes
is always 0, which means the index blocks will not be put on
the free list for reuse. Therefore, indexes are always growing
and can become very fragmented. Hence we have to drop and
recreate indexes at times. This articles explains when we should do it.
Determining When Index is Fragmented
You should regularly evaluate the space usage and efficiency
of indexes. To see how well or how poorly an index is using
its space use an ANALYZE Command option.
To ascertain index fragmentation, the following SQL statement can be used:
ANALYZE INDEX &index_name VALIDATE STRUCTURE;
Then check INDEX_STATS view particularly columns LF_ROWS
and DEL_LF_ROWS which shows current number of entry slots in leaf blocks. On the other hand LF_ROWS_LEN and WS_LEN shows the total number of bytes associated with these entries.
As a rule of thumb, when the number of or spaced used by
deleted entries is greater than 15 - 20% of the total entries
you should consider rebuilding the index.
The script below calculates the ratio of the DEL_LF_ROWS and LF_ROWS
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 ratio heading '% Deleted|Leaf Rows' format 999.99999
lf_rows - del_lf_rows lf_rows_used,
to_char(del_lf_rows / (lf_rows)*100,'999.99999') ratio
where name = upper('&index_name');
You can monitor the leaf entries versus HEIGHT (levels)
column of INDEX_STATS. The HEIGHT column of INDEX_STATS should not change if the index entries remain the same. If the index HEIGHT keeps increasing it means that more branch block levels are being added, an expected behavior if more entries are being stored in the leaf blocks. On the other hand, if the HEIGHT increases but number of leaf entries remains the same, it means the structure
is becoming top_heavy with branch blocks. This occurs when branch blocks are being maintained for partially emptied leaf blocks. If this is the case consider rebuilding the index.