This content has been marked as final. Show 6 replies
vk82 wrote:NEVER unless you have proof that measurable benefit will result.
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
Simply put, if you have to ask this question, you have no proof that REBUILD should occur.
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.