jump to navigation

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.

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

About these ads


1. Yas - December 12, 2007

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.

2. Richard Foote - December 12, 2007

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 :)

3. Nigel Thomas - December 13, 2007

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?

4. Hemant K Chitale - January 8, 2008

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.

5. Richard Foote - January 8, 2008

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 ;)

6. Naresh Bhandare - January 27, 2009

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.


7. Richard Foote - February 5, 2009

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 2,042 other followers

%d bloggers like this: