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} [ed: thread has been deleted] 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 a simple pointer would do).
[Updated May 2009: The forum item has since been deleted – 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.”
Oracle Scratchpad 
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 GMT Jan 5,2009 |
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.
http://groups.google.com/group/Oracle-Server 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 GMT Jan 7,2009 |
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 GMT Jan 8,2009 |
Hi Jonathan
The link your provided is not showing the discussion page.
thanks
—Raj
Comment by Raj — September 25, 2011 @ 6:14 am BST Sep 25,2011 |