RE: Risk of RULE mode in 10g+

  • From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
  • To: <Richard.Goulet@xxxxxxxxxxx>, <zollarja@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 15 Mar 2010 16:11:29 -0500

Sorry Dick but that is not true.  There are some queries that can only
run with CBO, like selecting from an IOT for example, but if the query
can run in RULE is still will even in 11. 


I'm not aware of rule retuning wrong results but anything is possible I
suppose. Jeff if you find that info I'd sure like to have it.


A key problem with RULE is that Oracle is not keeping it updated.  There
will be more and more queries over time where RULE will not be used.



Ric Van Dyke

Hotsos Enterprises



Hotsos Symposium 

Be there.




From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Goulet, Richard
Sent: Monday, March 15, 2010 2:30 PM
To: zollarja@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Risk of RULE mode in 10g+




    OH Dear, I'll try to find the url that says this for you, but in 10g
the rule based optimizer isn't deprecated, it's dead period.  You can
put rule in there all you want, but it will run in cost mode.  Isn't
suppose to cause problems with the result set that I've heard of, nor
does it cause a problem with executing the sql.


Dick Goulet 
Senior Oracle DBA/NA Team Lead 
PAREXEL International 




From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jeffrey Zollars
Sent: Monday, March 15, 2010 3:22 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Risk of RULE mode in 10g+

We are upgrading an application to the current version and also
upgrading the DB version from 9i to 10g. The application code has
several bits of code that are explicitly setting RULE mode. One in a
configuration table that can be updated. Second, in several hints coded
in SQL statements which cannot be easily changed. 

I have provided Oracle documentation to the application vendor showing
that RULE mode is desupported in 10g+ versions. They were skeptical to
say the least. 

I had thought that I'd seen stronger warnings that RULE mode could
actually generate incorrect result sets when running queries. I have
searched but could not find those warnings now. I am trying to make a
case to request the application vendor to go back and modify the code
before we update to the current version of the application on Oracle version. I'm concerned about the integrity of the result sets. 

I am wondering if anyone knows of or can provide a link to the warning
that result sets may be incorrect in 10g+ using RULE mode.  


J. A. Zollars

Other related posts: