Re: unbalanced indexes -- common wisdom?

"Mikito Harakiri" <mikharakiri_at_yahoo.com> wrote in message news:bdf69bdf.0205231311.603b7284_at_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.
>

No it's not. You can't flashback through DDL, and never have been able to since that particular feature was invented for 9i. Rebuilding the index is DDL. Therefore, the rebuilt index can't be reconstructed by flashback. The table hasn't been subject to DDL so it can be.

As for all that stuff about Oracle not wanting to reorganise the index nodes because "it will complicate restoring the state back", that's just not true. Rollback (undo, if you must) is there precisely to permit the 'restoration of a block's state', and it makes not a jot of difference what sort of block you are talking about -leaf node or data block, no difference.

Oracle doesn't "reorganise the index nodes" in the way you seem to require that it should because to do so would be an expensive waste of time, slowing down every bit of DML on the system: the very next piece of DML would likely 'disorganise' the structure straightaway, and require yet further expensive 'reorganisation'.

Actually, it's near-impossible to induce an index to grow in height beyond about 3 or 4 except under really weird circumstances, and Oracle's re-balancing and re-organisation algorithms are pretty efficient. The need for index rebuilds is significantly less than you might think.

HJR
> "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 - 17:04:46 CDT