海量数据库解决方案之视图合并应用原理

为了让视图或嵌套视图按照最优化的方式执行，可能需要对其进行必要的转换，下面我们就针对具体的转换方法予以详细说明。虽然这里的内容有些费解，但是它在帮助我们深刻理解执行计划方面有着非常大的作用。在这里我们将把视图查询分为两类：视图查询和读取查询。

所谓的视图查询是指我们创建视图所使用的SELECT语句，即数据字典中所存储的SELECT语句。而读取查询是指执行该视图的SQL语句。即使该视图是嵌套视图（也就是在FROM中使用括号括起来的SELECT语句），使用方法也相同，位于嵌套视图之外的SELECT语句被称为读取查询。

为了实现最优化视图读取的目标，需要对查询语句进行转换，以确保能够从视图的原始表中读取数据。能够实现该目的的方法有如下两种。

þ  视图合并（View Merging）法：将视图查询与读取查询合并。

þ  查询条件推入（Predicate Pushing）法：在无法执行视图合并的情况下，将读取查询中的查询条件推入到视图查询中去。

为了把视图查询合并到读取查询中，优化器将读取查询中所使用视图的名字替换为原始表名字，并将视图查询WHERE条件中的查询条件添加到读取查询的WHERE条件中去，具体如下所示。

CREATE VIEW emp_10 (e_no, e_name, job, manager, hire_date, salary, -- 视图查询语句

commission, deptno) AS

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno

FROM emp

WHERE deptno = 10;

读取视图数据的查询语句如下所示。

SELECT e_no, e_name, salary, hire_date –- 读取查询

FROM emp_10 – 视图

WHERE salary > 1000000;

现在让我们按照上面所介绍的方法将视图查询与读取查询合并，需要强调的是在该合并中是以读取查询为基准，即把视图查询中的对应查询条件合并到读取查询中去。首先将读取查询中视图的名字替换为原始表的名字EMP，然后把视图中所使用的查询条件deptno=10合并到读取查询的WHERE条件中去，最后将视图中的列也换为原始表中相对应的列。合并完成后的查询语句如下所示。

SELECT empno, ename, sal, hiredate – 合并后的查询语句

FROM emp

WHERE deptno = 10 AND sal > 1000000;

如果EMP表中存在DEPTNO+SAL的组合索引，转换之后的查询将可以直接使用该索引，因此会具有比较好的执行计划。像这样，如果视图查询可以被合并到读取查询中，那么就可以将共同使用的部分创建为视图，这样不仅可以缩短读取查询的长度，而且还可以简化读取查询的使用。

需要注意的是这样的合并操作并不是经常都能够被执行，在如下情况下就无法实现这一合并操作。

þ  集合运算（UNION，UNION ALL，INTERSECT，MINUS）

þ  CONNECT BY

þ  使用ROWNUM的情况

þ  SELECT-List中的统计函数（AVG，COUNT，MAX，MIN，SUM）

þ  GROUP BY（只有在使用了Merge 提示或者相关参数为Enable的情况下才可以合并）

þ  SELECT-List 的DISTINCT（只有在使用了Merge提示或者相关参数为Enable的情况下才可以合并）

如果嵌套视图或视图中包括了以上所列举的内容，那么优化器就将读取查询中的查询条件添加到视图中，现在让我们通过例子来介绍具体的转换方法。

CREATE VIEW emp_union_view (e_no, e_name, job, mgr, hiredate, sal, comm, deptno)

AS SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM regular_emp

UNION ALL

SELECT empno, ename, job, manager, hiredate, salary, comm, 90 FROM temporary_emp;

现在我们思考一下如下使用该视图的读取查询，在该转换中是以视图查询为基准，让读取查询的查询条件进入到视图查询的查询条件中去。

SELECT e_no, e_name, mgr, sal

FROM emp_union_view

WHERE deptno = 20;

在执行该读取查询时会做如下转换。

SELECT empno, ename, mgr, sal

FROM ( SELECT empno, ename, mgr, sal FROM regular_emp WHERE deptno = 20

UNION ALL

SELECT empno, ename, manager, salary FROM temporary_emp WHERE 90 = 20);

通过观察转换后的查询语句能够发现，读取查询中的查询条件都已被添加到各个SELECT查询语句中去了，并且各个列也与原始表中的列进行了相对应的转换。在这里需要进一步说明的是由于临时员工表没有部门编号列，所以为他们所指定的部门编号为90，因此就出现了在第二个SELECT查询语句中所出现的常量与常量进行比较的现象。在查询语句被执行时，最先检查的是具有常量值的查询条件，所以在上面的例子中临时员工的数据不会被读取，因为上述常量查询条件显然不成立。

接下来要介绍的是在实际中最常用的类型，即包含了GROUP BY的视图。

CREATE VIEW emp_group_by_deptno

AS SELECT deptno, AVG(sal) avg_sal, min(sal) min_sal, MAX(sal) max_sal

FROM emp

GROUP BY deptno;

