Re: unbalanced indexes -- common wisdom?

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Thu, 23 May 2002 10:03:35 +1000
Message-ID: <OEWG8.16068$b5.55993@newsfeeds.bigpond.com>

Actually a badly fragmented index can lead to performance problems if the wasted space is sufficient to unnecessarily increase the "height" of the index (fixed with ALTER INDEX nameofindex REBUILD command) or if range scans are commonly performed, resulting in an unnecessary number of leaf blocks being read (fixed again with ALTER INDEX nameofindex REBUILD or ALTER INDEX nameofindex COALESCE).

In summary, index maintenance may be necessary but Oracle naturally has thought of it. Check Oracle doco to determine which command is more appropriate.

Richard Foote
"Mikito Harakiri" <mikharakiri_at_yahoo.com> wrote in message news:bdf69bdf.0205221358.4b35f7b6_at_posting.google.com...
> 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?
Received on Wed May 22 2002 - 19:03:35 CDT