我们用两篇视图合并的讨论，来继续我们查询转换的系列。在这些博文中，我们会回顾有关视图合并的术语，解释不同类型的视图合并，并讨论视图未能合并的原因。博文中的样例使用了Oracle的样例schemas.
我们使用术语视图来描述出现在FROM子句中的子查询块。Oracle可以合并若干类的视图：
- 简单视图合并，针对简单的select-project-join 视图.
- 外连接视图合并，针对发生外连接的视图
- 复杂视图合并，针对含有distinct和group by 的视图.
在今天的这篇博文中，我们将讨论前两个。复杂视图合并我们将在下一篇中讨论。
简单视图合并
考虑一个带有视图的简单查询 ：
select e.first_name, e.last_name, dept_locs_v.street_address, dept_locs_v.postal_code
from employees e,
(select d.department_id, d.department_name, l.street_address, l.postal_code
from departments d, locations l
where d.location_id = l.location_id) dept_locs_v
where dept_locs_v.department_id = e.department_id
and e.last_name = 'Smith';
该查询连接employees表和一个返回每个部门所在大街地址的视图。视图本身是两个表的连接。查询可以先连接departments表和locations表，产生视图的结果集，然后再用该结果集与employees表连接的方式来执行该查询。因为查询含有视图（dept_locs_v)，优化器可以提供如下受限的连接次序：
E, V
V, E
在视图内部，可以提供两种连接次序：
D, L
L, D
因此，组合起来，对于该形式的查询只有四种可能的连接次序。连接的方法也是受限的；由于视图的列上没有索引，所以，对于连接次序[E, V]，基于索引的嵌套循环连接是行不通的。没有视图合并的情况下，优化会选择如下的执行计划：
-----------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 (15)|
|* 1 | HASH JOIN | | 7 (15)|
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 (0)|
|* 3 | INDEX RANGE SCAN | EMP_NAME_IX | 1 (0)|
| 4 | VIEW | | 5 (20)|
|* 5 | HASH JOIN | | 5 (20)|
| 6 | TABLE ACCESS FULL | LOCATIONS | 2 (0)|
| 7 | TABLE ACCESS FULL | DEPARTMENTS | 2 (0)|
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - access("E"."LAST_NAME"='Smith')
5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
视图合并会合并视图中的表到外层查询块，取消视图的查询块。视图合并后，查询看起来是这样的：
select e.first_name, e.last_name, l.street_address, l.postal_code
from employees e, departments d, locations l
where d.location_id = l.location_id
and d.department_id = e.department_id
and e.last_name = 'Smith';
现在，所有的三个表出现在一个查询块中，优化器在考虑何种连接次序（共6种）时不再受限，而且，employees表和departments表连接时，可以基于索引进行。如下的执行计划是使用了视图合并后的：
-------------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 (0)|
| 1 | NESTED LOOPS | | |
| 2 | NESTED LOOPS | | 4 (0)|
| 3 | NESTED LOOPS | | 3 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 (0)|
|* 5 | INDEX RANGE SCAN | EMP_NAME_IX | 1 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 (0)|
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 0 (0)|
|* 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 0 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID | LOCATIONS | 1 (0)|
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."LAST_NAME"='Smith')
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
Oracle使用术语“简单”来特指select-project-join视图。上例中，为了选择更好的执行计划，使用了简单视图合并。这类视图会自动合并，只要它是被允许这样做的，因为一般情况下，视图合并后的执行计划至少会和未进行视图合并的一样好。视图合并后所增加的可使用的连接次序和访问方法，常常会产生更好的执行计划。视图合并还允许其它转换的发生，例如，视图合并发生后，视图内的表与视图外的表位于同一个查询块，从而允许进行连接消除的转换。
为什么一个select-project-join的视图不能被合并，有多种原因，典型的原因是这样做在语义上不是有效的。一个视图对于简单视图合并不是有效的部分原因，列在了下面：
- 视图包含了除select, project, join之外的结构, 包括:
Group by
Distinct
Outer-join（外连接）
Spreadsheet clause（分页子句）
Connect by（层次查询）
Set operators（集合操作符）
Aggregation（聚合操作） - 视图位于一个半连接或反连接的右侧。
- 视图在SELECT部分包含有子查询。
- 外层查询包含有PL/SQL函数。
请注意，这里的部分结构并不是在所有查询中都不允许进行视图合并，而是取决于其它约束的有效性。
外连接视图合并
如果一个视图涉及与来自于外层查询块中的表进行外连接，或者视图中包含外连接的表，那么其对于视图合并是否是有效的，取决于一些额外的限制。视图合并后，必须是可以使用Oracle的外连接语法来表示的。这暗含了对位于外连接左侧的视图的一个重要限制：来自外部查询块中的每一张表，最多只可以和视图底层中的一个表做外连接。例如，在这个查询中，目前就是不可能做视图合并的：
select e1.first_name||' '||e1.last_name emp_name, dept_managers_v.manager_name,
dept_managers_v.department_name
from employees e1,
(select e2.manager_id, e2.first_name||' '||e2.last_name as manager_name,
d.department_id, d.department_name
from departments d, employees e2
where d.manager_id = e2.employee_id) dept_managers_v
where dept_managers_v.department_id = e1.department_id(+)
and dept_managers_v.manager_id = e1.manager_id(+);
如果视图并合并，这将导致表e1要被外连接到两张表上，而这对于Oracle的外连接是不合法的。但是，在下面查询中的视图就可以被合并。
select e1.first_name||' '||e1.last_name emp_name, dept_managers_v.manager_name,
dept_managers_v.department_name
from employees e1,
(select e2.manager_id, e2.first_name||' '||e2.last_name as manager_name,
d.department_id, d.department_name
from departments d, employees e2
where d.manager_id = e2.employee_id) dept_managers_v
where dept_managers_v.department_id = e1.department_id(+);
合并后的查询看起来是这样的：
select e1.first_name||' '||e1.last_name emp_name,
e2.first_name||' '||e2.last_name as manager_name,
d.department_name
from employees e1, employees e2, departments d
where d.manager_id = e2.employee_id
and d.department_id = e1.department_id(+);
这将允许优化器提供额外的连接次序和访问方法。就如前面我们所讨论过的。
如果视图位于外连接的右侧，则仅当视图的FROM子句中仅包含一个表（可以是一个表或者另一个视图）时才可被合并。 如果视图包含一个以上的表，查询的语义上要求这两个表的连接要发生在外连接之前。合并参与了外连接的视图还有一些其它的限制，但这些是导致合并外连接视图无效的最常见原因。
总结
本博文涵盖了视图合并的基础，简单select-project-join视图和出现在外连接中的视图是如何合并的，以及为什么其中一些视图不能被合并。在第二部分我们将讨论复杂视图合并，并将揭示视图合并中最神秘视图–VW_NWVW_*视图的原因！
原文链接：https://blogs.oracle.com/optimizer/post/optimizer-transformations-view-merging-part-1
Optimizer Transformations: View Merging part 1
January 2, 2020 | 5 minute read
Maria Colgan
Distinguished Product Manager
We continue our series on query transformations with a two-part discussion of view merging. In these posts, we will review the Oracle terminology related to view merging, explain the different types of view merging, and discuss the reasons that a view might not be merged. The examples in these posts use the Oracle sample schemas.
We use the term view to describe a sub-query block appearing in the FROM clause. Oracle can merge several different types of views:
- Simple view merging, for simple select-project-join views.
- Outer-join view merging for outer-joined views.
- Complex view merging, for distinct and group by views.
In today’s post, we will discuss the first two. We’'ll discuss complex view merging in the next post.
Simple View Merging
Consider a simple query with a view:
select e.first_name, e.last_name, dept_locs_v.street_address, dept_locs_v.postal_code
from employees e,
(select d.department_id, d.department_name, l.street_address, l.postal_code
from departments d, locations l
where d.location_id = l.location_id) dept_locs_v
where dept_locs_v.department_id = e.department_id
and e.last_name = 'Smith';
The query joins the employees table with a view that returns the street address for each department. The view is itself a join of two tables. The query can be executed by joining departments and locations to produce the rows of the view, and then joining that result to employees. Because the query contains the view (V), the join orders that the optimizer can consider are constrained to the following:
E, V
V, E
Within the view, two join orders are considered:
D, L
L, D
So in combination, there are only four possible join orders for this form of the query. The join methods are also constrained; the index-based nested loops join is not feasible for the join order [E, V], since there is no index on the column from the view. Without view merging, the optimizer chooses the following plan:
-----------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 (15)|
|* 1 | HASH JOIN | | 7 (15)|
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 (0)|
|* 3 | INDEX RANGE SCAN | EMP_NAME_IX | 1 (0)|
| 4 | VIEW | | 5 (20)|
|* 5 | HASH JOIN | | 5 (20)|
| 6 | TABLE ACCESS FULL | LOCATIONS | 2 (0)|
| 7 | TABLE ACCESS FULL | DEPARTMENTS | 2 (0)|
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - access("E"."LAST_NAME"='Smith')
5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
View merging merges the tables from the view into the outer query block, removing the view query block. After view merging, the query looks like this:
select e.first_name, e.last_name, l.street_address, l.postal_code
from employees e, departments d, locations l
where d.location_id = l.location_id
and d.department_id = e.department_id
and e.last_name = 'Smith';
Now that all three tables appear in one query block, the optimizer is not constrained by what join orders it can consider (there are a total of 6), and the joins to employees and departments can be index-based. The following plan is chosen with view merging:
-------------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 (0)|
| 1 | NESTED LOOPS | | |
| 2 | NESTED LOOPS | | 4 (0)|
| 3 | NESTED LOOPS | | 3 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 2 (0)|
|* 5 | INDEX RANGE SCAN | EMP_NAME_IX | 1 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 (0)|
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 0 (0)|
|* 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 0 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID | LOCATIONS | 1 (0)|
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."LAST_NAME"='Smith')
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
Oracle uses the term “simple” to refer to select-project-join views. The example above used simple view merging to select the better plan. Such views are automatically merged if it is legal to do so, since it is generally the case that the merged view will result in a plan that is at least as good as the unmerged view would. With the additional join orders and access paths available after a view has been merged, view merging can frequently result in a much better plan. View merging can also allow other transformations to take place; for instance, a table inside of the view may allow a table outside of the view to be join eliminated after the view has been merged and both tables reside in one query block.
There are several reasons why a select-project-join view might not be merged, typically because it is not semantically valid to do so. Some of the reasons a view may not be valid for simple view merging are listed below.
- The view contains constructs other than select, project, join, including:
Group by
Distinct
Outer-join
Spreadsheet clause
Connect by
Set operators
Aggregation - The view appears on the right side of a semi- or anti-join.
- The view contains subqueries in the select list.
- The outer query block contains PL/SQL functions.
Note that some of these constructs do not disallow view merging in all queries, but depend on additional validity constraints.
Outer Join View Merging
If a view is involved in an outer join with tables from the outer query block or if the view contains outer-joined tables, there are many additional restrictions on whether it is valid to merge the view. After view merging, it must be possible to express the query in terms of Oracle outer join syntax. This imposes one significant restriction on views on the left of an outer join: each table from the outer query block can be outer-joined to at most one underlying table of the view. For instance, it is currently not possible to merge the view in this query:
select e1.first_name||' '||e1.last_name emp_name, dept_managers_v.manager_name,
dept_managers_v.department_name
from employees e1,
(select e2.manager_id, e2.first_name||' '||e2.last_name as manager_name,
d.department_id, d.department_name
from departments d, employees e2
where d.manager_id = e2.employee_id) dept_managers_v
where dept_managers_v.department_id = e1.department_id(+)
and dept_managers_v.manager_id = e1.manager_id(+);
If the view were merged, it would result in table e1 being outer joined to two tables, which is not legal in Oracle outer join. But the view in the following query can be merged:
select e1.first_name||' '||e1.last_name emp_name, dept_managers_v.manager_name,
dept_managers_v.department_name
from employees e1,
(select e2.manager_id, e2.first_name||' '||e2.last_name as manager_name,
d.department_id, d.department_name
from departments d, employees e2
where d.manager_id = e2.employee_id) dept_managers_v
where dept_managers_v.department_id = e1.department_id(+);
The merged form of the query looks like this:
select e1.first_name||' '||e1.last_name emp_name,
e2.first_name||' '||e2.last_name as manager_name,
d.department_name
from employees e1, employees e2, departments d
where d.manager_id = e2.employee_id
and d.department_id = e1.department_id(+);
This allows the optimizer to consider additional join orders and access paths like we discussed earlier.
If a view appears on the right of an outer join, the view can be merged only if it contains a single table in the from-clause (which can be a table or another view). If a view contains more than one table, the semantics of the query require the join between those two tables to occur before the outer join. There are additional restrictions on merging of views participating in an outer join, but these are the most common reasons for merging of outer joined views to not be valid.
Summary
In this post we covered the basics of view merging, how it works for simple select-project-join views and views appearing in outer joins, and why one of these views might not be merged. In part two of this post we discuss complex view merging, and reveal the reason for one of the great mysteries of view merging - the VW_NWVW_* view!
