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

(see answer below)

> -----Original Message-----
> From: Young, Jeff A. [mailto:jayoung_at_Trilegiant.com]
>
> Forgot to mention this is a 9i R2 db.
>
> ...
>
>
> 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?

How about this example? (from a 9.2 database) Note that for the same query, the execution plan is different once the index is created. Is that a good thing? Depends on your data distribution I suppose.

SQL> create table automobile

  2     (brand_name varchar2 (30),
  3      model_name varchar2 (30),
  4      engine_size number,
  5      constraint automobile_pk primary key (brand_name)
  6     ) ;

Table créée.

SQL> set autotrace traceonly explain
SQL> select /*+ rule */
  2 max (model_name)
  3 from automobile
  4 where brand_name > 'ABC' ;

Execution Plan

   0 SELECT STATEMENT Optimizer=HINT: RULE    1 0 SORT (AGGREGATE)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'AUTOMOBILE'
   3    2       INDEX (RANGE SCAN) OF 'AUTOMOBILE_PK' (UNIQUE)

SQL> create index automobile_idx1
  2 on automobile (brand_name, model_name) ;

Index créé.

SQL> select /*+ rule */
  2 max (model_name)
  3 from automobile
  4 where brand_name > 'ABC' ;

Execution Plan

   0 SELECT STATEMENT Optimizer=HINT: RULE    1 0 SORT (AGGREGATE)
   2 1 INDEX (RANGE SCAN) OF 'AUTOMOBILE_IDX1' (NON-UNIQUE)

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