Re: index rebuilding...

"Howard J. Rogers" wrote:

> He's changed his mind.
>
> Regards
> HJR
>
> "DA Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3E3B0D3F.9916449D_at_exesolutions.com...
> > "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
> >

So now you ARE speaking for him. <g>

Thanks.

Daniel Morgan Received on Fri Jan 31 2003 - 19:12:27 CST