X

Insights into Statistics, Query Optimization and the Oracle Optimizer

Why was the RULE hint ignored?

Maria Colgan
Master Product Manager
Recently I got a call from a customer that had just upgraded to Oracle Database 11.2.0.2 and was panicking because they thought the Rule Based Optimizer (RBO) had been removed. The reason they thought this was because when they tried to add the RULE hint to a SQL statement they still got a Cost Based Optimizer (CBO) plan. I quickly reassured them that the RBO had not been removed in 11.2.0.2 and asked them to describe their scenario. It turned out that the statement they added the RULE hint to, already had several hints specified in it. The RULE mode is ignored if the statement contains hints other than the RULE hint itself, hence their cost based plan.


Although the RBO was depreciated in Oracle Database 10g, the RULE mode and hint are still honored assuming the query can actually use the RBO. The following object / query properties  force the CBO to be used even when the RULE mode / hint is specified:

  • Other hints are specified in the SQL statement
  • One or more partitioned tables are accessed in the SQL statement
  • One or more IOTs are accessed in the SQL statement
  • One or more Materialized views are accessed in the SQL statement
  • A SAMPLE clauses is specified in a SELECT statement
  • A spreadsheet clause is specified
  • Parallel execution is used
  • Grouping sets are used
  • Group outer-join is used
  • A create table with a parallel clause
  • A left outer join (ANSI) is specified
  • A full outer join (ANSI) is specified
  • Flashback cursor (AS OF) is used

You should also be aware that if you use the RULE hint you severely limit the Optimizer options. It will eliminate all cost-based transformation (including star transformation), and prevents certain access paths and join methods from being considered. For example,

  • Function based indexes
  • Bitmap indexes
  • Reverse key indexes
  • Index skip scans
  • Hash Joins

Although hints are some time necessary to work around a suboptimal execution plan, it is far wiser to use a specific hint then to try and revert back to RBO via the RULE hint. Ideally you should take advantage of SQL Plan Management (SPM) in these cases, rather than adding the hint directly to the SQL Statement. Using SPM you can associate a hinted plan with a non-hinted SQL statement.

+Maria Colgan

Join the discussion

Comments ( 2 )
  • guest Tuesday, December 20, 2011

    "One or more Materialized views are accessed in the SQL statement"

    Does this also apply when selecting from the MV directly and not relying on query transformation?

    Thanks.


  • guest Monday, April 9, 2012

    Hi,

    Am really interested in the "Tips for Preventing Suboptimal Execution Plans" presentation. Cannot get it from oracleus.wingateweb.com anymore.

    Any idea from where I could download it?

    Thanks.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha