Re: index rebuilding...

One other point is that Oracle handles removing entries from leaf nodes differently than "theory" holds. On an entry removal from a leaf node, the theory holds that the tree would be modified in some way. But we know this isn't true in Oracle index trees.

Cheers,
Brian

Jonathan Lewis wrote:
>
> There are two reasons why I used to think this.
> First - the term 'rebalancing' misdirects the intuition,
> and second the manuals (once upon a time, perhaps)
> implied that the target of the B*tree mechanism was
> to ensure that no leaf was more than one layer deeper
> than any other leaf.
>
> The mechanism on inserts is simple and recursive:
> If you need to insert an index entry and the leaf
> is full, then it splits. But if it splits, the branch block
> above it has to have an extra entry (pointing to
> the value that starts the new leaf block) - so Oracle
> has to insert an entry into the branch block.
>
> But if the branch block is full, it has to split - which
> means the branch block above it has to have a
> new entry - but if that block is full ..... and so on
> until the block above is the root block.
>
> So what do you do if the root block is full ?
> Split it in two, and create a new root block
> above it with just two entries - at which point
> EVERY leaf block has changed height.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Coming soon a new one-day tutorial:
> Cost Based Optimisation
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> ____UK_______March 19th
> ____USA_(FL)_May 2nd
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA_(CA, TX)_August
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> DA Morgan wrote in message <3E3AAC94.B8B6073C_at_exesolutions.com>...
> >
> >I never thought I'd ever try to correct you but I believe (sticking
> it
> >way out there on ths one) it is possible for there to be four on one
> and
> >three on the other from time-to-time as Oracle doesn't rebalance
> every
> >time someone performs a single insert or delete. Isn't it more a case
> of
> >never having 5 and 3?
> >
Received on Mon Feb 03 2003 - 10:28:52 CST