This discussion is archived
6 Replies Latest reply: Nov 14, 2012 11:30 AM by jgarry RSS

When I should rebuild the inde

vk82 Explorer
Currently Being Moderated
Hi Guru's,


Sorry to ask the saem question again.
I know that most of the post are written on net for the same problem.
After google and going through Tom Kyte's Website I am still in confusion that when to rebuild the index. He said on his website that there is no requirement to rebuild index but i want to know in which circumstances i need to rebuild the indexes.

Can anyone help me out please.

Thanks in advance
  • 1. Re: When I should rebuild the inde
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check this thread
    When to rebuild indexes
  • 2. Re: When I should rebuild the inde
    vlethakula Expert
    Currently Being Moderated
    I normally do when index size is greater than table size
  • 3. Re: When I should rebuild the inde
    sb92075 Guru
    Currently Being Moderated
    vk82 wrote:
    Hi Guru's,


    Sorry to ask the saem question again.
    I know that most of the post are written on net for the same problem.
    After google and going through Tom Kyte's Website I am still in confusion that when to rebuild the index. He said on his website that there is no requirement to rebuild index but i want to know in which circumstances i need to rebuild the indexes.

    Can anyone help me out please.

    Thanks in advance
    NEVER unless you have proof that measurable benefit will result.
    Simply put, if you have to ask this question, you have no proof that REBUILD should occur.
  • 4. Re: When I should rebuild the inde
    6363 Guru
    Currently Being Moderated
    vlethakula wrote:

    I normally do when index size is greater than table size
    And when after the rebuild it is still larger than table size do you rebuild it again and again forever?
  • 5. Re: When I should rebuild the inde
    Aman.... Oracle ACE
    Currently Being Moderated
    vlethakula wrote:
    I normally do when index size is greater than table size
    And what would you do when you would find that after the rebuild , the size of the index becomes larger?

    Edit : Its the same what 3360 had asked already.

    Aman....

    Edited by: Aman.... on Nov 14, 2012 10:23 PM
  • 6. Re: When I should rebuild the inde
    jgarry Guru
    Currently Being Moderated
    There are some bugs with certain versions and odd procedures that some places use that can influence when to rebuild. The bugs generally show up as excessive index size. Odd procedures can be all sorts of strange things, including implicit index patterns that could make performance worse with a rebuild. So seriously, the best rule of thumb is to not rebuild unless you can show why you should for a particular index. Some DW or DSS environments may take data load shortcuts that can make indices unusable. Massive deletes where index leaves won't be reused for a long time also might benefit, especially if it leaves sparse leaf blocks.

    Poke around on Richard Foote's blog for some enlightening examples, including how to figure your local situation out yourself.

    Watch out for rebuilds that make a temporary performance increase, or at least figure out why that is happening so you can document it for the future.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points