在上面的视图中只使用了GROUP BY而没有任何其他查询条件，以表中的全部数据为对象进行了GROUP BY。在这里需要注意的是假设已将相关参数设置为Enabled。此时在实现视图转换中，读取查询中的查询条件会被添加到视图查询中去。如下所示，在读取查询被执行时会做如下转换。

SELECT *

FROM emp_group_by_deptno

WHERE deptno = 10;

转换后的查询语句：

SELECT deptno, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal

FROM emp

WHERE deptno = 10

GROUP BY deptno;

希望各位读者能够以上面所介绍的内容为基础，来进一步学习视图合并中的一些其他重要内容。如果视图转换的相关参数（_complex_view_merging，optimizer_secure_view_merging）被设置为Enabled，则即使在视图中使用了 GROUP BY或者DISTINCT，读取查询中的查询条件也可以被添加到视图查询中去。

如果我们仔细思考一下，就会发现在第二个例子中有一个非常重要的问题，即在创建视图时无法将经常发生变化的查询条件放入到视图中。因此所创建的视图基本上都不可能拥有查询条件，这就意味着视图中所要查询的数据是原始表的全部数据。例如，原始表中有1亿行数据，在没有任何查询条件的前提下，为了获得“GROUP BY日期”的结果，就必须对上述拥有1亿行数据的大表执行全表扫描。

如果在读取查询中以特定的日期为查询条件，在没有设定参数或使用提示的前提下，该查询条件无法被合并到视图查询中去，所以只能在读取表中全部数据之后先按照日期对其进行分组，然后从该分组的分组结果中查询满足该特定日期的数据，这是比较烦琐的过程。相反，如果将该查询条件合并到视图查询中，则所需要读取的只是满足该特定日期的数据，相信各位读者应该已经很清楚地看到了两者之间的差异。

现在让我们比较一下合并的执行计划和不能合并的执行计划之间的区别，这里所使用的视图在前面已经进行了说明。

SELECT d.loc, v.avg_sal

FROM dept d, emp_group_by_deptno v

WHERE d.deptno = v.deptno AND d.loc = 'London';

下面所示的是正常合并之后的查询语句和优化器为该查询语句所制定的执行计划。

SELECT dept.loc, AVG(sal)               

FROM dept, emp

WHERE dept.deptno = emp. deptno

AND dept.loc = 'London'

GROUP BY dept.rowid, dept. loc;

执行计划：

 

SORT (GROUP BY) NESTED LOOPS TABLE ACCESS (FULL) OF 'EMP' TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' INDEX (RANGE SCAN) OF 'DEPT'(UNIQUE)

 

仔细观察上面的结果，让人觉得奇怪的是，原来视图查询中的“GROUP BY DEPTNO”怎么变成了DEPT.ROWID了呢？在执行计划中对应操作就是在执行表连接之后执行SORT(GROUP BY)操作。发生这种变化的主要原因就是优化器对用户所编写的SQL语句进行了重新解释，并将其转换成了更加有效的SQL语句。在这种情况下，由于是在所有可能的查询条件都被使用之后，又对SQL语句进行了二次加工，所以这里的SQL语句是最优的。

如果把没有进行视图合并的执行计划与这里的合并之后的执行计划放在一起进行比较，那么很容易就能够发现两者之间的显著差异。下面列举的是在读取查询中通过使用提示来特意地制止执行合并的例子。在没有执行合并时，执行步骤为首先从视图中读取数据，然后再将所读取的结果与表DEPT进行连接。需要注意的是我们并不能说按照这样的方式处理就始终都不能获得较好效果。

这种情况仅限于使用读取查询中的查询条件无法缩减视图的查询范围时，希望读者能够将这一点铭记于心，并在使用时多加注意。我们分析执行计划时，如果发现没有按照要求执行视图合并，则可以通过使用“MERGE”提示对其进行引导。

SELECT /*+ NO_MERGE(v) */

d.loc, v.avg_sal

FROM dept d, emp_group_by_ deptno v

WHERE d.deptno = v.deptno

AND d.loc = 'London';

执行计划：

 

HASH JOIN TABLE ACCESS (FULL) OF 'DEPT' VIEW OF 'EMP_GROUP_BY_DEPTNO' SORT (GROUP BY) TABLE ACCESS (FULL) OF 'EMP'

在由IN比较运算符所构成的子查询中也可以实现视图合并，在此情况下如果子查询起的是向主查询提供其执行结果的角色则非常有效。通过优先对子查询的执行来将其执行结果提供给主查询，从而使得主查询的查询范围在很大程度上得到缩减，在本书中将起这种作用的子查询称为提供者角色。相反，如果子查询起的是验证主查询结果的作用，则需要付出的代价就比较大了，在本书中将起这种作用的子查询称为检验者角色。事实上，这样的视图合并对处理海量数据有着非常大的影响。

SELECT emp.ename, emp.sal

FROM emp, dept

WHERE (emp.deptno, emp.sal) IN (SELECT deptno, avg_sal

FROM   emp_group_by_deptno)

AND emp.deptno = dept.deptno AND dept.loc = 'London';

