Re: Index on pk and composite index with pk as first column. Why

The second index could help (performance-wise) only, if all the columns in "select" list included in the index, meaning reading only index would satisfy the query.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, April 09, 2003 2:13 PM

> 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?
>
> - Jeff
>
> -----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.
>
> - Jeff
>
>
> --
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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).
> --
> 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).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.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).