Re: Reverse key Indexes Oracle 8.0

In article <x2ogcdbh3k.fsf_at_zdbaora.nat.bt.com>,   Robin Smith <smithrc_at_zdbaora.nat.bt.com> wrote:
> cccccc_at_weee.co.uk (gw) writes:
>
> > Can you please tell me when you would use the reverse key option on
> > create index?
> >
> > What advantages does this give ?
> > Any examples of use?
> >
> > The oracle 8 documentation is fairly sparse in this area.
> >
> > Thanks
> > GW
>
> Reverse key indexes are used to stop b*tree indexes "exploding" with a
> sequential dataload. If your first key of an index is a sequence then
> records will always be added to the right hand side of the index so it
> will become skewed. If you reverse the key then entries will also go
> into the middle of the index so will become self balancing.
>
> e.g.
>
> sequence reverse
> 1000 0001
> 1001 1001
> 1002 2001
>
> etc.
>

Actually, I believe that a closer reading of the manuals reveals that reverse key indexes were added to Oracle to solve a problem of multiple inserts needing to update the same index block in an Oracle Parallel Server, OPS, environment. The cross instance locking on index updates to the same block can be a serious performance problem under OPS while it does not present a problem under an exclusive database.

I believe that reverse indexes preclude the use of the index by the optimizer to support range scans which is a heavy trade off for a lot of applications.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Nov 29 1999 - 09:00:11 CST