Optimizer hint <*+ RULE *> question

Giganews Newsgroups
Subject: Optimizer hint <*+ RULE *> question
Posted by:  magjez (magj…@yahoo.com)
Date: 3 Nov 2005


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