Re: unbalanced indexes -- common wisdom?

The following statement appears in the article you quote:

<quote>

    When a row is deleted from a table, the Oracle database will not     reuse the corresponding index space until you rebuild the index.     That's the way indexes are implemented in Oracle <end quote>

The statement is untrue - but commonly quoted as fact.

There are a very few special cases where the nature of the application, and the method of implementation result in space being wasted unduly. This wastage is a consequence of the fact that Oracle does not merge sparsely used blocks according to the full B-tree algorithm.

The following statement also appears
<quote>

    index blocks will not be put on the free list for reuse. <end quote>

This is also untrue, and it is easy to prove (left as exercise) that it is untrue.

The following statement also appears
<quote>

    For example, a three-level index might have a node that experiences     heavy INSERT activity. This node could spawn a fourth level without     the other level-three nodes spawning new levels.     That makes the index unbalanced.
<end quote>

This first part of this statement is TRUE - and in fact ideal. The second part is very misleading. No matter how perfectly an index is maintained, it will always be possible to ensure that you can get it to a state where either just one 3rd level node has split to produce an "imbalance" or (the only possible alternative) every 3rd level node has to split to leave the index half-populated if you want "balance" - i.e. every leaf has to be at the fourth level.

The important point about balance b-trees is that no leaf is MORE THAN ONE level deeper that every other leaf.

No more time for reading the second page, tea's up.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



Mikito Harakiri wrote in message ...


>Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message

news:<rupneuc1hcf9rviejkd6kmhtdm4vbstrav_at_4ax.com>...


>> On 22 May 2002 11:09:35 -0700, mikharakiri_at_yahoo.com (Mikito Harakiri)

>> wrote:

>>

>> >There are so many useless options for creating an index, and the most

>> >important one -- "balanced" -- seems to be missing. Would the standard

>> >B-Tree implemented in any time in the future?

>>

>> Just FYI: ordinary Oracle indexes *are* balanced.

>

>I'm sorry, I was distracted by the title:

>http://gethelp.devx.com/techtips/oracle_pro/10min/10min0601/10min0601.asp

>

>I really meant fragmented. I know, this ugly index maintenance is

>unneccessary, as it doesn't improve performance -- but there is still

>a waisted storage. Since oracle created every possible option to

>manage storage, how did they forget about this one?