Re: Hints and RBO?

Try optimizer_mode = RULE, with and without stats. You claim that should not make a difference (/*+ RULE*/ is equivalent to optimizer_mode = RULE).

You're in for a surprise!

Frank

spencer wrote:

> Martin had it right.
>
> For Oracle version 8.0.6, with optimizer_mode=choose, Oracle
> will use RBO only if at least one of these conditions is true:
> 1) the query includes /*+ RULE */ hint, or
> 2) none of the tables used in the query have statistics collected.
>
> That means if any hints other than /*+ RULE */ are specified in
> the query, Oracle will use CBO.
>
> "Philip Chee" <philip_at_aleytys.pc.my> wrote in message news:971536738.5418snx_at_aleytys.pc.my...
> > In article <1279.321T496T12073331lothar.armbruester_at_rheingau lothar.armbruester_at_rheingau.vistec.net writes:
> > >Bill Buchan wrote at 13-Okt-00 17:46:36 on the subject "Hints and RBO?":
 

> > >>However if I put HINTs in my SQL the EXPLAIN PLAN changes; therefore
> > >>the optimizer is responding to the hints.
 

> > >>I thought that you could only use hints with CBO?
> > >
> > >No, there are some hints that indicate the CBO and others that indicate
> > >RBO (e.g. /*+ RULE */ ;-) Hints like /*+ FIRST_ROWS */ or /*+ ALL_ROWS */
> > >and the like indicate CBO. Hints like /*+ INDEX */ or /*+ FULL */ do work
> > >with both optimizers (AFAIK).
> >
> > I had a case where FIRST_ROWS changed a query plan when there were NO
> > statistics. That was way back on 7.0.13.
> >
> > Philip
> >
> > ---=====================================================================---
> > Philip Chee: Tasek Corporation Berhad, P.O.Box 254, 30908 Ipoh, MALAYSIA
> > e-mail: philip_at_aleytys.pc.my Voice:+60.5.291.1011 Fax:+60.5.291.9932
> > Guard us from the she-wolf and the wolf, and guard us from the thief,
> > oh Night, and so be good for us to pass.
> > --
> > þ 20289.32 þ I think I think, therefore I think I am. I think.
> >
Received on Mon Oct 16 2000 - 12:26:16 CDT