"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<3fce36f0$0$14056$afc38c87_at_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
I understand now. Thank you so much for taking the time to explain.
Received on Fri Dec 05 2003 - 03:42:08 CST
