Re: Statistics and optimizer

That is correct. If you have any hint other than "rule", the entire query gets planned by CBO. Any hint, including weird things like push_subq or whatever.

So, if you want to use rule, don't use hints, other than "rule".

But, why are you hung up on RBO anyway? Why not just use CBO and drive the CBO quirks with appropriate hints. Or, tune the thing and then use the plan stability feature to enforce the tuned queries plan? If you are in a large scale environment, I suspect you are better off with CBO anyway. I mean, RBO is rather harsh. It will use an index if it can, even if it costs you performance. And, you cannot use many of the newer features with it, such as bitmap indexes, hash anti-joins, etc.

• ricky

Ann Myhre wrote:
>
> "Ricky Sanchez" <rsanchez_at_more.net> wrote in message
> news:3AF74243.B4ECA8AA_at_more.net...
> > Actually, a simple way of looking at it is that if *any* hint is used,
> > other than "rule", then CBO will be used. If CBO is used for any part of
> > the query, ie subqueries, then the entire statement will use CBO.
> >
> > - ricky
>
> That really confuse me. What you say is that, if I use any hints exept RULE,
> and even though I haven't collected ANY statistics (there is absolutely NO
> statistics genererated an any object whatsoever), Oracle (optimizer) will
> choose CBO?
> My problem is that I want of course in most cases optimizer to choose CBO,
> but from the information I have got it seems that it will choose RBO if I
> haven't rembered to use ANALYZE first. I have tried different approches and
> from the EXPLAIN PLAN it also seems to choose CBO even though I have not
> used ANALYZE. But maybe I read something wrong?
>
> Ann
Received on Tue May 08 2001 - 19:13:18 CDT