Re: Risk of RULE mode in 10g+

> 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 10.2.0.4 version. I'm concerned
> about the integrity of the result sets.

Be aware that the RULE hint would work *only* the SQL met certain conditions. For example, if the table is partitioned, Oracle rejects the RULE model and uses Cost. (This occurs even in 8i, so this is noting new). The full list is here:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#74098

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

As for wrong results, there are multiple issues with wrong results even in the CBO depending on your version.

John Kanagaraj <><
http://www.linkedin.com/in/johnkanagaraj http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!) ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

--
http://www.freelists.org/webpage/oracle-l