When to rebuild indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: When to rebuild indexes

  1. #1
    Join Date
    Oct 2002
    Location
    london
    Posts
    41

    Question When to rebuild indexes

    When is a good time to rebuild indexes?

    Are there any particular stats, queries that can be run to tell when an index is fragmented?
    OCP 8i DBA

  2. #2
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Analyze the indexes using

    ANALYZE INDEX index_name VALIDATE STRUCTURE;

    Then query the INDEX_STATS table and compare LF_ROWS with DEL_LF_ROWS.
    if you have > 15% of the rows as deleted, then I'd rebuild the index.

  3. #3
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    as INDEX_STATS only show the details for the last validate structure issued you'll be wanting to script/PLSQL something to look at all your indexes. It's been done a thousand times before so there are plenty freely available. I'm sure this can be done 'online' but I'm scared to try it can someone confirm this for me?

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If you are going to rebuild indexes then benchmark your performance before and afterwards. Probably you will find that there is no difference, but you might find that performance degrades. You might then reconsider whether you want to bother.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    SlimDave, I'll have to disagree with you.

    We regularly re-index and get significant performance increases. More boost on indexes with frequent updates. Not to mention the space recovered from unused leaf blocks.

    Certainly worth it.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Hey, I'm not saying it can't happen, just that it's unlikely.

    Benchmarking is the key -- if it's faster afterwards then keep on going, but don't go thinking that index rebuilds are a quick and easy step to happiness. If you're not benchmarking your improvement then you're wasting time -- rebuilding indexes every month turns into a little religious rite that must be performed to keep the database gods happy.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,
    If your index contains a columns with a growing ID and older rows are deleted due to the apllication-design, then you can rebuild the index regularly because your index growth in space and your leaf-blocks are not filled enough for effective read.

    ORca


    Be aware of that index-rebuild can cause the optimizer to choose another execution-plan, after collecting stats. This is very problematic with partitioned indexes.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    That sounds like it might be a candidate for a reverse index?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Instead of REBUILDING, consider COALESCE.
    The command is :
    ALTER INDEX indexname COALESCE;

    By coalescing, Oracle merges near emplty blocks and frees them for future insertion. Moreover, there won't be lock on the table, so the availability of the table is ensured. In 24x7 env, this is a most command I have come across.

    Tamil

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