Re: Explain Plan Weirdness

Urgh...forgot. I'm using cbo and the table was analyzed using estimate on 1064 rows.

Thanks

mkb

Sybrand Bakker wrote:

> First question:
> which optimizer are you using. Probably the rule based optimizer: no
> statistics on the tables and optimizer_goal in init<sid>.ora = choose.
> In
> this case the RBO will be used and hints will be -AFAIK- ignored.
> Secondly, the typical RBO behavior is to take the take with the lowest
>
> number of columns satisfying the where clause. So yes, if your delete
> statement is the way it is, RBO won't use the PK. Of course you can
> trick it
> to do that (which is more difficult with CBO), by simply writing AND
> CYN >
> 0.
> IMO you should do some measurements if you make this change, to see
> whether
> the PK is really more efficient. I doubt it very much, as there is no
> equality on the leading column of the index... Equality always has
> priority
> over range.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
> 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.
> >
> >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 Tue May 11 1999 - 08:22:32 CDT