Re: Explain Plan Weirdness

From: Pete Sharman <psharman_at_us.oracle.com>
Date: Mon, 10 May 1999 16:33:29 -0700
Message-ID: <37376CC9.D3A02B6F@us.oracle.com>

Sybrand

One point of clarification. There are some hints that will force cost-based optimization regardless of the presence or otherwise of statistics. Whether that's sensible to do of course is another question.

HTH. Pete

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
> >
> >

--

Regards

Pete

---

Pete Sharman                              Email: psharman_at_us.oracle.com
Project Manager                           Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education            (650)607 0109 (local)

Oracle Corporation

SQL> select standard_disclaimer, witty_remark   2 from company_requirements;

Opinions are mine and do not necessarily reflect those of Oracle Corporation

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

---

Received on Mon May 10 1999 - 18:33:29 CDT