Re: index rebuilding...

"Howard J. Rogers" wrote:

> "DA Morgan" <damorgan_at_exesolutions.com> wrote in message
> > 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?
>
> Jonathan can answer for himself, of course. But I'll take a stab at it
> too... no, it's not possible for a single insert to result in an unbalanced
> index. Oracle's indexes are *always* balanced. That's why you really don't
> want to create indexes unnecessarily, because they can slow down simple DML
> on the table dramatically. Your simple insert that threatens to unbalance an
> index will cause a whole raft of cascading adjustments to the index
> structure precisely to avoid imbalance.
>
> I don't know whether it's just a myth or not, but Oracle refers to its
> indexes as "B*Tree" structures, not 'b-tree' ones. And that's because the
> 'b' doesn't stand for 'binary', but 'balanced'.
>
> Whether that bit of apocrypha is true or not, Oracle index structures are
> always re-balanced.
>
> Regards
> HJR
Just went through my files and found a posting by Jonathan from 23 May, 2002 subject title "unbalance indexes --common widsom?". And I quote:

"No matter how perfectly an index is maintained, it will always be possible to ensure that you can get it to a state where either just one 3rd level node has split to produce an "imbalance" or (the only possible alternative) every 3rd level node has to split to leave the index half-populated if you want "balance" - i.e. every leaf has to be at the fourth level.

The important point about balance b-trees is that no leaf is MORE THAN ONE level deeper that every other leaf."

(even the above misspelling accurately copied).

So unless Jonathan has changed his mind ... my recollection is correct.

Jonathan?

Daniel Morgan Received on Fri Jan 31 2003 - 17:56:47 CST