Eric -- Thanks for the question regarding "Column Definition Order on Composite Primary Kay", version 10.2.0

You Asked

Suppose I have a table with composite primary key

create table RULE_INST (
RULE_ID NUMBER(15) not null,
TEST_ID NUMBER(15) not null,
CHECK_ID NUMBER(30) not null,
MOE_TST_GRP VARCHAR2(32) default 'none',
CONSTRAINT RULE_INST_PK PRIMARY KEY (RULE_ID, TEST_ID, CHECK_ID MOE_TST_GRP)
);

RULE_ID and TEST_ID are FK references.
Suppose that most queries filter by TEST_ID reducing 300 millions rows down to about a million rows. Many of the queries' explain plan shows a full table scan, rather than using the primary key index. I have been told that moving TEST_ID as the first column in the table AND the index, as shown below, will allow Oracle (10g) to use the primary key, rather than the full table scan. Is this true? Is it both, or just the order of the columns in the definition of the index, or just the order of the columns in the definition of the table?

create table RULE_INST (
TEST_ID NUMBER(15) not null,
RULE_ID NUMBER(15) not null,
CHECK_ID NUMBER(30) not null,
MOE_TST_GRP VARCHAR2(32) default 'none',
CONSTRAINT RULE_INST_PK PRIMARY KEY (TEST_ID, RULE_ID, CHECK_ID, MOE_TST_GRP)
);

and we said...

by default, the index generated to support the primary key constraint will have columns in the order of the primary key itself.

if you frequently "where test_id = :x", then yes, test_id should likely be "first" in the constraint, so it is first in in the index that is being created for you.

Reviews

Column order of table material?   February 8, 2007 - 2pm Central time zone Bookmark | Bottom | Top Reviewer: Dana from Phoenix, AZ USA Since you can do the following (in 10G): SQL> create table tst_pk( 2 rule_id number not null, 3 test_id number not null, 4 filler varchar(100) null, 5 constraint t_pk primary key(test_id,rule_id)); Would there be a particular reason for changing the column order? Just curious. Followup   February 8, 2007 - 4pm Central time zone: you would list it first in the constraint - I don't care what order the table shows. Column Definition Order on Composite Primary Key answer   February 9, 2007 - 11am Central time zone Bookmark | Bottom | Top Reviewer: Eric Robertson from Vienna, VA The response started with 'by default'. I am interested in understanding the 'non default' cases and would appreciate a pointer to the Oracle documentation on these cases. Thanks. Followup   February 12, 2007 - 8am Central time zone: what 'non default' cases? not sure what you mean by that

---

Write a Review