Indexes and the rule_based optimizer

Please help.....

I have problems on a ORACLE 7.1.6.2.5 database. I created a table with a primary key and a non-unique index as shown below.

Create table test (

col1 number(5),
col2 number(5),
col3 number(5),
col4 number(5));

alter table test
add constraint pk_test primary key (col1,col2);

create index test_ind on test (col3,col4);

I then issued the following query:

explain plan for
select *
from test
where col1 = 1 and col3 = 1;

explain plan output when index test_ind was created last:

ID P_ID OPERATION OPTIONS OBJECT_NAME
--- ---- -------------------- --------------- -------------------------

  0      SELECT STATEMENT
  1    0 TABLE ACCESS         BY ROWID        TEST
  2    1 INDEX                RANGE SCAN      TEST_IND

I then dropped the primary key, recreated it and issued the same sql code.

explain plan output when primary key was recreated:

ID P_ID OPERATION OPTIONS OBJECT_NAME
--- ---- -------------------- --------------- -------------------------

  0      SELECT STATEMENT
  1    0 TABLE ACCESS         BY ROWID        TEST
  2    1 INDEX                RANGE SCAN      PK_TEST

The database use the rule-based optimizer. The world according to ORACLE7 Server Concepts Manual p13-33, the primary key must always be used in scenario explained above.

Does anybody have an idea why this is happening?

Werner Adams                            Tel :    +27 (0)13-2472717
Database Administrator                  Fax:     +27 (0)13-2472622
Columbus Stainless                      E-mail:  adams.werner_at_columbus.co.za

Always question! Never simply accept and follow!