公众号：杨建荣的学习笔记 · 作者：r3笔记第37天 · 发布：2014-10-26 23:20:35 · 原文链接

在sql解析器中，在生成执行计划的时候，会在多个执行计划中选择最优的计划，在这个过程中，查询转换就是一个很重要的过程。
虽然最终的执行结果没有变化，但是从优化器的角度来看，查询转换的结果会更好。
大体来说查询转换有以下几种类型。
视图合并
子查询解嵌套
谓词推进
物化视图查询重写

比如
select *from emp where deptno in (select deptno from dept)
会在查询转换中转换为下面的样子.
select e.* from emp e,dept d where e.deptno=d.deptno;
从查询转换的角度来说，两者的查询效果是基本一致的。

–视图合并
select *
from emp e,
(select deptno from emp) s_view
where e.deptno=s_view.deptno;

70 rows selected.
Execution Plan
———————————————————-
Plan hash value: 3638257876
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 71 | 2911 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 71 | 2911 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 2 (0)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(“E”.”DEPTNO”=”DEPTNO”)

来比较一下是否视图合并的结果。
select
*
from emp e,
(select /*+no_merge*/ deptno from emp) s_view
where e.deptno=s_view.deptno

70 rows selected.
Execution Plan
———————————————————-
Plan hash value: 2243607326
—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | 71 | 3621 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 71 | 3621 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 2 (0)| 00:00:01 |
| 3 | VIEW | | 14 | 182 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 42 | 2 (0)| 00:00:01 |
—————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 – access(“E”.”DEPTNO”=”S_VIEW”.”DEPTNO”)

可以比较上面两个查询的时候，会发现第二个执行计划的VIEW关键字表示，执行计划是保持原样的，会单独处理视图。
使用圆括号把子查询括起来不一定在sql执行的时候会保证子查询块会单独执行。
这个时候可以根据要求来选择是否选择视图合并。

视图合并在大体来说性能还是不错的,不过在使用order by, 聚合函数，分析函数，group等等的时候，视图合并的功能还是会受到限制或者无法启用。
而且在试图合并的功能受到限制的时候，如果硬要启用视图合并功能，可能查询的结果会有一些变化，这个时候需要你来判断是否确定要启用视图合并。
select e.ename,e.sal,v.avg_salary
from emp e,
(select deptno,avg(sal) avg_salary
from emp oe
group by deptno)v
where e.deptno=v.deptno and e.sal>1000;

12 rows selected.
Execution Plan
———————————————————-
Plan hash value: 269884559
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 14 | 546 | 6 (34)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 546 | 6 (34)| 00:00:01 |
| 2 | VIEW | | 3 | 78 | 3 (34)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 3 (34)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 13 | 169 | 2 (0)| 00:00:01 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – access(“E”.”DEPTNO”=”V”.”DEPTNO”)
5 – filter(“E”.”SAL”>1000)

select /*+ merge(v)*/ e.ename,e.sal,v.avg_salary
from emp e,
(select deptno,avg(sal) avg_salary
from emp oe
group by deptno)v
where e.deptno=v.deptno and e.sal>1000;

12 rows selected.
Execution Plan
———————————————————-
Plan hash value: 4115741206
—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | 68 | 2176 | 6 (34)| 00:00:01 |
| 1 | HASH GROUP BY | | 68 | 2176 | 6 (34)| 00:00:01 |
|* 2 | HASH JOIN | | 68 | 2176 | 5 (20)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 13 | 325 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
—————————————————————————-

Predicate Information (identified by operation id):
—————————————————
2 – access(“E”.”DEPTNO”=”DEPTNO”)
3 – filter(“E”.”SAL”>1000)

–子查询解嵌套
子查询嵌套和视图合并的不同之处在于视图合并是在select xxx from的部分，而子查询嵌套式在where xxxx的部分。

select *from emp where deptno in (select deptno from dept)
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 |
————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“DEPTNO” IS NOT NULL)

先来比较一下是否启用子查询嵌套的区别 ,如果不需要启用子查询嵌套，可以使用Hint no_unnest来引导。
select *from emp where deptno in (select /*+ no_unnest*/deptno from dept)
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1783302997
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
——————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM “DEPT” “DEPT”
WHERE “DEPTNO”=:B1))
3 – access(“DEPTNO”=:B1)

可以从谓语部分看出来，两个的执行计划还是有很大的不同，第二个执行计划是未启用子查询解嵌套的形式。会在子查询中走索引，然后通过exists子句来和外部查询的deptno字段连接。
而第一个执行计划直接走了一个全表扫描，对于deptno的部分直接使用条件deptno is not null来过滤了。

select *from emp outer
where outer.sal>(select avg(inner.sal) from emp inner where inner.deptno=outer.deptno)
6 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1245077725
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 64 | 6 (34)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 64 | 6 (34)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 3 | 78 | 3 (34)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 3 (34)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 2 (0)| 00:00:01 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – access(“ITEM_1″=”OUTER”.”DEPTNO”)
filter(“OUTER”.”SAL”>”AVG(INNER.SAL)”)

这个例子中子查询转换为了一个内嵌式图，然后与外部的查询合并连接,相关列成为了连接条件，子查询的剩余部分成为了内嵌视图。

sql的执行方式和下面的sql类似。
select *from emp outer,
(select inner.deptno,avg(inner.sal) avg_sal from emp inner group by inner.deptno)inner where inner.deptno=outer.deptno
and outer.sal>inner.avg_sal;

6 rows selected.
Execution Plan
———————————————————-
Plan hash value: 269884559
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 64 | 6 (34)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 64 | 6 (34)| 00:00:01 |
| 2 | VIEW | | 3 | 78 | 3 (34)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 3 (34)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 2 (0)| 00:00:01 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – access(“INNER”.”DEPTNO”=”OUTER”.”DEPTNO”)
filter(“OUTER”.”SAL”>”INNER”.”AVG_SAL”)

关于谓词推进和物化视图的部分，稍后补充。