作者：Maria Colgan 曾担任Oracle优化器的产品经理
最近，我接到客户的一个电话，他刚刚升级到Oracle Database 11.2.0.2。因为认为基于规则的优化器（RBO）已被新版本移除而感到恐慌。他这样认为的原因是因为他们曾尝试添加RULE提示到SQL语句中，但他们仍然会得到一个基于成本的执行计划。
我立即向他们保证，RBO并未从11.2.0.2中移除，并请他们详细描述他们的场景。他们使用RULE提示添加到语句中，而且还有几个其它提示。如果语句包含RULE提示之外的其它提示，那么RULE模式会被忽略，因而会得到基于成本的执行计划。
尽管RBO已经在Oracle Database 10g时被废弃，但RULE模式和提示仍然有效。即使RULE模式/提示被指定了，但下面的对象和查询属性仍会强制使用CBO：
- SQL语句中有其它提示
- SQL语句中访问了一个或多个分区表
- SQL语句中访问了一个或多个IOT表（索引组织表）
- SQL语句中访问了一个或多个物化视图
- SELECT语句中指定了SAMPLE子句
- 指定了分页语句
- 使用了并行
- 使用了分组运算
- 使用了分组外联接（Group outer-join)
- 带有并行子句的create table语句
- 使用了ANSI标准的左外联接（即使用的是LEFT JOIN的写法，而不是（+）的写法）
- 使用了ANSI标准的全外联接
- 使用了闪回游标（AS OF）
你还应意识到，使用RULE提示会对优化器有若干限制。这会消除所有基于成本的查询转换（包括星型转换），并防止特定的访问路径和联接方法。比如：
- 函数索引
- 位图索引
- 反转键索引
- 索引跳扫
- 哈希联接
尽管在处理欠优的执行计划时需要使用一些提示，尝试使用特定的提示以及通过RULE提示回到RBO模式也是明智的。但理想情况下，在这些场景中应该发挥SQL plan Management(SPM)的优势，而不是直接在语句中添加提示。使用SPM你可以将提示后的执行计划与一个未加提示的语句关联起来。
原文链接：https://blogs.oracle.com/optimizer/post/why-was-the-rule-hint-ignored
原文内容：
Why was the RULE hint ignored?
Maria Colgan | December 20, 2011 | 2 minute read
Distinguished 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.
