Oracle Scratchpad

January 4, 2009

Index rebuild – again

Filed under: Index Rebuilds,Indexing,Infrastructure,trace files — Jonathan Lewis @ 7:10 pm BST Jan 4,2009

Someone posted a very pertinent question about blocks splits and index rebuilds on the OTN Database forum yesterday covering a detail of the cost/benefit equation that I don’t think I’ve mentioned before.

Since I answered the question on the forum I’ve posted a link here to make it available to a wider audience. 

(This is a strategy I may adopt more frequently in the future – there’s a lot of useful material of mine all over the internet, and I really ought to make sure I don’t spend time repeating myself when  simple pointer would do).

[Updated May 2009: The forum item has since gone missing – so maybe linking to notes I’ve written in public groups is not so smart after all]. Fortunately I happen to have been saving copies of the thread as it progressed, and the little point that I wanted to highlight was just the following:

“When you create or rebuild an index Oracle does not honour the pctfree setting in the branch blocks. Since the branch blocks are effectively 100% packed, the very first leaf block split in each branch block (except the last one) will almost certainly result in a branch block split. This means a single row insert into an 8KB block could result in 16KB+ of redo for the leaf block plus 16KB+ for the branch block split.”

[Further reading on rebuilding indexes]


  1. I’ve just realised that I used the wrong URL in the note yesterday. It’s now pointing to the correct posting.

    Comment by Jonathan Lewis — January 5, 2009 @ 7:37 am BST Jan 5,2009 | Reply

  2. Yes, an index/contents listing of everything you do would be a lot better than a glob of maybe-relevant google search results. You could even annotate and note supercession. I know I would use and link to such a thing. It just needs to be easy enough to maintain to not be another whole job.

    You might also consider some sort of redundant public archiving, given the notorious losses of Good Things on the cloudytubes. Perhaps another blog for this purpose, or a google group. is unused… :-)

    The downside is some people seem to respond better to a direct response than a pointer to a doc (not to mention, when reading through a post, it’s too easy to think you will click on the link later). But of course, with pointers to hand, cut and paste becomes easy, just requiring care to paste the relevant thing.

    Comment by joel garry — January 7, 2009 @ 12:22 am BST Jan 7,2009 | Reply

  3. Another downside is when OTN is “experiencing Problems and is temporary unavailable” like it is right now… ;-)

    Comment by Olivier Degimbe — January 8, 2009 @ 1:24 pm BST Jan 8,2009 | Reply

  4. Hi Jonathan
    The link your provided is not showing the discussion page.

    Comment by Raj — September 25, 2011 @ 6:14 am BST Sep 25,2011 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

You are commenting using your 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

Powered by