Why was the RULE hint ignored?
By Maria Colgan on Dec 19, 2011
Recently I got a call from a customer that had just upgraded to Oracle Database 18.104.22.168 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 22.214.171.124 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.