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")  

1 2 3 下一頁