Explain Plan Weirdness

I've got this table T1, as follows:

 CYN                     NOT NULL NUMBER(4)
 CQN                    NOT NULL NUMBER(1)
 AC                          NOT NULL CHAR(10)
 PIC                       NOT NULL CHAR(5)
 JC                        NOT NULL CHAR(1)
 CC                      NOT NULL CHAR(1)

with a pk (T1_PK) of all the above columns and an index on T1_IDX1(AC, PIC) columns.

I run explain plan on this SQL:

delete T1
where CYN = 1997
and CQN = 3
and AC = '2175322166'
and PIC = '17464'

and I get this:

Execute Plan

  TABLE ACCESS BY ROWID T1
     INDEX RANGE SCAN T1_IDX1 Question:
Why does the optimizer not use the PK? Would it not be reasonable to assume that the PK provides a better access path than the index T1_IDX1? Funny thing is, if I use all the columns in the PK for the delete, the explain shows use of the PK. But even if I include the following hint:

delete /*+ index(T1, T1_PK) */ T1
...
...

The plan still comes out as above. BTW, this is on Oracle 7.3.2/Solaris 2.5.1.

So, can anyone enlighten me as to why the optimizer chooses the index and not the pk or am I missing something really basic here.

Thanks

mkb Received on Mon May 10 1999 - 15:59:48 CDT