Re: How are INDEXES BALANCED?

original question:
> >> > > I need to know if an index is not balanced, how do I get it
 balanced
> >or is it done automatically? For example, when I enter an order number
 to
> >

 My reply:
> >> Oracle does not automatically rebalance its indexes.
> >> If it did, then the performance of your inserts would be
 unpredictable.
> >> The only way to rebalance an index is to drop and recreate it, or use
 the alter
> >> index rebuild option in 7.3.

Tom Kyte replied:
>
> the indexes stay balanced (it would be a fairly usless index that
> became widely unbalanced). From the server concepts manual:
>
> <quote>
> The B*–tree structure has the following advantages:
> - All leaf blocks of the tree are at the same depth, so retrieval of
> any record from anywhere in the index takes approximately the
> same amount of time.
>
> - B*–tree indexes automatically stay balanced.
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>

and my reply to that:
Tom: I am familiar with what the manual says; I have repeated it myself. However, I have since found that
Oracle does not balance its indexes. In order to reduce the impact of index restructuring during inserts,
no rebalancing occurs. Thus, the user sees an application in which insert takes about the same time.
The burden of restructuring is placed on the shoulders of the DBA - and it occurs in batch mode during
index recreation, thus eliminating the effect on the users.

Instead of balancing the index, Oracle adds more levels to it at the most-used points. Try a bunch
of deletes and inserts and see what happens to the number of levels :) If index balancing occurred automatically and indexes reused deleted space, there would be no
need for alter index rebuild.

Kevin Loney. Received on Tue Sep 16 1997 - 00:00:00 CDT