周末时，近日有网友朋友向我提问，“什么是视图合并？多次看到这个名词，但是每次都是扎入到执行计划中去分析了，但是对这个概念和功能完全不清楚，干啥用的？” 对于这个概念，趁赶着这个问题，自己也好好复习一下。

一. 什么是视图合并（View Merging）？

我们通过一个SQL案例来说明，现有如下SQL：

SQL1:
select emp1.empno, emp1.ename, emp1.comm
 from emp1,
 (select emp.ename, emp.deptno
 from emp, dept
 where emp.deptno = dept.deptno) a
 where emp1.ename = a.ename

执行计划如下：

我们看到，SQ中存在如下的子查询结果集，区别于标量子查询，该子查询是在from 与Where之间的，从执行计划上来看，emp表和dep表进行关联过滤限定后在再EMP1进行最后关联，也就是说如下查询结果集作为一个整体看待，这个比较符合SQL写法的认知逻辑，但是强大的Oracle CBO默认会按照如此的逻辑进行关联么？

select emp.ename, emp.deptno
 from emp, dept
 where emp.deptno = dept.deptno

可以看到，我在SQL中添加了一个 Hint:no_merge，说明此执行计划是人为控制后的产物，我们来看一下去除掉Hint，CBO默认选择什么执行计划？

我们看到，CBO默认进行的Table Join方案为，EMP与EMP1进行 HASH JOIN后，最后一步进行与DEPT的JOIN工作。这便是Oracle的视图合并。

视图合并是Oracle CBO针对SQL的自动优化措施进行了查询转换，Oracle会保证转换前后的SQL语义是等价的，不再将视图内部（子查询）单独处理与执行，进行视图（子查询）进行拆分，把视图定义或子查询中的表与外部查询的表进行合并（emp与emp1)，说了这么多，目的是什么，目的只有1个：让CBO有更多执行路径可以选择，更多路径成本比较，选择出一种最优的执行路径。

视图合并有诸多前提条件，但是宏观上讲，视图或子查询中不能出现UNION,MINUS等集合运算，start with connect by,rownum,cube,rollup等关键字，因为视图合并后，会影响结果集，无法确认合并后语义是否一致。

二. 视图合并的种类 （观点结论引用于著名的 《基于Oracle的SQL优化》- 崔华 著 一书）：

（1）简单视图合并：

简单视图合并，是指针对那些不含外连接，以及所带视图的视图定义SQL语句中不包含distinct,group by等聚合行数的目标SQL进行视图合并；进行简单视图合并的SQL，经过CBO的等价改写，一定是小于或等于未做简单视图合并的原SQL成本值，但是如果成本值相同的情况，CBO更倾向于选择进行视图合并的执行路径。

（2）外连接视图合并：

外连接视图合并，是指针对于使用了Outer Join，并且自带视图定义的SQL语句中不含DISTINCT，GROUP BY等聚合函数的目标SQL的视图合并，当目标SQL在和外部查询的表连接时(上例的emp1.ename = a.ename)变为视图与外表之间的连接，有2个前提条件

a.视图（子查询）作为外连接的驱动表；

or

b.视图如果作为被驱动表，但是视图定义SQL语句中只包含一个表；

并且

（3）复杂视图合并：

复杂视图合并，为所带视图定义的SQL语句中含有，外连接、简单视图合并中的禁忌条件：DISTINCT与GROUP BY 的情形。对于复杂视图合并的意义在于，可以将DISTINCT与GROUP BY在视图内部（子查询）中先过滤一部分数据，试图将嫌少中间结果集。但是合并结果并不能一定带来目标SQL的性能提升，CBO选择复杂视图合并的情况：会采取合并改写后的SQL的Cost < 未合并改写后的Cost前提下。

一.标量子查询

数据库经常能看到如下类形式的SQL：

select t.OWNER,t.OBJECT_NAME,(select TABLESPACE_NAME from t1 where 
t.object_name=t1.segment_name) travelskydba from t;

我们看到，该SQL中的查询列是由一个单独的SQL查询组成，查询出T表与T1表关联Join后的T1表中的数据。可以看到，T表与T1表是存在关联关系的，t.object_name=t1.segment_name，此类SQL我们成为标量子查询。

我们从执行计划中看到，T1表扫描了5012次，T表扫描了1次，是否有点类似于嵌套循环（Nested Loop）的感觉？标量子查询类似于嵌套循环，本例子中，T表为主表，T1为子表，主表通过t.object_name=t1.segment_name传值给子表进行Filter过滤，不同于嵌套循的是，标量子查询的驱动表固定为主表，并不可更改，需要T表传值与T1表。

