Re: Risk of RULE mode in 10g+

From: John Kanagaraj <>
Date: Mon, 15 Mar 2010 13:34:00 -0700
Message-ID: <>

Hi Jeffrey,
> 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.

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:

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 <>< (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 **

Received on Mon Mar 15 2010 - 15:34:00 CDT

Original text of this message