Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index on pk and composite index with pk as first column. Why

RE: 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 12:04:22 -0800
Message-ID: <F001.0057E5E8.20030409120422@fatcity.com> 





Thanks all.  And, I wouldn't be changing anything immediately :)  The system
is running and I wouldn't want to be the one to further reduce performance,
but I may do some further investigation based on your responses to see how
the table that these indexes are on are actually being used in the
application.



Given how this system was put together (green java developers, changing
requirements, not enough time, etc, etc, you know, the same old story), I do
question things.  I don't always have the answers (probably not most of the
time!), but when I see things that look funny to me, I have to know why, and
I don't assume there must have been a good reason...






-----Original Message-----


Sent: Wednesday, April 09, 2003 3:19 PM


To: Multiple recipients of list ORACLE-L
Why




Stephane - Thanks again for sharing your expertise.



Jeff - Listen to Stephane. What I was clumsily getting at is that the
tendency is to assume your predecessor was a knucklehead and immediately
change everything. I've been bitten by that before so my tendency is to take
a cautious approach. It may turn out that these indexes are not needed, but
there is a chance some queries may work more slowly.



Dennis Williams


DBA, 40%OCP, 100% DBA



Lifetouch, Inc.


dwilliams_at_lifetouch.com 




-----Original Message-----


Sent: Wednesday, April 09, 2003 1:44 PM


To: Multiple recipients of list ORACLE-L
Why




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).
-- 
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).


Received on Wed Apr 09 2003 - 15:04:22 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US