Re: Hints and RBO?

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 Sun Oct 15 2000 - 21:37:04 CDT