参考：https://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_1
本文演示所用的表均来自Oracle sample schemas的HR用户。
视图合并：优化器不再将目标sql中视图的定义当做一个独立的处理单元来单独执行，
而是将其拆开，把其定义SQL中的基表拿出来与外部查询中的表合并。这样，执行计划中
就不会再有视图出现。
Oracle can merge several different types of views:
1、Simple view merging, for simple select-project-join views.
2、Outer-join view merging for outer-joined views.
3、Complex view merging, for distinct and group by views.
==》oracle里有三种类型的视图合并，本文件将介绍1，2两种类型。
Simple view merging, for simple select-project-join views.
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 (UNION,UNION ALL,INTERSECT,MINUS)
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.
==》简单的视图合是指不包含外连接或视图中不含Group by、Distinct等聚合函数的sql的视图的合并。
示例1：selecte.first_name,e.last_name,dept_locs_v.street_address,dept_locs_v.postal_code
fromemployees e,
(selectd.department_id,d.department_name,l.street_address,l.postal_code
fromdepartments d,locations l
whered.location_id=l.location_id)dept_locs_v
wheredept_locs_v.department_id=e.department_id
ande.last_name= 'Smith';
Execution Plan
----------------------------------------------------------
Plan hashvalue:1618608772
----------------------------------------------------------------------------------------------
|Id|Operation|Name| Rows |Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------------
|0| SELECTSTATEMENT| |1|56|4(0)|00:00:01|
|1|NESTEDLOOPS| | | | | |
|2|NESTEDLOOPS| |1|56|4(0)|00:00:01|
|3|NESTEDLOOPS| |1|25|3(0)|00:00:01|
|4|TABLE ACCESS BY INDEX ROWID|EMPLOYEES|1|18|2(0)|00:00:01|
|*5|INDEXRANGE SCAN|EMP_NAME_IX|1| |1(0)|00:00:01|
|6|TABLE ACCESS BY INDEX ROWID|DEPARTMENTS|1|7|1(0)|00:00:01|
|*7|INDEX UNIQUESCAN|DEPT_ID_PK|1| |0(0)|00:00:01|
|*8|INDEX UNIQUESCAN|LOC_ID_PK|1| |0(0)|00:00:01|
|9|TABLE ACCESS BY INDEX ROWID |LOCATIONS|1|31|1(0)|00:00:01|
----------------------------------------------------------------------------------------------
Predicate Information(identified byoperation id):
---------------------------------------------------
5- access("E"."LAST_NAME"='Smith')
7- access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
8- access("D"."LOCATION_ID"="L"."LOCATION_ID")
==》这里执行步骤中未出现view关键字，说明做了视图合并。
示例2：使用hint不走视图合并
select /*+ no_merge(dept_locs_v) */e.first_name,e.last_name,dept_locs_v.street_address,dept_locs_v.postal_code
fromemployees e,
(selectd.department_id,d.department_name,l.street_address,l.postal_code
fromdepartments d,locations l
whered.location_id=l.location_id)dept_locs_v
wheredept_locs_v.department_id=e.department_id
ande.last_name= 'Smith';
Execution Plan
----------------------------------------------------------
Plan hashvalue:1227207751
---------------------------------------------------------------------------------------------------
|Id|Operation|Name| Rows |Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------------------------------
|0| SELECTSTATEMENT| |1|61|8(25)|00:00:01|
|*1|HASH JOIN| |1|61|8(25)|00:00:01|
|2|TABLE ACCESS BY INDEX ROWID |EMPLOYEES|1|18|2(0)|00:00:01|
|*3|INDEXRANGE SCAN|EMP_NAME_IX|1| |1(0)|00:00:01|
|4|VIEW | |27|1161|6(34)|00:00:01|
|5|MERGE JOIN| |27|1026|6(34)|00:00:01|
|6|TABLE ACCESS BY INDEX ROWID|LOCATIONS|23|713|2(0)|00:00:01|
|7|INDEXFULL SCAN|LOC_ID_PK|23| |1(0)|00:00:01|
|*8|SORT JOIN| |27|189|4(50)|00:00:01|
|9|VIEW | index$_join$_003|27|189|3(34)|00:00:01|
|*10|HASH JOIN| | | | | |
|11|INDEXFAST FULL SCAN|DEPT_ID_PK|27|189|1(0)|00:00:01|
|12|INDEXFAST FULL SCAN|DEPT_LOCATION_IX|27|189|1(0)|00:00:01|
---------------------------------------------------------------------------------------------------
Predicate Information(identified byoperation id):
---------------------------------------------------
1- access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3- access("E"."LAST_NAME"='Smith')
8- access("D"."LOCATION_ID"="L"."LOCATION_ID")
filter("D"."LOCATION_ID"="L"."LOCATION_ID")
10- access(ROWID=ROWID)
==》步骤9的view关键字是索引连接的原因。而步骤4的view关键字才是视图未合并的体现。
这里步骤4name列为空，而不是‘dept_locs_v’，这是因为出现在from关键后面的是query block而不是我们定义的视图。
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.==>在语义相同的情况下，视图合并会增加额外的连接顺序和获取路径，以获得更好的执行计划。
Outer-join view merging for outer-joined views.
==》是指外部查询的表和视图之间使用了外连接，或者是该视图定义中使用了外连接。且该视图定义中不包含Group by、Distinct等聚合函数。
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.
==》外部表和视图做外连接视图合并的前提是，要么该视图被作为外连接的驱动表，要么该视图作为被驱动表，但视图定义中只包含一张表。
示例3：这里dept_managers_v作为驱动表且查询块中未包含聚合函数等限制。
create or replace viewdept_managers_vas
selecte2.manager_id,e2.first_name||' '||e2.last_nameasmanager_name,
d.department_id,d.department_name
fromdepartments d,employees e2
whered.manager_id=e2.employee_id;
selecte1.first_name||' '||e1.last_name emp_name,dept_managers_v.manager_name,
dept_managers_v.department_name
fromemployees e1,
dept_managers_v
wheredept_managers_v.department_id=e1.department_id(+);
Execution Plan
----------------------------------------------------------
Plan hashvalue:2673826498
---------------------------------------------------------------------------------------------------
|Id|Operation|Name| Rows |Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------------------------------
|0| SELECTSTATEMENT| |106|5936|9(34)|00:00:01|
|*1|HASH JOIN| |106|5936|9(34)|00:00:01|
|2|MERGE JOIN OUTER| |106|3922|6(34)|00:00:01|
|*3|TABLE ACCESS BY INDEX ROWID|DEPARTMENTS|11|209|2(0)|00:00:01|
|4|INDEXFULL SCAN|DEPT_ID_PK|27| |1(0)|00:00:01|
|*5|SORT JOIN| |107|1926|4(50)|00:00:01|
|6|VIEW | index$_join$_001|107|1926|3(34)|00:00:01|
|*7|HASH JOIN| | | | | |
|8|INDEXFAST FULL SCAN|EMP_DEPARTMENT_IX|107|1926|1(0)|00:00:01|
|9| INDEXFAST FULL SCAN|EMP_NAME_IX|107|1926|1(0)|00:00:01|
|10|VIEW | index$_join$_004|107|2033|3(34)|00:00:01|
|*11|HASH JOIN| | | | | |
|12|INDEXFAST FULL SCAN|EMP_NAME_IX|107|2033|1(0)|00:00:01|
|13|INDEXFAST FULL SCAN|EMP_EMP_ID_PK|107|2033|1(0)|00:00:01|
---------------------------------------------------------------------------------------------------
Predicate Information(identified byoperation id):
---------------------------------------------------
1- access("D"."MANAGER_ID"="E2"."EMPLOYEE_ID")
3-filter("D"."MANAGER_ID" IS NOT NULL)
5- access("D"."DEPARTMENT_ID"="E1"."DEPARTMENT_ID"(+))
filter("D"."DEPARTMENT_ID"="E1"."DEPARTMENT_ID"(+))
7- access(ROWID=ROWID)
11- access(ROWID=ROWID)
==》执行计划中未出现步骤view且name列为‘dept_managers_v’的路径，则说明做了视图合并。
示例4：这里dept_managers_v作为被驱动表
selecte1.first_name||' '||e1.last_name emp_name,dept_managers_v.manager_name,
dept_managers_v.department_name
fromemployees e1,
dept_managers_v
wheree1.department_id=dept_managers_v.department_id(+);
Execution Plan
----------------------------------------------------------
Plan hashvalue:4283792639
----------------------------------------------------------------------------------------------
|Id|Operation|Name| Rows |Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------------------
|0| SELECTSTATEMENT| |107|7811|10(20)|00:00:01|
|*1|HASH JOIN OUTER| |107|7811|10(20)|00:00:01|
|2|TABLE ACCESSFULL|EMPLOYEES|107|1926|3(0)|00:00:01|
|3|VIEW |DEPT_MANAGERS_V|11|605|6(17)|00:00:01|
|*4|HASH JOIN| |11|418|6(17)|00:00:01|
|*5|TABLE ACCESSFULL|DEPARTMENTS|11|209|3(0)|00:00:01|
|6|VIEW | index$_join$_004|107|2033|3(34)|00:00:01|
|*7|HASH JOIN| | | | | |
|8|INDEXFAST FULL SCAN|EMP_NAME_IX|107|2033|1(0)|00:00:01|
|9|INDEXFAST FULL SCAN|EMP_EMP_ID_PK|107|2033|1(0)|00:00:01|
----------------------------------------------------------------------------------------------
Predicate Information(identified byoperation id):
---------------------------------------------------
1- access("E1"."DEPARTMENT_ID"="DEPT_MANAGERS_V"."DEPARTMENT_ID"(+))
4- access("D"."MANAGER_ID"="E2"."EMPLOYEE_ID")
5-filter("D"."MANAGER_ID" IS NOT NULL)
7- access(ROWID=ROWID)
==》这里步骤3中出现了view关键字，对应的name列的值是DEPT_MANAGERS_V。说明这里未做视图合并。
示例5：这里dept_managers_v作为被驱动表，但是该视图只包含一张表的定义。
create or replace viewdept_managers_vas
select
d.department_id,d.department_name
fromdepartments d;
select
e1.first_name||' '||e1.last_name emp_name,dept_managers_v.department_name
fromemployees e1,
dept_managers_v
wheree1.department_id=dept_managers_v.department_id(+);
Execution Plan
----------------------------------------------------------
Plan hashvalue:2296652067
----------------------------------------------------------------------------------
|Id|Operation|Name| Rows |Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------------
|0| SELECTSTATEMENT| |107|3638|7(15)|00:00:01|
|*1|HASH JOIN OUTER| |107|3638|7(15)|00:00:01|
|2|TABLE ACCESSFULL|EMPLOYEES|107|1926|3(0)|00:00:01|
|3|TABLE ACCESSFULL|DEPARTMENTS|27|432|3(0)|00:00:01|
----------------------------------------------------------------------------------
Predicate Information(identified byoperation id):
---------------------------------------------------
1- access("E1"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))
==》这里可以看出做了视图合并的。
end!
487
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
