Re: How are INDEXES BALANCED?

>If you delete/update indexed values *and* you don't reuse those values
 then the
>index will *still be balanced* but it will contain values that don't point
 to
>data. That is not out of balance. The height of the tree is balanced,
 there
>are just entries in there that don't point to rows anymore. The time to
 access
>any given value with in the index tree is equitable.

Tom,

In theory, that is true. In practice, Oracle doesn't do it. Let's take an example that
doesn't have deletes involved.
Insert 52000 names into a table , 2000 for each letter of the alphabet. Now, insert 52000 new names, all starting with the letter J. (2000 JAs, 2000 JBs, etc)
Oracle SHOULD rebalance the index so that all the J entries are at the same level as the rest of the index entries. But that's not what happens. 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.

If you drop and recreate the index, you get a balanced index, where the first branch block level is A-C, D-E, F-G, H-I, JA-JC, JD-JE, JF-JG , etc.

That is balanced.

So if you do many deletes, you should rebuild your indexes. And if you do many inserts in one part of the data value range, you should rebuild your indexes. That's the important thing.

Kevin. Received on Wed Sep 17 1997 - 00:00:00 CDT