Re: unbalanced indexes -- common wisdom?

Jonathan Lewis wrote:

> 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?

Lets add this to the semi-official Oracle myths.

Daniel Morgan Received on Thu May 23 2002 - 14:41:23 CDT