Oracle SQL 调优：视图合并（View Merging）

Oracle SQL 调优：视图合并（View Merging）

在使用视图或嵌套视图的查询语句中，Oracle 为了取得最优的执行计划会将这些视图进行合并，将视图中的表与外部查询的表进行连接。

1. --示例：   
2. SQL> select e.first_name, e.last_name, dept_locs_v.street_address, dept_locs_v.postal_code  
3.   2  from employees e,  
4.   3       (select d.department_id, d.department_name, l.street_address, l.postal_code  
5.   4        from departments d, locations l  
6.   5        where d.location_id = l.location_id) dept_locs_v  
7.   6  where dept_locs_v.department_id = e.department_id  
8.   7  and e.last_name = 'Smith';  
9.   
10. 执行计划  
11. ----------------------------------------------------------   
12. Plan hash value: 994428606  
13.   
14. ---------------------------------------------------------------------------------------------   
15. | Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
16. ---------------------------------------------------------------------------------------------   
17. |   0 | SELECT STATEMENT              |             |     1 |    56 |     4   (0)| 00:00:01 |  
18. |   1 |  NESTED LOOPS                 |             |     1 |    56 |     4   (0)| 00:00:01 |  
19. |   2 |   NESTED LOOPS                |             |     1 |    25 |     3   (0)| 00:00:01 |  
20. |   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     1 |    18 |     2   (0)| 00:00:01 |  
21. |*  4 |     INDEX RANGE SCAN          | EMP_NAME_IX |     1 |       |     1   (0)| 00:00:01 |  
22. |   5 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |     7 |     1   (0)| 00:00:01 |  
23. |*  6 |     INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |  
24. |   7 |   TABLE ACCESS BY INDEX ROWID | LOCATIONS   |     1 |    31 |     1   (0)| 00:00:01 |  
25. |*  8 |    INDEX UNIQUE SCAN          | LOC_ID_PK   |     1 |       |     0   (0)| 00:00:01 |  
26. ---------------------------------------------------------------------------------------------   
27.   
28. Predicate Information (identified by operation id):  
29. ---------------------------------------------------   
30.   
31.    4 - access("E"."LAST_NAME"='Smith')  
32.    6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")  
33.    8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")  
34.   
35. --使用no_merge禁止视图合并   
36. SQL> select /*+ no_merge(dept_locs_v)*/e.first_name, e.last_name, dept_locs_v.street_address, dept_locs_v.postal_code  
37.   2  from employees e,  
38.   3       (select d.department_id, d.department_name, l.street_address, l.postal_code  
39.   4        from departments d, locations l  
40.   5        where d.location_id = l.location_id) dept_locs_v  
41.   6  where dept_locs_v.department_id = e.department_id  
42.   7  and e.last_name = 'Smith';  
43.   
44. 执行计划  
45. ----------------------------------------------------------   
46. Plan hash value: 842533999  
47.   
48. --------------------------------------------------------------------------------------------------   
49. | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |  
50. --------------------------------------------------------------------------------------------------   
51. |   0 | SELECT STATEMENT              |                  |     1 |    61 |     7  (15)| 00:00:01 |  
52. |*  1 |  HASH JOIN                    |                  |     1 |    61 |     7  (15)| 00:00:01 |  
53. |   2 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES        |     1 |    18 |     2   (0)| 00:00:01 |  
54. |*  3 |    INDEX RANGE SCAN           | EMP_NAME_IX      |     1 |       |     1   (0)| 00:00:01 |  
55. |   4 |   VIEW                        |                  |    27 |  1161 |     4   (0)| 00:00:01 |  
56. |   5 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS      |     1 |     7 |     1   (0)| 00:00:01 |  
57. |   6 |     NESTED LOOPS              |                  |    27 |  1026 |     4   (0)| 00:00:01 |  
58. |   7 |      TABLE ACCESS FULL        | LOCATIONS        |    23 |   713 |     3   (0)| 00:00:01 |  
59. |*  8 |      INDEX RANGE SCAN         | DEPT_LOCATION_IX |     4 |       |     0   (0)| 00:00:01 |  
60. --------------------------------------------------------------------------------------------------   
61.   
62. Predicate Information (identified by operation id):  
63. ---------------------------------------------------   
64.   
65.    1 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")  
66.    3 - access("E"."LAST_NAME"='Smith')  
67.    8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")