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

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Wed, 09 Apr 2003 10:44:01 -0800
Message-ID: <F001.0057E311.20030409104401@fatcity.com>

DENNIS WILLIAMS wrote:
>
> 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
>

Jeff,

   I don't know if this is what Dennis had in mind when refering to the WHERE clause, but basically an index is the place where you look for the address (rowid) of a datablock. There are however a number of cases when you find in the index everything you need to answer your question, and spare the additional fetch of the datablock. Suppose you have something such as

     select a, b
     from T
     where C1 = :b1
       and C2 = :b2

where (C1, C2) is the primary key. The 'standard' way is to use the PK, find the rowid, then go straight to the block, scan it (don't forget this, it has some cost) and return the values for columns a and b. Now, if you have an index on (C1, C2, a, b) you directly get a and b in the leaf where C1 and C2 took you. This is a classic trick for improving response times of some very repetitively executed queries, and only works, of course, if ALL columns, which means those in the select list as well as the where clause, can be found in the index. It has some cost though:
- The more numerous the columns in an index, the larger its size, and therefore for range and full (fast or slow) scans you will have more blocks to visit,
- When you'll update a or b (you don't update primary keys, do you?) you will have the additional cost (and potentially a lot of contention induced) of maintaining the index
- and of course the more indexes, the costlier inserts and deletes. I am wondering, but I have never tried it, whether you could enforce the PK using the (C1, C2, a, b) index? It doesn't seem too far fetched to me and it could work with 9.x.

Regards,

Stephane Faroult
Oriole Software

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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 - 13:44:01 CDT