RE: use of reverse key index,cost based optimizer

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).