when should i rebuild db indexes?

Sachin Thapa

Thanks John,

I'll go through the link that you have provided.

https://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf

Regards,

Sachin

John Stegeman

I didn't provide a link.

dthrashtaconecom

Here's a note on MOS that echos the advice given by the high ranking experts above:  Index Rebuild, the Need vs the Implications (Doc ID 989093.1).

And here's another note on MOS that supports Mr. Foote's expertise on this subject: bde_rebuild.sql - Validates and rebuilds indexes occupying more space than needed (Doc ID 182699.1).  Be sure to read the comments in the script starting with "Before you proceed to rebuild your indexes, please read the following extract from feedback provided by Richard Foote on October 2003:"

Good luck,

David R. Thrash

Jonathan Lewis

Sachin,

It's been several years since I went through the process of highlighting the errors in answers (which often were little more than links to his multiple websites) that Don Burleson supplied to this forum, and I really can't face the thought of dissecting the stuff he writes any more. I did, however, look at the link you supplied - and the only thing I'll say about it is that it reference 4 Doc IDs from Metalink (MoS):

77574.1     This doesn't exist anymore, because in the feedback box I suggested to Oracle that it be withdrawn because the advice was so bad (I explained what was wrong with it at the time)

122008.1     I also suggested to Oracle that they withdraw this one too for the same reasons - it did disappear temporarily, then reappeared and if you read it you will find that it says "The advice we used to give is no longer valid, and generally you don't need to rebuild indexes. It also links to the other two notes referenced by the Burleson page.  Here's a link to a comment I made on OTN in 2008 where I pointed out that these two notes had been withdrawn:

989186.1    This is basically a script that does some arithmetic based on the current stats on b-tree indexes to give you an idea of how big your indexes would be if you rebuilt them so that you can get an idea of (a) how much space you could reclaim in the database and (b) whether an index might be so much larger than it needs to be that you should investigate why it has got so large and (probably) rebuild it at least once.  (The code is largely some stuff I wrotre a few years ago with some wrapping to capture the results to a table, see: https://jonathanlewis.wordpress.com/index-efficiency-3/ and https://jonathanlewis.wordpress.com/index-sizing/ )

989093.1     This is a note that explains why you rarely need to rebuild B-tree indexes - when it first appeared I think it was part of the "new" 122008.1 with an acknowledgment to Richard Foote who wasn't an employee at the time.

Bottom line - whatever the article may have said, the (surviving) MoS notes in the links don't agree with it.

Regards

Jonathan Lewis

Martin Preiss

personally I tend to rebuild indexes if they are in an unusable state.

I heard that in some situations it may be useful to tell the end users that you did a rebuild without actually doing something. This approach is known as "Faith-based Tuning" - https://richardfoote.wordpress.com/2011/04/20/blks_gets_per_access-index-rebuild-criteria-twisted-logic/#comment-12417.