magjez wrote:
> Hello,
>
> I am trying to force a query to run using the Rule Based Optimizer
> (RBO). The query is fairly complex so I've included the <*+ RULE *>
> hint in every SELECT statement contained in it. The query contains a
> UNION ALL. When I run it, the execution plan suggests that it's using
> the RBO (Optimizer: Hint:RULE) but it is using the CBO. The CBO is
> causing the query to run much slower. I realize that I should
> eventually attempt to rewrite the query so that it is optimal for CBO
> but for the time being I would just like to be able to run the RBO. If
> I clear the statistics of the database, the default CHOOSE option does
> the RBO (no hints in query) and the query runs great. Any suggestions
> as to why the the RULE hint would be ignored? Any ideas on how to force
> it?
>
> Thanks,
> Maggie
>
The only reason I know of for the optimizer to use the CBO,
even when hinted to use the RBO, is the fact there are
statistics on (one of) the table(s) involved.
You may want to drop statistics on all objects involved
in the query.
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Thu Nov 03 2005 - 12:02:13 CST
