Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is there such thing as row migration for indexes?

Re: Is there such thing as row migration for indexes?

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 4 Dec 2003 06:18:07 +1100
Message-ID: <3fce36f0$0$14056$afc38c87@news.optusnet.com.au>

"Jeffrey Yee" <jeffyee_at_hotmail.com> wrote in message news:ec4cec95.0312030149.1d423999_at_posting.google.com...
> Dear Howard,
>
> Thank you very much for the explanation. It makes a lot of sense. What
> I'm still confuse is why is there a need to specify pctfree for index,
> since any modification on the index is either an insert or
> delete/insert combination. Please advice once again. Thank you in
> advance.

Well, if every insert you were performing into the table was guaranteed to require positional placing in the index *after* the inserts you'd already done, then you wouldn't need pctfree at all (ie, you could -and should- set it to zero). This is exactly what happens when you place an index on a sequence number. The sequence number only ever goes up, so the next insertion into the index is always going to be positionally after the last one. I can therefore fill each leaf node of the index to the brim, and have done with it.

But what if the next insert could go anywhere in the index? If I index people's names, and unless I cunningly arrange only ever to hire new employees in ascending alphabetical order, then my next insert might be 'Bob' when my previous insert was 'Xavier'. But if I'd set pctfree to zero (or if there had been no pctfree at all) then it is quite likely that the first leaf node of my index is already full to the brim with entries for Adam, Charles, David and Ed. So Bob can't simply be inserted into that leaf node, but we have to make room for it. A block split occurs that moves the 'David' and 'Ed' entries out of the way, thus making enough room for the new 'Bob' entry.

But block splits are really rather expensive. You've got to delete some rows from one leaf node (redo+undo generated); you've got to re-insert them into their new leaf node (redo+undo generated). You've got to insert a new pointing entry into one of the branch blocks (redo+undo generated). And conceivably, you've got to cascade that sort of re-organisation all the way up the entire height of the index tree.

What if I'd cunningly arranged for every block to have some guaranteed empty space in it before starting, though? Then Bob would have found there was 10% (say) of the leaf node already empty, and could simply have been inserted into that space.

Sure, if I then go on to employ Bill, Benny, Brian, Betty and Blodwin as well, I might well find that my 'guaranteed' empty space has been all used up... and we're back to block splitting if that's the case. But if I set *sufficient* empty space to start with, it will be a long time before I need to split that block.

Reserving some empty space is, of course, precisely what pctfree does. When you index a sequence number, you know later inserts between existing values can't happen, so there's never going to be a need for a block split, and therefore pctfree can be 0 (or close to it). But for non-sequence data, you set pctfree to whatever level you think sufficient to permit subsequent inserts to fit between existing values without the need for an expensive block split to achieve the same result.

Regards
HJR Received on Wed Dec 03 2003 - 13:18:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US