Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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_at_radisys.com>
Date: Fri, 30 May 2003 18:44:39 -0800
Message-ID: <F001.005A7635.20030530184439@fatcity.com>


Dennis,

My understanding of B*tree is that it is always balanced. Monotonically increasing
keys will create a right hand index, but nonetheless balanced.

If wrong, I'm sure to be corrected. :)

Also, I don't believe the reverse key index will help queries any. I'm guessing that under
normal circumstances it would increase the number of index blocks that needed to be
cached.

In the case of a range scan, it would definitely not perform as well, and increase the likelihood
of a FFS or FTS, depending on the queries normally used in a system.

The primary purpose of these was to reduce block pings on OPS IIRC, which would also reduce
block contention on inserts as you said.

Jared

DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM> Sent by: root_at_fatcity.com
 05/30/2003 12:09 PM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: use of reverse key index,cost based optimizer


helpdesk

   I don't see where anyone responded. If you look up reverse key index in the documentation, it says something about if you have a column where most of the values have leading values that are close. Reverse key will help the
btree of the index be more balanced. That helps on queries. And on inserts you aren't continually hitting the same block, but spreading the inserts.

   Oracle has two SQL optimizers, rule-based and cost based. The cost based
is more sophisticated. You first populate statistics on your tables. When creating an execution plan for your SQL the CBO will consider those statistics. Does that answer your questions?

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

-----Original Message-----
Sent: Friday, May 30, 2003 1:25 AM
To: Multiple recipients of list ORACLE-L

hai gurus

please tell use of using reverse key index and what exactly cost based optimizer
 thanks in advance
manjunath

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
  INET: helpdesk.hcl_at_bplmail.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:
  INET: Jared.Still_at_radisys.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). Received on Fri May 30 2003 - 21:44:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US