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

"Jeffrey Yee" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> 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