Re: Optimizer hint <*+ RULE *> question

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Fri, 04 Nov 2005 19:21:53 +0100
Message-ID: <dkg8ai$bpi$1@news2.zwoll1.ov.home.nl>

fitzjarrell_at_cox.net wrote:
> 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
>

Thanks for the list. I do agree with Jonathan, though; there are a lot of things that *require* CBO, but just a few that make "CBO kick in".

As the OP mentions neither versions nor options/editions, I thought I'd be on the safe side with the statistics. I have seen that behavior in 8.0 (or should I say "since 8.0").

Again - appreciate the list.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Received on Fri Nov 04 2005 - 12:21:53 CST