| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How are INDEXES BALANCED?
On 16 Sep 97 18:24:57 GMT, "Kevin Loney" <kevin.loney_at_astramerck.com> wrote:
>
>
>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
[snip]
>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.
You hit on the key point, in general deleted index space is not returned to the heap.
The index stays 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.
The number of levels (height of the tree) won't go down in general but that does not in any way imply that the index is unbalanced. It might imply that there are slots in the index that could be reused if that value is ever indexed again but the index is balanced.
alter index rebuild reclaims this deleted space.
alter index rebuild can quickly 'move' the index, quicker then re-reading the entire table and sorting in most cases.
alter index rebuild puts the pctfree back on the index blocks for new index entries to allow for inserts to ocurr without having to split blocks.
so, still there is a need for alter index rebuild even though it is not rebalancing the already balanced (but perhaps larger than needed) index.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |