| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index question
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
 |
 |