| 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 on pk and composite index with pk as first column. Why
Forgot to mention this is a 9i R2 db.
-----Original Message-----
Sent: Wednesday, April 09, 2003 1:12 PM
To: 'ORACLE-L_at_fatcity.com'
Why
Not sure about that yet. Would it make a difference? This system is about 3 years old and has been evolving quickly. I haven't searched the application to see if there is a query with the additional columns.
Let's assume that there is a query being used by the application that includes the other 2 columns. Would an index across A, B, and C in addition to the index on A (the primary key) be appropriate? I can't get any more unique than a primary key, so why would adding columns to an index that already has the primary key as the leading column make things better?
-----Original Message-----
Sent: Wednesday, April 09, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L
Why
Jeff
At first glance, I agree that there appears to be some redundancy here. I wonder if someone wrote some queries that reference the additional columns in a WHERE clause? Can you confirm that?
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Wednesday, April 09, 2003 11:49 AM
To: Multiple recipients of list ORACLE-L
We have a few tables that have an index on the primary key of a table, but also have a unique index on the primary key plus some other columns.
Example. Table X with columns A, B, C, and D has a primary key on A. An additional composite index is on A, B, and C in that order.
When would there be performance benefits to having both indexes? Can someone point me to documentationt that might help? I didn't find anything in particular to this specific issue in the oracle docs.
I guess I'm looking at it like this. If you've got an index on the primary key already which is unique in itself, what is adding another index with that same column plus some other trailing ones going to help? Wouldn't the index with the primary key already get you down to one row?
I thought maybe if you were querying on a range (ie. A > 10000), then maybe the second index might help, but I still can't quite rationalize this.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Young, Jeff A.
INET: jayoung_at_trilegiant.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).
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).
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 Wed Apr 09 2003 - 13:38:41 CDT
 |
 |