RE: Index question
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Aug 2004 11:32:25 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rick Stephenson
Sent: Tuesday, August 10, 2004 11:09 AM
To: Oracle ListServ (oracle-l@xxxxxxxxxxxxx)
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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
