Re: index rebuilding...

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?

>