通过对上面查询语句的观察会发现子查询中没有任何查询条件，而只是将GROUP BY所获得的结果提供给主查询后执行表连接。由于这种执行步骤所需要的代价比较大，所以有必要对其执行视图合并，合并结果如下所示。

SELECT e1.ename, e1.sal

FROM emp e1, dept d, emp e2

WHERE e1.deptno = d.deptno

AND d.loc = 'London'

AND e1.deptno = e2.deptno

GROUP BY e1.rowid, d.rowid,

e1.ename, e1.sal

HAVING e1.sal = AVG(e2.sal);

 

执行计划：

 

FILTER SORT (GROUP BY) TABLE ACCESS (BY INDEX ROWID) OF 'EMP' NESTED LOOPS NESTED LOOPS TABLE ACCESS (FULL) OF 'DEPT' TABLE ACCESS (BY INDEX ROWID) OF 'EMP' INDEX (RANGE SCAN) OF 'EMP_X1' INDEX (RANGE SCAN) OF 'EMP_X1'

上面的查询语句比较费解，可以算是高级SQL语句了。通过右边的执行计划可以看出SORT(GROUP BY)是在处理完所有的查询条件之后被执行的，以及将AVG_SAL放在HAVING中进行处理，所对应的执行计划为“FILTER”。

在实际工作中使用嵌套视图的情况要比使用视图的情况多。下面是对一个在使用嵌套视图的情况下是否有必要执行视图合并进行判断的例子。

SELECT /*+ NO_MERGE (v) */ e1.last_name, e1.sal, v.avg_salary

FROM emp e1, (SELECT deptno, avg(sal) avg_salary

FROM emp e2

GROUP BY deptno) v

WHERE e1.deptno = v.deptno AND e1.sal > v.avg_salary;

这是一个查询工资大于部门平均工资的员工信息的查询语句。为了查询满足条件的信息而需要优先求得各个部门的平均工资，然后再查询大于该平均工资的员工信息，所以这是一个非常正常的查询语句。但是对于我们来说，所需要的是利用查询语句查找到满足查询条件的结果集合而不是定义执行过程。所以从优化器制定执行计划的立场来看，并没有必要一定要先求得各个部门的平均工资。

例如，EMP和DEPT表可以直接通过DEPTNO连接获得笛卡儿积，然后再执行GROUP BY，这样一次性就可以完成所有的处理。在过去以这样的方式执行该查询时需要用户直接改变查询语句，但现在优化器可以自行决定对其进行改变。下面是合并后查询语句的执行计划。

FILTER

SORT (GROUP BY)

HASH JOIN

TABLE ACCESS (FULL) OF 'EMP'

TABLE ACCESS (FULL) OF 'EMP'

分析一下该执行计划，相同的表通过连接获得笛卡儿积，在执行SORT (GROUP BY)的同时，一边计算各个部门的平均工资，一边过滤（FILTER）大于平均工资的员工信息。接下来再让我们观察一下使用 “NO_MERGE”提示的执行计划。

HASH JOIN

TABLE ACCESS (FULL) OF 'EMP'

VIEW

SORT (GROUP BY)

TABLE ACCESS (FULL) OF 'EMP'

通过观察可以发现，在嵌套视图被优先执行之后，对其执行结果进行了SORT(GROUP BY)，最后使用前一执行中所获得的结果与表EMP进行连接。从某种角度上看，上面的例子反倒显得比较有效。由此可见，对于相同的查询语句而言，执行视图合并与不执行视图合并的最大差异就在于优化器为其所制定的执行计划上。那么我们究竟应当基于什么样的标准来判断是否需要执行视图合并呢？

结论就是，如果在读取查询语句中存在大量可以缩减查询范围的查询条件，且将这些查询条件添加到视图中可以缩减整体的数据处理量，那么就有必要执行视图合并，反之就没有必要执行视图合并。即使让我们从常识的角度来思考一下也会发现，如果在读取查询中存在一些可以缩减查询范围的查询条件，且将这些查询条件添加到嵌套视图中可以缩减视图的查询范围，那么优先考虑这些查询条件是再自然不过的事情了。

在实际应用中，由于所使用的查询语句以特定集合为对象进行查询的情况要比以整体集合为对象进行查询的情况多，所以将查询条件添加到嵌套视图的做法通常都可以提高执行速度。

以前为了将查询条件添加到视图中，需要编写非常费解的查询语句，而现在只需要通过视图合并就可以很容易地实现查询语句的转换目的。希望各位读者能够以这里所介绍的判断准则为依据，在实际工作中，通过对视图合并和非视图合并的执行计划的比较来进一步提高自己的判断能力。

如果读者能够以集合的观念来理解 SQL 语句，那么对嵌套视图的认识就会发生质的变化。由于对嵌套视图的理解存在差异，所以在对如何将读取查询中的查询条件添加到嵌套视图中去的认识上也会存在着很大的差异。是否能够很好地理解这个概念将对高级 SQL 的灵活运用有着非常重要的影响。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25708791/viewspace-697849/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25708791/viewspace-697849/