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: Index question

RE: Index question

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 10 Aug 2004 11:18:51 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKCEOGFCAA.mwf@rsiz.com>


Unless column d is routinely quite long in actual content there is unlikely to be a downside.

The theoretical downside that could occur is if the extra keylength resulted in significantly more more blocks being scanned in the index in queries you actually ended up using, and/or caused additional depth for individual key lookups.

Conversely, if adding some modest in length columns to the index frequently results in your actual queries not having to visit the table for data, having an even longer index can result in a performance benefit.

Actual sizes, update index maintenance requirements, and queries used dominate over the theoretical concerns.

(By the way, at your release level, Oracle can use non-first index columns in some contexts, as well.)

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Rick Stephenson Sent: Tuesday, August 10, 2004 11:09 AM
To: Oracle ListServ (oracle-l_at_freelists.org) Subject: Index question

I have a query that references Table t column c in the where clause. Table t already has an index on (column c, column d). Is there any reason to add an index to table t that contains just column c? I know that Oracle will use the other index because of the leading column, but is there a performance hit due to it being a composite index?

I am running Oracle EE 9.2.0.5.

Thanks,

Rick Stephenson



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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Tue Aug 10 2004 - 10:22:47 CDT

Original text of this message

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