Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How are INDEXES BALANCED?
This is a multi-part message in MIME format.
--------------F14DD78BA9FEA31756BDC4FB Content-Type: text/plain; charset=us-ascii Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Transfer-Encoding: 7bit
Dennis Taylor wrote:
> Kevin Loney <kevin.loney_at_astramerck.com> wrote in article
> <01bcc367$b384bd80$aa9e02a7_at_LONEYK.astramerck.com>...
> >
> > Oracle
> > leaves the rest of the index alone and splits the J entry blocks,
creating
> > new levels
> > in the index only at that part of the index. So the index levels
may now
> > be:
> >
> > top level: (branch blocks)
> > A-H I-P Q-Z
> >
> > next level: (branch blocks)
> > A, B, C, D, E, F, G, H, I, J, K, L, M, N, O , P, Q, R, S, T, U, V,
> W, X,
> Y,
> > Z
> >
> > next level:
> > for all except the Js, point to leaf blocks. For the J's:
> > new branch blocks:
> > JA, JB, JC, JD, JE, JF , etc.
> >
> > next level:
> > leaf blocks for J's.
> >
> > That's not balanced. It has more levels at the point of greatest
activity.
> >
>
> Kevin:
>
> There is no requirement (AFAIK) for a balanced btree to be
> *completely*
> balanced. You are talking about one extra level of branch blocks in
> the
> area of greatest activity. That's exactly the same behaviour I got
> with my
> implementation (which I'll grant you wasn't of professional level),
> and the
> algorithm as I understand it allows a +/- 1 depth over the entire
> index.
> I'd be more concerned if there was more depth variation.
>
> The thing is, your example shows that Oracle is doing some explicit
> balancing. It looks at this point like the argument is about whether
> the
> balancing is thorough enough or not.
Correct me if I am wrong here. I think the point Kevin is making here is that leaf access is slower. My question is this. Does Oracle continue to leaf out additional levels if the index is not re-built? My assumption is that it does. Therefore, reorganization of indices is important in highly volatile areas of a database. If so I think it is very intuitive since auto-balacing could produce very high overhead.
--------------F14DD78BA9FEA31756BDC4FB Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Mark E. Porterfield Content-Disposition: attachment; filename="vcard.vcf" begin: vcard fn: Mark E. Porterfield n: E. Porterfield;Mark org: Management Consulting Solutions Inc., A BRC Company email;internet: portrfld_at_nauticom.net title: Sr. Consultant tel;work: 412.612.7938 tel;fax: 412.779.9199 tel;home: 412.935.5502
--------------F14DD78BA9FEA31756BDC4FB-- Received on Thu Sep 18 1997 - 00:00:00 CDT