Re: Hints and RBO?

Bill Buchan wrote at 13-Okt-00 17:46:36 on the subject "Hints and RBO?":  

>I'm confused. Please help me resolve the flaw in my logic (I have
>RTFM but I'm missing the bit I want to understand!):
 

>I have set the optimizer mode to CHOOSE but have not gathered any
>statistics. Therefore the optimizer will default to RULE based?

Yes, I think so. Since the CBO needs statistics to do its work Oracle has to use RBO when no stats are present. But beware, I had some SQLs that caused a parsing time of over 2min! I don't know exactly what happened but the SQL queried 12 or more tables that had no stats and the optimizer mode was CHOOSE. This was Oracle 7.2 though.

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

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | lothar.armbruester_at_rheingau.vistec.net
Hauptstr. 26            | la_at_oktagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de