Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: unbalanced indexes -- common wisdom?

Re: unbalanced indexes -- common wisdom?

From: Mikito Harakiri <mikharakiri_at_yahoo.com>
Date: 23 May 2002 14:11:13 -0700
Message-ID: <bdf69bdf.0205231311.603b7284@posting.google.com>


Thank you, Richard.

One of the reasons may be oracle unique "read history" transaction isolation semantics. It is easy to understand how it works for ordinary blocks with table data, but what really happens with index blocks? My guess is that oracle doesn't want to reorganize blocks unnecessarily, because it will complicate restoring the state back.

Here is an interesting experiment. My table with a PK index contained 1 record as of yesterday. I inserted 9999 more records, then deleted the old record and half of the new ones. I'm still able to read my yesterday's data as:

SELECT --+full(emp1)

   empno FROM emp1
   AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY)    WHERE empno = 7934 -- yesterday's record; no longer in the table

both through FTS and index. Now, if I rebuild the index then the index path gives me an ORA-08176 error, while FTS still works. I think this is a bug.

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<OEWG8.16068$b5.55993_at_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 Thu May 23 2002 - 16:11:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US