视图合并（View Merging）
来源：互联网 发布：mac软件打不开一直在跳 编辑：程序博客网 时间：2024/06/15 00:34
在使用视图或嵌套视图的查询语句中，oracle 为了取得最优的执行计划会将这些视图进行合并，将视图中的表与外部查询的表进行连接。
--示例：SQL> select e.first_name, e.last_name, dept_locs_v.street_address, dept_locs_v.postal_code 2 from employees e, 3 (select d.department_id, d.department_name, l.street_address, l.postal_code 4 from departments d, locations l 5 where d.location_id = l.location_id) dept_locs_v 6 where dept_locs_v.department_id = e.department_id 7 and e.last_name = 'Smith';执行计划----------------------------------------------------------Plan hash value: 994428606---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 56 | 4 (0)| 00:00:01 || 1 | NESTED LOOPS | | 1 | 56 | 4 (0)| 00:00:01 || 2 | NESTED LOOPS | | 1 | 25 | 3 (0)| 00:00:01 || 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 18 | 2 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | | 1 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 7 | 1 (0)| 00:00:01 ||* 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 || 7 | TABLE ACCESS BY INDEX ROWID | LOCATIONS | 1 | 31 | 1 (0)| 00:00:01 ||* 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | | 0 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("E"."LAST_NAME"='Smith') 6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")--使用no_merge禁止视图合并SQL> select /*+ no_merge(dept_locs_v)*/e.first_name, e.last_name, dept_locs_v.street_address, dept_locs_v.postal_code 2 from employees e, 3 (select d.department_id, d.department_name, l.street_address, l.postal_code 4 from departments d, locations l 5 where d.location_id = l.location_id) dept_locs_v 6 where dept_locs_v.department_id = e.department_id 7 and e.last_name = 'Smith';执行计划----------------------------------------------------------Plan hash value: 842533999--------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 61 | 7 (15)| 00:00:01 ||* 1 | HASH JOIN | | 1 | 61 | 7 (15)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 18 | 2 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | | 1 (0)| 00:00:01 || 4 | VIEW | | 27 | 1161 | 4 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 7 | 1 (0)| 00:00:01 || 6 | NESTED LOOPS | | 27 | 1026 | 4 (0)| 00:00:01 || 7 | TABLE ACCESS FULL | LOCATIONS | 23 | 713 | 3 (0)| 00:00:01 ||* 8 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 4 | | 0 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------Predicate Information (identified by operation 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")可以使用hint来引导优化器，MERGE(v)，合并视图。NO_MERGE(v)，如果在使用该视图的父查询中使用该提示，禁止该视图被合并。
并不是任何使用视图的查询语句都会进行视图合并，在视图中出现以下操作时不能进行视图合并：
Set operators(union,union all,intersact,minus)
Aggregation(avg,count,max,min,sum)
Rownum
Connect by
Group by（隐藏参数_complex_view_merging为true时，可能合并）
Distinct（隐藏参数_complex_view_merging为true时，可能合并）
SQL> --使用rownum, 没有合并SQL> select e.first_name, e.last_name, dept_locs_v.street_address, dept_locs_v.postal_code 2 from employees e, 3 (select rownum ,d.department_id, d.department_name, l.street_address, l.postal_code 4 from departments d, locations l 5 where d.location_id = l.location_id order by 2) dept_locs_v 6 where dept_locs_v.department_id = e.department_id 7 and e.last_name = 'Smith';执行计划----------------------------------------------------------Plan hash value: 2276247677-----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 52 | 6 (17)| 00:00:01 ||* 1 | HASH JOIN | | 1 | 52 | 6 (17)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 18 | 2 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | | 1 (0)| 00:00:01 || 4 | VIEW | | 27 | 918 | 3 (0)| 00:00:01 || 5 | COUNT | | | | | || 6 | NESTED LOOPS | | 27 | 1350 | 3 (0)| 00:00:01 || 7 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 513 | 2 (0)| 00:00:01 || 8 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 || 9 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 | 31 | 1 (0)| 00:00:01 ||* 10 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | | 0 (0)| 00:00:01 |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID") 3 - access("E"."LAST_NAME"='Smith') 10 - access("D"."LOCATION_ID"="L"."LOCATION_ID")还有，当视图在外连接的右侧时，有些不能合并，有些能合并。
当视图在外连接的左侧，并且该视图与外部查询的同一表进行多于一次的外连接时，不能合并。这源于外连接的限制，外部查询的每一个表最多只能与视图中的表进行一次连接。
--当视图在外连接的右侧时，不能合并。SQL> select e1.first_name||' '||e1.last_name emp_name, dept_managers_v.manager_name, 2 dept_managers_v.department_name 3 from employees e1, 4 (select e2.manager_id, e2.first_name||' '||e2.last_name as manager_name, 5 d.department_id, d.department_name 6 from departments d, employees e2 7 where d.manager_id = e2.employee_id) dept_managers_v 8 where dept_managers_v.department_id(+) = e1.department_id;执行计划----------------------------------------------------------Plan hash value: 3319085545------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 107 | 7811 | 9 (12)| 00:00:01 ||* 1 | HASH JOIN OUTER | | 107 | 7811 | 9 (12)| 00:00:01 || 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 || 3 | VIEW | | 11 | 605 | 5 (0)| 00:00:01 || 4 | NESTED LOOPS | | 11 | 418 | 5 (0)| 00:00:01 ||* 5 | TABLE ACCESS FULL | DEPARTMENTS | 11 | 209 | 3 (0)| 00:00:01 || 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 19 | 1 (0)| 00:00:01 ||* 7 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("DEPT_MANAGERS_V"."DEPARTMENT_ID"(+)="E1"."DEPARTMENT_ID") 5 - filter("D"."MANAGER_ID" IS NOT NULL) 7 - access("D"."MANAGER_ID"="E2"."EMPLOYEE_ID")--当视图在外连接的左侧，并且该视图与外部查询的同一表进行一次外连接时，能够合并。SQL> select e1.first_name||' '||e1.last_name emp_name, dept_managers_v.manager_name, 2 dept_managers_v.department_name 3 from employees e1, 4 (select e2.manager_id, e2.first_name||' '||e2.last_name as manager_name, 5 d.department_id, d.department_name 6 from departments d, employees e2 7 where d.manager_id = e2.employee_id) dept_managers_v 8 where dept_managers_v.department_id = e1.department_id(+);执行计划----------------------------------------------------------Plan hash value: 508024882-----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 106 | 5936 | 8 (13)| 00:00:01 || 1 | NESTED LOOPS | | 106 | 5936 | 8 (13)| 00:00:01 || 2 | MERGE JOIN OUTER | | 106 | 3922 | 6 (17)| 00:00:01 ||* 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 11 | 209 | 2 (0)| 00:00:01 || 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 ||* 5 | SORT JOIN | | 107 | 1926 | 4 (25)| 00:00:01 || 6 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1926 | 3 (0)| 00:00:01 || 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 19 | 1 (0)| 00:00:01 ||* 8 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("D"."MANAGER_ID" IS NOT NULL) 5 - access("D"."DEPARTMENT_ID"="E1"."DEPARTMENT_ID"(+)) filter("D"."DEPARTMENT_ID"="E1"."DEPARTMENT_ID"(+)) 8 - access("D"."MANAGER_ID"="E2"."EMPLOYEE_ID") --当视图在外连接的左侧，并且该视图与外部查询的同一表进行多于一次的外连接时，不能合并。SQL> select e1.first_name||' '||e1.last_name emp_name, dept_managers_v.manager_name, 2 dept_managers_v.department_name 3 from employees e1, 4 (select e2.manager_id, e2.first_name||' '||e2.last_name as manager_name, 5 d.department_id, d.department_name 6 from departments d, employees e2 7 where d.manager_id = e2.employee_id) dept_managers_v 8 where dept_managers_v.department_id = e1.department_id(+) 9 and dept_managers_v.manager_id = e1.manager_id(+);执行计划----------------------------------------------------------Plan hash value: 1655263574------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 11 | 990 | 9 (12)| 00:00:01 ||* 1 | HASH JOIN OUTER | | 11 | 990 | 9 (12)| 00:00:01 || 2 | VIEW | | 11 | 748 | 5 (0)| 00:00:01 || 3 | NESTED LOOPS | | 11 | 462 | 5 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL | DEPARTMENTS | 11 | 209 | 3 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 23 | 1 (0)| 00:00:01 ||* 6 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 || 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2354 | 3 (0)| 00:00:01 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("DEPT_MANAGERS_V"."DEPARTMENT_ID"="E1"."DEPARTMENT_ID"(+) AND "DEPT_MANAGERS_V"."MANAGER_ID"="E1"."MANAGER_ID"(+)) 4 - filter("D"."MANAGER_ID" IS NOT NULL) 6 - access("D"."MANAGER_ID"="E2"."EMPLOYEE_ID")
复杂视图或嵌套视图（包含有group by 或 distinct）的视图合并由_complex_view_merging隐藏参数控制，当为true时，优化器评估可能应用视图合并，当为false时，即使使用merge hint也不能应用视图合并。以group by 为例：
当_complex_view_merging设置为true时，以下sql语句
select d.loc,v.avg_sal
from dept d,( select deptno,avg(sal) avg_sal,min(sal) min_sal,max(sal) max_sal
from emp group by deptno) v
where d.deptno=v.deptno and d.loc='CHICAGO';
可能会由查询转换器经视图合并将其转换为以下sql执行
select d.loc,avg(e.sal) avg_sal
from dept d,emp e
where d.deptno=e.deptno and d.loc='CHICAGO'
group by d.rowid,d.loc;
SQL> conn / as sysdba已连接。SQL> @hidParam.sql输入 parname 的值: _complex_view_merging原值 3: WHERE x.indx = y.indx AND ksppinm = '&parName'新值 3: WHERE x.indx = y.indx AND ksppinm = '_complex_view_merging'KSPPINM KSPPSTVL KSPPDESC--------------- ---------- --------------------------------------------------_complex_view_m TRUE enable complex view mergingergingSQL> conn scott/tiger已连接。SQL> select d.loc,v.avg_sal 2 from dept d,( select deptno,avg(sal) avg_sal,min(sal) min_sal,max(sal) max_sal 3 from emp group by deptno) v 4 where d.deptno=v.deptno and d.loc='CHICAGO';执行计划----------------------------------------------------------Plan hash value: 2941989041----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 37 | 7 (43)| 00:00:01 || 1 | NESTED LOOPS | | 1 | 37 | 7 (43)| 00:00:01 || 2 | VIEW | | 3 | 78 | 6 (50)| 00:00:01 || 3 | HASH GROUP BY | | 3 | 21 | 6 (50)| 00:00:01 || 4 | TABLE ACCESS FULL | EMP | 10000 | 70000 | 4 (25)| 00:00:01 ||* 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 ||* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 5 - filter("D"."LOC"='CHICAGO') 6 - access("D"."DEPTNO"="V"."DEPTNO")--手动改变统计信息SQL> exec dbms_stats.set_table_stats(user,'EMP',numrows=>100000);PL/SQL 过程已成功完成。--至此，应用了视图合并SQL> select d.loc,v.avg_sal 2 from dept d,( select deptno,avg(sal) avg_sal,min(sal) min_sal,max(sal) max_sal 3 from emp group by deptno) v 4 where d.deptno=v.deptno and d.loc='CHICAGO';执行计划----------------------------------------------------------Plan hash value: 2006461124----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 81 | 18 (67)| 00:00:01 || 1 | HASH GROUP BY | | 3 | 81 | 18 (67)| 00:00:01 ||* 2 | HASH JOIN | | 33333 | 878K| 13 (54)| 00:00:01 ||* 3 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 || 4 | TABLE ACCESS FULL| EMP | 100K| 683K| 8 (63)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("D"."DEPTNO"="DEPTNO") 3 - filter("D"."LOC"='CHICAGO') --将_complex_view_merging隐藏参数设置为false之后，即使使用merge引导优化器合并视图也是枉然。SQL> alter session set "_complex_view_merging"=false;会话已更改。SQL> select d.loc,v.avg_sal 2 from dept d,( select deptno,avg(sal) avg_sal,min(sal) min_sal,max(sal) max_sal 3 from emp group by deptno) v 4 where d.deptno=v.deptno and d.loc='CHICAGO';执行计划----------------------------------------------------------Plan hash value: 2941989041----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 37 | 23 (83)| 00:00:01 || 1 | NESTED LOOPS | | 1 | 37 | 23 (83)| 00:00:01 || 2 | VIEW | | 3 | 78 | 22 (87)| 00:00:01 || 3 | HASH GROUP BY | | 3 | 21 | 22 (87)| 00:00:01 || 4 | TABLE ACCESS FULL | EMP | 100K| 683K| 8 (63)| 00:00:01 ||* 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 ||* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 5 - filter("D"."LOC"='CHICAGO') 6 - access("D"."DEPTNO"="V"."DEPTNO")SQL> select /*+ merge(v)*/ d.loc,v.avg_sal 2 from dept d,( select deptno,avg(sal) avg_sal,min(sal) min_sal,max(sal) max_sal 3 from emp group by deptno) v 4 where d.deptno=v.deptno and d.loc='CHICAGO';执行计划----------------------------------------------------------Plan hash value: 2941989041----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 37 | 23 (83)| 00:00:01 || 1 | NESTED LOOPS | | 1 | 37 | 23 (83)| 00:00:01 || 2 | VIEW | | 3 | 78 | 22 (87)| 00:00:01 || 3 | HASH GROUP BY | | 3 | 21 | 22 (87)| 00:00:01 || 4 | TABLE ACCESS FULL | EMP | 100K| 683K| 8 (63)| 00:00:01 ||* 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 ||* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 5 - filter("D"."LOC"='CHICAGO') 6 - access("D"."DEPTNO"="V"."DEPTNO")
sql语句如果不能进行视图合并，这个时候oracle查询转换器同样会对该sql进行一种转换，将外部查询的谓词推入（Predicate Pushing）到视图中基表，从而能够使用索引访问，进行这种转换也是为了获得最优的执行计划。需要注意的一点是，谓词推入的前提是该sql中的视图没有进行视图合并。
同样以嵌套视图为例：
SQL> create index ind_emp_deptno on emp(deptno);索引已创建。SQL> set autot traceonly explainSQL> select d.loc,v.avg_sal 2 from dept d,( select e.deptno,avg(e.sal) avg_sal,min(e.sal) min_sal,max(e.sal) max_sal 3 from emp e group by e.deptno) v 4 where d.deptno=v.deptno and v.deptno=20;执行计划----------------------------------------------------------Plan hash value: 3947471307------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 27 | 2 (0)| 00:00:01 || 1 | HASH GROUP BY | | 1 | 27 | 2 (0)| 00:00:01 || 2 | NESTED LOOPS | | 5 | 135 | 2 (0)| 00:00:01 || 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 ||* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 35 | 1 (0)| 00:00:01 ||* 6 | INDEX RANGE SCAN | IND_EMP_DEPTNO | 5 | | 0 (0)| 00:00:01 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("D"."DEPTNO"=20) 6 - access("E"."DEPTNO"=20)SQL> select /*+ no_merge(v) */d.loc,v.avg_sal 2 from dept d,( select e.deptno,avg(e.sal) avg_sal,min(e.sal) min_sal,max(e.sal) max_sal 3 from emp e group by e.deptno) v 4 where d.deptno=v.deptno and v.deptno=20;执行计划----------------------------------------------------------Plan hash value: 87641604-------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 || 1 | NESTED LOOPS | | 1 | 37 | 3 (0)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 11 | 1 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 || 4 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 || 5 | SORT GROUP BY | | 1 | 7 | 2 (0)| 00:00:01 || 6 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 35 | 2 (0)| 00:00:01 ||* 7 | INDEX RANGE SCAN | IND_EMP_DEPTNO | 5 | | 1 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("D"."DEPTNO"=20) 7 - access("E"."DEPTNO"=20) --这里将谓词v.deptno=20推入到了基表e(emp)，从而能够使用索引访问。
还有一种转换叫做join谓词推入。
通常情况下是不能通过基于索引的嵌套循环连接来访问视图的，因为视图（与表不同）上没有索引，而join谓词推入能够基于索引的嵌套循环连接来访问该视图 ， 但是这样的话也并不总是最优的;因为这同样遵循当驱动行源数据量比较大时，hash 连接 或 sort-merge 连接 可能会带来更好的效率。
以下类型的视图支持(当不能视图合并时才有可能推入)这一转换:
UNION ALL/UNION view
Outer-joined view
Anti-joined view
Semi-joined view
DISTINCT view
GROUP-BY view
SQL> create index ind_emp_dept_no on emp(deptno);索引已创建。SQL> set autot traceonly explainSQL> select /*+ no_merge(v) */ d.loc,v.ename 2 from dept d,( select * from emp) v 3 where d.deptno=v.deptno(+) ;执行计划----------------------------------------------------------Plan hash value: 2615629228----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 434 | 7 (15)| 00:00:01 ||* 1 | HASH JOIN OUTER | | 14 | 434 | 7 (15)| 00:00:01 || 2 | TABLE ACCESS FULL | DEPT | 4 | 44 | 3 (0)| 00:00:01 || 3 | VIEW | | 14 | 280 | 3 (0)| 00:00:01 || 4 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("D"."DEPTNO"="V"."DEPTNO"(+))--使用no_merge保证视图不能合并，push_pred 使join谓词推入SQL> select /*+ no_merge(v) push_pred (v) */ d.loc,v.ename 2 from dept d,( select * from emp) v 3 where d.deptno=v.deptno(+) ;执行计划----------------------------------------------------------Plan hash value: 114584144-------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 350 | 11 (0)| 00:00:01 || 1 | NESTED LOOPS OUTER | | 14 | 350 | 11 (0)| 00:00:01 || 2 | TABLE ACCESS FULL | DEPT | 4 | 56 | 3 (0)| 00:00:01 || 3 | VIEW PUSHED PREDICATE | | 1 | 11 | 2 (0)| 00:00:01 || 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 60 | 2 (0)| 00:00:01 ||* 5 | INDEX RANGE SCAN | IND_EMP_DEPT_NO | 5 | | 1 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 5 - access("EMP"."DEPTNO"="D"."DEPTNO") --join谓词已推入到基表，并访问基表的索引。SQL> select /*+ no_merge(v) no_push_pred (v) */ d.loc,v.ename 2 from dept d,( select * from emp) v 3 where d.deptno=v.deptno(+) ;执行计划----------------------------------------------------------Plan hash value: 2615629228----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 434 | 7 (15)| 00:00:01 ||* 1 | HASH JOIN OUTER | | 14 | 434 | 7 (15)| 00:00:01 || 2 | TABLE ACCESS FULL | DEPT | 4 | 44 | 3 (0)| 00:00:01 || 3 | VIEW | | 14 | 280 | 3 (0)| 00:00:01 || 4 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("D"."DEPTNO"="V"."DEPTNO"(+)) - 视图合并（View Merging）
- 视图合并（View Merging）
- 理解Git——Merging(合并)
- 【Android Studio】Resource Merging资源合并规则
- 合并有序数组(Merging sorted array)
- SQL VIEW（视图）
- sql 视图（view）
- 视图（view）
- SQL VIEW（视图）
- Oracle 视图（View）
- Oracle 视图（View）
- 视图（view）用法
- Oracle 视图（View）
- ORACLE 视图（VIEW）
- SQL VIEW（视图）
- SQL VIEW（视图）
- SQL VIEW（视图）
- SQL VIEW（视图）
- paip. VS2010版本控制tfs使用总结
- HOW TO：在 Visual C# .NET 中设置窗口挂钩
- paip.svn使用小结
- MYSQL常用命令
- 关于初学者该选择学习ARM7还是ARM9的一点建议
- 视图合并（View Merging）
- Scala学习文档
- 暑假闭关计划
- Algorithms 学习笔记04 Sort
- paip.提升开发效率之查询界面
- ORA-04031错误的解决思路
- windowsphone7 消息推送Demo
- 关于windows64位与32位操作系统的浅见（初学者，第一篇博客）
- 为什么基类指针指向派生类是安全的，而派生类指向基类则是不安全的
