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

Thread: rebuilding indexes

  1. #1
    Join Date
    Oct 2000
    Posts
    22

    Red face

    Hi guys,
    I ran these two scripts to find out if there are indexes that needs to be rebuid, but there were none. None of the indexes needs rebuilding.

    SQL> select index_name, blevel, decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',
    2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK
    from dba_indexes


    SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,
    (LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS)
    DISTINCTIVENESS
    from index_stats
    where NAME='&index_name'





    But I found out that the extents for the indexes segments are 35.
    What do you guys think?


    thanks,


  2. #2
    Join Date
    Jan 2001
    Posts
    71
    your index may just grow with table, but not much delete. so you see index extent increase and no rebuild is nessary. you only need to rebuild index when deleted rows is more than 20% of total rows.

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    If your indexed column is using a sequence, then the index will grow on one side. In that case, you need to rebuild the index once in 3 months, so that the number of look ups in the B-Tree will be decreased.

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