在使用视图或嵌套视图的查询语句中,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