sql优化第一天，认识优化器和RBO中的等价改写SQL例子1

oracle有两种优化器，一种是基于成本的优化器CBO,一种是基于规则的优化器RBO,早期的版本使用基于规则的RBO优化器。从oracle10 g开始起，CBO已经成为比较成熟的优化器，但是要对sql优化有个系统的了解，我们还是需要从RBO开始学起。

基于规则的优化器（RBO）通过硬编码在Oracle数据库代码中的一系列固定的规则，来 决定目标SQL的执行计划。具体来说就是这样：Oracle会在代码里事先给各种类型的执行路径定一个等级， 一共有15个等级，从等级1到等级15。并且Oracle会认为等级值低的执行路径的执行效率会比等级值高的执 行效率要高，也就是说在RBO的眼里，等级1所对应的执行路径的执行效率最高，等级15所对应的执行路径 的执行效率最低。在决定目标SQL的执行计划时，如果可能的执行路径不止一条，则RBO就会从该SQL诸 多可能的执行路径中选择一条等级值最低的执行路径来作为其执行计划。

和CBO相比，RBO是有其明显缺陷的。在使用RBO的情况下，执行计划一旦出了问题，很难对其做调 整；另外，如果使用了 RBO,则目标SQL的写法，甚至是目标SQL中所涉及的各个对象在该SQL文本中出 现的先后顺序，都可能会影响RBO对于该SQL执行计划的选择。更糟糕的是，Oracle数据库中很多很好的特 性、功能均不能在RBO下使用，因为它们均不被RBO所支持。

只要出现了如下的情形之一（包括但不限于这些情形），那么即便你修改了优化器模式或者使用了 RULE Hint, Oracle依然不会使用RBO （而是强制使用CBO）：

•目标 SQL 中涉及的对象有 IOT （Index Organized Table）.

•目标SQL中涉及的对象有分区表。

•使用了并行查询或者并行DML。

•使用了星型连接。

•使用了哈希连接。

•使用了索引快速全扫描。

・使用了函数索引。

RBO下如何对执行计划进行有限的调整

1.等价改写SQL

如在目标SQL的where条件中对NUMBER或DATE类型的列加上0 （如果是VARCHAR2或CHAR 类型，可以加上一个空字符，例如II"）,这样就可以让原本可以走的索引现在走不了。对于包含多表连接的目 标SQL而言，这种改变甚至可以影响表连接的顺序，进而就可以实现在使用RBO的情况下对该目标SQL的 执行计划做调整的目的。

RBO会在诸多可能的执行计划中选择一条等级值最低的作为执行计划。如果出现两条或者两条以上等级值相同的执行路径的情况，RBO会选择数据字典缓存中的缓存顺序和目标SQL文本中出现的先后顺序来判断。

例子：

例子1
create emp_temp as  select * from emp;
在emp的mgr和dept_no两个列上分别建上名为IDX_MGR_EMPTEMP和IDX_DEPTNO_EMPTEMP的索引
观察
create index idx_mgr_emptemp on emp_temp(mgr);
create index idx_deptno_emptemp on emp_temp(deptno);

SQL1
select * from emp_temp
where mgr>100 and deptno>100

对于范例SQL 1而言，其where条件中出现了列MGR和DEPTNO,而在列MGR和DEPTNO上分别存 在着索引 IDX_MGR_TEMP 和 IDX_DEPTNO_TEMP。

现在的问题是，如果在启用RBO的情形下执行范例SQL 1,则Oracle会选择走上述两个索引中的哪一个？ 我们来实际验证一下。

在当前Session中将优化器模式修改为RULE,表示在当前Session中启用RBO；
SCOTT@erpdb> alter session set optimizer_mode='RULE';

Session altered.

然后执行范例SQL 1：

SCOTT@erpdb> SET AUTOTRACE TRACEONLY EXPLAIN; 
SCOTT@erpdb> select * from emp_temp where mgr>100 and deptno>100;

Execution Plan
----------------------------------------------------------
Plan hash value: 3753604472

----------------------------------------------------------
| Id  | Operation                   | Name               |
----------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP           |
|*  2 |   INDEX RANGE SCAN          | IDX_DEPTNO_EMPTEMP |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MGR">100)
   2 - access("DEPTNO">100)

Note
-----
   - rule based optimizer used (consider using cbo)

注意到 Id = 2 的执行步骤为 “INDEX RANGE SCAN | IDX_DEPTNO_TEMP”，Note 部分有关键字 “rule based optimizer used (consider using cbo)",这说明Oracle在执行上述范例SQL 1时使用的是RBO,且选择的是 走对索引IDX_DEPTNO_TEMP的索引范围扫描。

范例SQL 1的where条件中有“mgr>100”，所以RBO实际上是可以选择走列MGR上的索引 IDX_MGR_TEMP的，只不过RBO这里并没有选择走该索引，而是选择走列DEPTNO上的索引 IDX_DEPTNO_TEMP。

假如我们发现走索引IDX_DEPTNO_TEMP不如走索引IDX_MGR_TEMP的执行效率高，或者说我们就 想让RBO选择走索引IDX MGR TEMP,那么应该如何做呢？

方法1：

select * from emp_temp

where mgr>100 and deptno+0>100;

select * from emp_temp where mgr>100 and deptno+0>100;

Execution Plan
----------------------------------------------------------
Plan hash value: 491574169

-------------------------------------------------------
| Id  | Operation                   | Name            |
-------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP        |
|*  2 |   INDEX RANGE SCAN          | IDX_MGR_EMPTEMP |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"+0>100)
   2 - access("MGR">100)

Note
-----
   - rule based optimizer used (consider using cbo)

注意，此时Id = 2的执行步骤已经从之前的“INDEX RANGE SCAN | IDX_DEPTNO_TEMP”变为了现 在的“INDEX RANGE SCAN | IDX_MGR_TEMP”，这说明我们确实迫使RBO改变了执行计划，即我们的调 整巳经生效了。

方法2：

先 Drop 掉索引 IDX_MGR_TEMP：

SQL> drop index idx_mgr_temp;

再重新创建上述索引IDX_MGR_TEMP：

SQL> create index idx_mgr_temp on emp_temp(mgr);

Index created

然后再次执行范例SQL1：

select * from emp_temp where mgr>100 and deptno+0>100;

Execution Plan
----------------------------------------------------------
Plan hash value: 491574169

-------------------------------------------------------
| Id  | Operation                   | Name            |
-------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEMP        |
|*  2 |   INDEX RANGE SCAN          | IDX_MGR_EMPTEMP |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"+0>100)
   2 - access("MGR">100)

Note
-----
   - rule based optimizer used (consider using cbo)
注意，Id = 2的执行步骤已经从之前的"INDEX RANGE SCAN | IDX_DEPTNO_TEMP”变为了现在的 "INDEX RANGE SCAN | IDX_MGR_TEMP”，说明我们确实迫使RBO改变了执行计划，这也说明当目标SQL 有两条或者两条以上的执行路径的等级值相同时，我们确实可以通过调整相关对象在数据字典缓存中的缓存顺 序来影响RBO对于其执行计划的选择