RE: use of reverse key index,cost based optimizer

Binley - Thanks for filling in the details. I suspected the answer might be more complicated than just "balanced" or "unbalanced". Have you tried reverse key indexes yourself? Results?

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Monday, June 02, 2003 10:35 AM
To: Multiple recipients of list ORACLE-L

The documentation is correct, but left out a lot of background information.

Oracle*trees are height balanced. That means that the level from root to branch block is always the same. In the case of monotonically increasing indexes, keys are always inserted at the high end. If keys are also deleted at the low-end, this leads to a high density high-end blocks and low density low-end blocks since blocks remain on the tree as long as there are some keys remaining. While the height is balanced, the density is lop-sided. Efficiency suffers as sparse leaf blocks are cached, or index-scanned.

But the real penalty is in multi-instance OPS/RAC where the high-end blocks are in popular demand for inserts, causing instance ping-pong effects, especially OPS for which it was invented for. RKI simply unties the key-value from the physical block storage leading to a spread in terms of storage and contention for the same blocks. The major down-side is index-range scans are no longer meaningful, and one would be hard-pressed to justify its use in single-instance situations.

• Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Monday, June 02, 2003 12:14 PM

> Jared
> Nope, been burned on that one. I just couldn't think of an easy way to
> test this, but thought if I pointed it out and somebody knew it wasn't
true
> it might irritate them into responding. Has anybody experienced success
with
> reverse key indexes?
>
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Sunday, June 01, 2003 1:30 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Dennis, are you implicitly asserting that you
> trust the documentation 100%? ;)
>
> On Sunday 01 June 2003 06:44, DENNIS WILLIAMS wrote:
> > Jared
> > Like you, I have always understood that b+ tree indexes maintain
their
> > balance automatically. The statement I was referring to is in the
Oracle9i
> > Database Performance Planning manual, Chapter 1:
> >
> > "Use of sequences, or timestamps, to generate key values that are
indexed
> > themselves can lead to database hotspot problems, which affect response
> > time and throughput. This is usually the result of a monotonically
growing
> > key that results in a right-growing index. To avoid this problem, try to
> > generate keys that insert over the full range of the index. This results
> in
> > a well-balanced index that is more scalable and space efficient. You can
> > achieve this by using a reverse key index or using a cycling sequence to
> > prefix and sequence values."
> >
> > I hate to admit it, but I ran across this tidbit while I was studying
for
> > the OCP. I have no idea what a well-balanced index means. Had good
> > supportive parents?
> >
> > But just when you think the reverse key index must be great, the
Concepts
> > manual points out:
> > "Using the reverse key arrangement eliminates the ability to run an
index
> > range scanning query on the index. Because lexically adjacent keys are
not
> > stored next to each other in a reverse-key index, only fetch-by-key or
> > full-index (table) scans can be performed."
> >
> > Dennis Williams
> > DBA, 80%OCP, 100% DBA
> > Lifetouch, Inc.
> > > manjunath
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Binley Lim
  INET: Binley.Lim_at_xtra.co.nz

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).