Re: Optimizer hint <*+ RULE *> question

Frank van Bortel wrote:
> 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...

This is not the only reason the CBO could be enabled. The following features require use of the CBO:

Partitioned tables and indexes
Index-organized tables
Reverse key indexes
Function-based indexes
SAMPLE clauses in a SELECT statement
Parallel query and parallel DML
Star transformations and star joins
Extensible optimizer
Query rewrite with materialized views
Enterprise Manager progress meter
Hash joins
Bitmap indexes and bitmap join indexes
Index skip scans

And, if any of these are used the CBO is enabled, even if optimizer_mode is set to RULE or a /*+ RULE */ hint is used.

David Fitzjarrell Received on Thu Nov 03 2005 - 13:09:23 CST