我們用兩篇視圖合并的討論，來繼續我們查詢轉換的系列。在這些博文中，我們會回顧有關視圖合并的術語，解釋不同類型的視圖合并，并討論視圖未能合并的原因。博文中的樣例使用了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!
