Re: Does order of a PK columns in a SELECT matter in the speed of the query?

From: Roy Brokvam <roy.brokvam_at_conax.com>
Date: Wed, 2 Jun 1999 09:46:51 +0200
Message-ID: <ql553.62$PO3.447@news1.online.no>

Ravi Verma wrote in message <01beac84$cc483f60$a2694e0c_at_Verma.medco.com>...
>Oracle is very particular about the order in cases where composite indexes
>are available.
>In order for a query to use the index, rather than go for a full table
>scan, the where clause should have the columns in the order that they are
>indexed.
>
>AV
>--------------------------------------------

[snip]

That's not the case with my version (7.3.4.1); for both the examples, a unique index scan is chosen by the optimizer. If your RDBMS chooses differently, you should report it to Oracle as a bug!

SQL> create table tab1
  2 (

  3        col1 varchar2(1) not null,
  4        col2 number not null,
  5        col3 varchar2(1) not null

  6 );

Table created.

SQL> alter table tab1
  2 add
  3 (

  4        constraint pk_tab1
  5        primary key ( col1, col2, col3 )

  6 );

Table altered.

SQL> delete from plan_table;

2 rows deleted.

SQL> explain plan for
  2 select * from tab1 where col1 = 'X' and col2 = 10 and col3 = 'Y';

Explained.

SQL> select

  2        lpad( ' ', 2 * ( level - 1 ) ) ||
  3        operation || ' ' ||
  4        options || ' ' ||
  5        object_name "Query plan"
  6  from
  7        plan_table
  8  start with
  9        id = 0
 10  connect by
 11        prior id = parent_id;

Query plan

---

---

SELECT STATEMENT
  INDEX UNIQUE SCAN PK_TAB1 SQL> delete from plan_table;

2 rows deleted.

SQL> explain plan for
  2 select * from tab1 where col2 = 10 and col3 = 'Y' and col1 = 'X';

Explained.

SQL> select

  2        lpad( ' ', 2 * ( level - 1 ) ) ||
  3        operation || ' ' ||
  4        options || ' ' ||
  5        object_name "Query plan"
  6  from
  7        plan_table
  8  start with
  9        id = 0
 10  connect by
 11        prior id = parent_id;

Query plan

---

---

SELECT STATEMENT
  INDEX UNIQUE SCAN PK_TAB1 SQL> drop table tab1;

Table dropped.

SQL> spool off

Regards,

Roy Brokvam
roy.brokvam_at_conax.com Received on Wed Jun 02 1999 - 02:46:51 CDT