Oracle-L: Index on pk and composite index with pk as first column. Why?

From: Young, Jeff A. <jayoung_at_trilegiant.com>
Date: Wed, 09 Apr 2003 08:48:50 -0800
Message-ID: <F001.0057E05E.20030409084850@fatcity.com>

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). Received on Wed Apr 09 2003 - 11:48:50 CDT