（1）标量子查询在SQL优化的过程中需要注意，子表（被驱动表）扫描次数的问题，也就是执行计划中的starts列，主表放回多少数据，就要传值与子表，如果数据量很大，子表扫描次数会非常庞大，造成SQL的性能问题。同嵌套循环一致，被驱动表连接列上需要创建选择性好的索引。

（2）在Oracle 12c中，标量子查询会等价改写为外连接，从而可以改变执行计划进行HASH_JOIN

为什么是外连接而不是内连接呢？我们往EMP表中插入一行数据：

如果存在主表EMP.DEPTNO的情况，改写为内连接会丢失ALBERT此行数据，所以标量子查询在不确定表业务特性时，一定要改写为外连接。

二.半连接：

当SQL出现in ,exists时，我们称为半连接，例如：

select * from emp where DEPTNO in (select DEPTNO from dept)
select * from emp where exists (select 1 from dept where emp.DEPTNO =dept.DEPTNO);

我们看到，EMP为驱动表，DEPT为被驱动表，我们可以改变内连接的执行计划：

此执行计划为EMP为驱动表，DEPT为被启动表进行嵌套循环。

我们通过添加HINT控制执行计划后，将DEPT表作为驱动该表，此方法可以帮助我们在办连接中，控制其执行计划。

半连接通常可以等价改写成内连接的方式，例如：

select *  from dept where deptno in (select deptno from emp)可以等价改写成

select dept.* from dept,(select deptno from emp group by deptno)  where dept.deptno=emp.depto（因为返回的是DEPT表的数据，所以要将EMP进行去重操作，避免重复数据，改变结果集）此改写方式因多了去重的操作，所以执行效率并不如返连接高。

三.反连接：

AB表进行关联，只返回A表其中没有成功关联上B表的数据，我们称为反连接，not in ,not exists关键字，我们看如下例子：

我们观察结果是NULL值，1400和0并没有如愿得出，是因为not in对空值敏感，如果该列包含空值，结果集会全部显示为空，因为NULL不等于任何值。剔除空值，则便显示正确结果：

exists对空值不敏感，空值皆可返回。

反连接执行计划可以改写成外连接+子表连接列 is null的情况，也同样可以利用qb_name hint改变执行计划，但是改写成外连接后，启动表被固定为主表，无法更换驱动表（主表存在NULL无法传值的情况）。

一. 什么是Sort Merge Join(排序连接合并)?

表连接方式中，排序连接合并个人理解为多数处理非等值连接的表连接情况，例如<,> >=,<=,执行效率上看与HASH JOIN比有所降低（因有排序过程），但是如果两个表的连接列上均有索引，则有些场景下则可以避免排序过程（索引有序）。工作过程原理如下，A,B两表进行排序连接合并：

（1）根据谓词条件访问A表，对A表的结果集按照连接列进行排序，形成R1

（2）根据谓词条件访问B表，对B表的结果集按照连接列进行排序，形成R2

（3）Oracle官方认为Sort Merge Join是不存在驱动表与被启动表的概念的，但是最终的R1,R2进行合并工作时翻阅了诸多资料后，个人感觉是存在的，R1中取出第一条记录去R2中进行匹配，第二条记录..第三条记录..直到匹配完成R1所有记录。也就是驱动表对应结果集R1有多少条记录，R2则需要被匹配多少次，但是被驱动表只需要被扫描1次（PGA存入排序后的结果集就好，无需再次访问被驱动表）。该过程是在PGA中需进行匹配的，之前上文中提高，排序连接合并多数情况下执行效率是差与HASH JOIN的，因为HASH JOIN只需要把驱动表放入PGA中，但是排序连接合并需要将两表结果集均放入PGA中。

实验环境中，模拟了一个SMJ的表连接SQL，可以看到，T1为“驱动表”，T为被“驱动表”，之所以加上引号，是因为Oracle官方认为SMJ连接方式是不存在驱动表与被驱动表的概念的，可以看到,T1首先进行排序操作，T表进行排序操作，ID=4的执行计划中，T1结果集去T结果集中进行数据匹配过滤。

优化方案总结：

（1）两表关联如果是等值连接，则尽量不需要走Sort Merge Join，数据关联结果集大走HASH JOIN,数据返回量少走Nested Loop

（2）如过两表关联时非等值连接，可以在连接列上建立索引，避免昂贵Cost的排序的工作。如果结果集很大，则可以尝试先过滤将结果集变小，再进行Sort Merge Join。