DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How to figure out if index needs rebuild?

  1. #1
    Join Date
    Aug 2000
    Chicago IL


    Hello I am rebuilding my table with new values. What method would I use to figure out if index needs rebuilding?

    "High Salaries = Happiness = Project Success."

  2. #2
    Join Date
    Dec 2001

    I have never used this myself but found the information on the Metalink. Hope it helps.


    Link if you have access to Metalink




    To describe how to determine whether an index is fragmented and
    therefore need rebuilding.


    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:


    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

    SELECT name,
    lf_rows - del_lf_rows lf_rows_used,
    to_char(del_lf_rows / (lf_rows)*100,'999.99999') ratio
    FROM index_stats
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width