Re: Explain Plan Weirdness

If you have done a simple analyze, then the stats on the PK index are relevant only to the queries that use the full index in the query, and Oracle is pretty bad at estimating the effect of using the leading columns of a partially used index. This is likely to explain why the PK is ignored when you don't hint it explicitly.

When you give an explicit hint -- less obvious. I have found that Oracle does seem to ignore perfectly sound hints from time to time, especially on the older versions.

This isn't SQL embedded in PL/SQL is it ? If so, check you have left a space between the '+' and the word INDEX

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

MKB wrote in message <37382F9D.C8C932C5_at_mci.com>...
>Urgh...forgot. I'm using cbo and the table was analyzed using estimate
>on 1064 rows.
>
>>> MKB wrote in message <3737493C.F936F4_at_mci.com>...
>> >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.
>> >
Received on Tue May 11 1999 - 15:19:47 CDT