Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: use of reverse key index,cost based optimizer

Re: use of reverse key index,cost based optimizer

From: Jared Still <>
Date: Sun, 01 Jun 2003 10:29:38 -0800
Message-ID: <>

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:
Author: Jared Still

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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).
Received on Sun Jun 01 2003 - 13:29:38 CDT

Original text of this message