Oracle SQL optimization: View Merging)

In a query statement using a view or nested view, Oracle merges these views to obtain the optimal execution plan and connects the tables in the view to the tables in the external query.

1. -- Example:
2. SQL>SelectE. first_name, e. last_name, dept_locs_v.street_address, dept_locs_v.postal_code
3. 2FromEmployees e,
4. 3 (SelectD. department_id, d. department_name, l. street_address, l. postal_code
5. 4FromSpecified ments d, locations l
6. 5WhereD. location_id = l. location_id) dept_locs_v
7. 6WhereDept_locs_v.department_id = e. department_id
8. 7AndE. last_name ='Smith';
10. Execution Plan
11. ----------------------------------------------------------
12. Plan hash value: 994428606
14. Bytes ---------------------------------------------------------------------------------------------
15. | Id | Operation |Name|Rows| Bytes | Cost (% CPU) |Time|
16. Bytes ---------------------------------------------------------------------------------------------
17. | 0 |SELECTSTATEMENT | 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 |TABLEACCESSBY INDEXROWID | EMPLOYEES | 1 | 18 | 2 (0) | 00:00:01 |
21. | * 4 |INDEXRange scan | EMP_NAME_IX | 1 | 1 (0) | 00:00:01 |
22. | 5 |TABLEACCESSBY INDEXROWID | ments | 1 | 7 | 1 (0) | 00:00:01 |
23. | * 6 |INDEX UNIQUESCAN | DEPT_ID_PK | 1 | 0 (0) | 00:00:01 |
24. | 7 |TABLEACCESSBY INDEXROWID | LOCATIONS | 1 | 31 | 1 (0) | 00:00:01 |
25. | * 8 |INDEX UNIQUESCAN | LOC_ID_PK | 1 | 0 (0) | 00:00:01 |
26. Bytes ---------------------------------------------------------------------------------------------
28. Predicate Information (identifiedByOperation id ):
29. ---------------------------------------------------
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")
35. -- Use no_merge to disable view Merging
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. 2FromEmployees e,
38. 3 (SelectD. department_id, d. department_name, l. street_address, l. postal_code
39. 4FromSpecified ments d, locations l
40. 5WhereD. location_id = l. location_id) dept_locs_v
41. 6WhereDept_locs_v.department_id = e. department_id
42. 7AndE. last_name ='Smith';
44. Execution Plan
45. ----------------------------------------------------------
46. Plan hash value: 842533999
48. Bytes --------------------------------------------------------------------------------------------------
49. | Id | Operation |Name|Rows| Bytes | Cost (% CPU) |Time|
50. Bytes --------------------------------------------------------------------------------------------------
51. | 0 |SELECTSTATEMENT | 1 | 61 | 7 (15) | 00:00:01 |
52. | * 1 | HASHJOIN| 1 | 61 | 7 (15) | 00:00:01 |
53. | 2 |TABLEACCESSBY INDEXROWID | EMPLOYEES | 1 | 18 | 2 (0) | 00:00:01 |
54. | * 3 |INDEXRange scan | EMP_NAME_IX | 1 | 1 (0) | 00:00:01 |
55. | 4 |VIEW| 27 | 1161 | 4 (0) | 00:00:01 |
56. | 5 |TABLEACCESSBY INDEXROWID | ments | 1 | 7 | 1 (0) | 00:00:01 |
57. | 6 | nested loops | 27 | 1026 | 4 (0) | 00:00:01 |
58. | 7 |TABLEACCESSFULL| LOCATIONS | 23 | 713 | 3 (0) | 00:00:01 |
59. | * 8 |INDEXRange scan | DEPT_LOCATION_IX | 4 | 0 (0) | 00:00:01 |
60. Bytes --------------------------------------------------------------------------------------------------
62. Predicate Information (identifiedByOperation id ):
63. ---------------------------------------------------
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 Next Page