Re: Statistics and optimizer

And it is worth remembering that certain physical features will automatically make Oracle run cost-based - e.g. queries using:

    partitioned segments
    parallel-defined segments
    IOTs
will automatically invoke CBO, and use the best guess statistics (i.e. little more than how many used blocks in the segment).

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Ricky Sanchez wrote in message <3AF88BAA.12992A86_at_more.net>...


>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