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]

September 16, 2007

Index Rebuild

Filed under: Index Rebuilds,Indexing,Infrastructure,Troubleshooting — Jonathan Lewis @ 10:12 pm BST Sep 16,2007

From time to time I see people asking about Oracle error ORA-01410: invalid rowid. It’s a problem I’ve responded to a few times in newsgroups and presentations, but I realised recently that despite mentioning it casually for several years I’ve never written a specific note about it – so here it is.


« Previous Page

Powered by