Index Internals – Rebuilding The Truth December 11, 2007
Posted by Richard Foote in Index Coalesce, Index Height, Index Internals, Index Rebuild, Index Shrink, Index statistics, Oracle Indexes, Oracle Myths, Oracle Opinion, Richard's Musings.trackback
The issue of when to rebuild indexes crops up again and again and again. Unfortunately, so do the same incorrect, myth-filled uninspired responses which considering how important and “key” (no pun intended) indexes are to database design and performance generally, is very puzzling indeed.
In the coming days I’ll post why I believe these index related myths simply refuse to go away …
This presentation was originally written several years ago but is still as relevant today as it’s always been.
Recently updated version: Index Internals – Rebuilding The Truth
Richard, first of all, thanks for starting a blog and welcome.
I see a question about rebuilding indexes everyday in several forums and mailing lists. Even Oracle’s own people suggest rebuilding indexes based on some percentage numbers or based on the blevel of an index in the reports they prepare for the customers. I think what they say is also not helping to destroy these myths.
Hi Yas
Yes, I agree totally.
I’ve just written a new blog entry that explains my take on all this.
Hopefully, this blog and the various things I’ll post over time will help that teeny weeny bit
Thanks for that very clear presentation. The main news for me was the 90/10 (or 99-1) split for monotonically increasing keys. Just out of interest, when did that optimisation get made?
Excellent Material !
You don’t just write “Indexes NEVER need to be rebuilt” but do
go through cases when they can be COALESCEd or REBUILDed and,
along the way, also cover a lot of ground.
Hi Hemant
Thanks for your kind comments.
One of the big mistakes some people seem to make when reading the presentation is that I somehow claim indexes should never be rebuilt.
I can only assume the thing is just too long, they give up at about the halfway mark and make the wrong assumption.
Thanks for managing to make it all the way through !!
Cheers
hi Richard,
one slides 78 and 79 (“Spot the difference” – why is the index space usage less efficient in the case that you commit after every record?
Sorry if you have already explined it in the presentation – I could not find it.
Thanks,
Naresh
HI Naresh
It’s simply due to a bug with the 9i release that caused more indexes to potentially be fragmented than designed due to 90-10 splits not being performed when they should. Now fixed.