Re: How are INDEXES BALANCED?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/09/16
Message-ID: <341ea751.9223011@newshost>#1/1

On 16 Sep 1997 14:42:41 GMT, "Dennis Taylor" <no_spam_dammit_at_ipipeline.net> wrote:

>
>Kevin Loney <kevin.loney_at_astramerck.com> wrote in article
><01bcbf88$4315f060$aa9e02a7_at_LONEYK.astramerck.com>...
>>
>> > > 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
 an
>>
>> Then get slapping. 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.
>>
>> Kevin Loney.
>
>Hm. I was told that it did by one of the Oracle instructors - Al Williamson
>(Oracle Canada) - who has a pretty impressive history, with Oracle and
>otherwise. And your source is ?
>

That's right, 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. ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
• All blocks of the B*–tree are three–quarters full on the average.
• B*–trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.
• Inserts, updates, and deletes are efficient, maintaining key order for fast retrieval.
• B*–tree performance is good for both small and large tables, and does not degrade as the size of a table grows. </quote>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 16 1997 - 00:00:00 CDT