| 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
Almost certainly, there's not a significant performance hit, unless
possibly if you're doing INDEX FULL SCAN or INDEX FAST FULL SCAN index
access. If so, and if there are *lots* of rows and column d is
significantly large, then the size of the index may have some impact.
Also, I suppose even in the case of INDEX RANGE SCAN or INDEX UNIQUE SCAN, the size of the columns in the key may play into it, to some degree. It's possible, depending on number of rows, that the index's BLEVEL increases if you add the second column. But, consider that it's unlikely, and also consider that even if it is true that the BLEVEL is bumped by one, you're only adding one consistent get per INDEX (RANGE|UNIQUE) SCAN for the extra level. Also, the second column could have a negative effect on the clustering factor, causing the index to not be used or be used less frequently in some cases.
Also, don't forget the extra recursive SQL to maintain the extra index, if you add an index on (col_c) alone.
Bottom line, every system is different, but my gut tells me that if you have (col_c,col_d) indexed, you don't need to worry about indexing (col_c).
If you're really concerned, run a 10046 trace against each case and compare the results. Also, don't forget to consider the extra index maintenance required in the case w/ the additional index.
-Mark
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
-----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?
=20
I am running Oracle EE 9.2.0.5.
=20
Thanks,
=20
Rick Stephenson
=20
-- 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:28:17 CDT
 |
 |