视图合并（View Merging）

最新推荐文章于 2024-01-24 15:48:10 发布
最新推荐文章于 2024-01-24 15:48:10 发布
阅读量3k 收藏 2
点赞数
分类专栏： SQL优化 文章标签： SQL PLSQL

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

[sql] view plain copy print ?
  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")  
--示例：
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] view plain copy print ?
  1. SQL> --使用rownum, 没有合并  
  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  rownum ,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 order by 2) 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: 2276247677  
  13.   
  14. -----------------------------------------------------------------------------------------------  
  15. | Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  16. -----------------------------------------------------------------------------------------------  
  17. |   0 | SELECT STATEMENT                |             |     1 |    52 |     6  (17)| 00:00:01 |  
  18. |*  1 |  HASH JOIN                      |             |     1 |    52 |     6  (17)| 00:00:01 |  
  19. |   2 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEES   |     1 |    18 |     2   (0)| 00:00:01 |  
  20. |*  3 |    INDEX RANGE SCAN             | EMP_NAME_IX |     1 |       |     1   (0)| 00:00:01 |  
  21. |   4 |   VIEW                          |             |    27 |   918 |     3   (0)| 00:00:01 |  
  22. |   5 |    COUNT                        |             |       |       |            |          |  
  23. |   6 |     NESTED LOOPS                |             |    27 |  1350 |     3   (0)| 00:00:01 |  
  24. |   7 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   513 |     2   (0)| 00:00:01 |  
  25. |   8 |       INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |  
  26. |   9 |      TABLE ACCESS BY INDEX ROWID| LOCATIONS   |     1 |    31 |     1   (0)| 00:00:01 |  
  27. |* 10 |       INDEX UNIQUE SCAN         | LOC_ID_PK   |     1 |       |     0   (0)| 00:00:01 |  
  28. -----------------------------------------------------------------------------------------------  
  29.   
  30. Predicate Information (identified by operation id):  
  31. ---------------------------------------------------  
  32.   
  33.    1 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")  
  34.    3 - access("E"."LAST_NAME"='Smith')  
  35.   10 - access("D"."LOCATION_ID"="L"."LOCATION_ID")  
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] view plain copy print ?
  1. --当视图在外连接的右侧时，不能合并。  
  2. SQL> select e1.first_name||' '||e1.last_name emp_name, dept_managers_v.manager_name,  
  3.   2        dept_managers_v.department_name  
  4.   3  from employees e1,  
  5.   4      (select e2.manager_id, e2.first_name||' '||e2.last_name as manager_name,  
  6.   5              d.department_id, d.department_name  
  7.   6       from departments d, employees e2  
  8.   7       where d.manager_id = e2.employee_id) dept_managers_v  
  9.   8  where dept_managers_v.department_id(+) = e1.department_id;  
  10.   
  11. 执行计划  
  12. ----------------------------------------------------------  
  13. Plan hash value: 3319085545  
  14.   
  15. ------------------------------------------------------------------------------------------------  
  16. | Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |  
  17. ------------------------------------------------------------------------------------------------  
  18. |   0 | SELECT STATEMENT               |               |   107 |  7811 |     9  (12)| 00:00:01 |  
  19. |*  1 |  HASH JOIN OUTER               |               |   107 |  7811 |     9  (12)| 00:00:01 |  
  20. |   2 |   TABLE ACCESS FULL            | EMPLOYEES     |   107 |  1926 |     3   (0)| 00:00:01 |  
  21. |   3 |   VIEW                         |               |    11 |   605 |     5   (0)| 00:00:01 |  
  22. |   4 |    NESTED LOOPS                |               |    11 |   418 |     5   (0)| 00:00:01 |  
  23. |*  5 |     TABLE ACCESS FULL          | DEPARTMENTS   |    11 |   209 |     3   (0)| 00:00:01 |  
  24. |   6 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    19 |     1   (0)| 00:00:01 |  
  25. |*  7 |      INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |  
  26. ------------------------------------------------------------------------------------------------  
  27.   
  28. Predicate Information (identified by operation id):  
  29. ---------------------------------------------------  
  30.   
  31.    1 - access("DEPT_MANAGERS_V"."DEPARTMENT_ID"(+)="E1"."DEPARTMENT_ID")  
  32.    5 - filter("D"."MANAGER_ID" IS NOT NULL)  
  33.    7 - access("D"."MANAGER_ID"="E2"."EMPLOYEE_ID")  
  34.   
  35. --当视图在外连接的左侧，并且该视图与外部查询的同一表进行一次外连接时，能够合并。  
  36. SQL> select e1.first_name||' '||e1.last_name emp_name, dept_managers_v.manager_name,  
  37.   2        dept_managers_v.department_name  
  38.   3  from employees e1,  
  39.   4      (select e2.manager_id, e2.first_name||' '||e2.last_name as manager_name,  
  40.   5              d.department_id, d.department_name  
  41.   6       from departments d, employees e2  
  42.   7       where d.manager_id = e2.employee_id) dept_managers_v  
  43.   8  where dept_managers_v.department_id = e1.department_id(+);  
  44.   
  45. 执行计划  
  46. ----------------------------------------------------------  
  47. Plan hash value: 508024882  
  48.   
  49. -----------------------------------------------------------------------------------------------  
  50. | Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |  
  51. -----------------------------------------------------------------------------------------------  
  52. |   0 | SELECT STATEMENT              |               |   106 |  5936 |     8  (13)| 00:00:01 |  
  53. |   1 |  NESTED LOOPS                 |               |   106 |  5936 |     8  (13)| 00:00:01 |  
  54. |   2 |   MERGE JOIN OUTER            |               |   106 |  3922 |     6  (17)| 00:00:01 |  
  55. |*  3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS   |    11 |   209 |     2   (0)| 00:00:01 |  
  56. |   4 |     INDEX FULL SCAN           | DEPT_ID_PK    |    27 |       |     1   (0)| 00:00:01 |  
  57. |*  5 |    SORT JOIN                  |               |   107 |  1926 |     4  (25)| 00:00:01 |  
  58. |   6 |     TABLE ACCESS FULL         | EMPLOYEES     |   107 |  1926 |     3   (0)| 00:00:01 |  
  59. |   7 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES     |     1 |    19 |     1   (0)| 00:00:01 |  
  60. |*  8 |    INDEX UNIQUE SCAN          | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |  
  61. -----------------------------------------------------------------------------------------------  
  62.   
  63. Predicate Information (identified by operation id):  
  64. ---------------------------------------------------  
  65.   
  66.    3 - filter("D"."MANAGER_ID" IS NOT NULL)  
  67.    5 - access("D"."DEPARTMENT_ID"="E1"."DEPARTMENT_ID"(+))  
  68.        filter("D"."DEPARTMENT_ID"="E1"."DEPARTMENT_ID"(+))  
  69.    8 - access("D"."MANAGER_ID"="E2"."EMPLOYEE_ID")    
  70.     
  71. --当视图在外连接的左侧，并且该视图与外部查询的同一表进行多于一次的外连接时，不能合并。  
  72. SQL> select e1.first_name||' '||e1.last_name emp_name, dept_managers_v.manager_name,  
  73.   2        dept_managers_v.department_name  
  74.   3  from employees e1,  
  75.   4      (select e2.manager_id, e2.first_name||' '||e2.last_name as manager_name,  
  76.   5              d.department_id, d.department_name  
  77.   6       from departments d, employees e2  
  78.   7       where d.manager_id = e2.employee_id) dept_managers_v  
  79.   8  where dept_managers_v.department_id = e1.department_id(+)  
  80.   9  and dept_managers_v.manager_id = e1.manager_id(+);  
  81.   
  82. 执行计划  
  83. ----------------------------------------------------------  
  84. Plan hash value: 1655263574  
  85.   
  86. ------------------------------------------------------------------------------------------------  
  87. | Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |  
  88. ------------------------------------------------------------------------------------------------  
  89. |   0 | SELECT STATEMENT               |               |    11 |   990 |     9  (12)| 00:00:01 |  
  90. |*  1 |  HASH JOIN OUTER               |               |    11 |   990 |     9  (12)| 00:00:01 |  
  91. |   2 |   VIEW                         |               |    11 |   748 |     5   (0)| 00:00:01 |  
  92. |   3 |    NESTED LOOPS                |               |    11 |   462 |     5   (0)| 00:00:01 |  
  93. |*  4 |     TABLE ACCESS FULL          | DEPARTMENTS   |    11 |   209 |     3   (0)| 00:00:01 |  
  94. |   5 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    23 |     1   (0)| 00:00:01 |  
  95. |*  6 |      INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |  
  96. |   7 |   TABLE ACCESS FULL            | EMPLOYEES     |   107 |  2354 |     3   (0)| 00:00:01 |  
  97. ------------------------------------------------------------------------------------------------  
  98.   
  99. Predicate Information (identified by operation id):  
  100. ---------------------------------------------------  
  101.   
  102.    1 - access("DEPT_MANAGERS_V"."DEPARTMENT_ID"="E1"."DEPARTMENT_ID"(+) AND  
  103.               "DEPT_MANAGERS_V"."MANAGER_ID"="E1"."MANAGER_ID"(+))  
  104.    4 - filter("D"."MANAGER_ID" IS NOT NULL)  
  105.    6 - 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: 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] view plain copy print ?
  1. SQL> conn / as sysdba  
  2. 已连接。  
  3. SQL> @hidParam.sql  
  4. 输入 parname 的值:  _complex_view_merging  
  5. 原值    3:  WHERE   x.indx = y.indx AND  ksppinm = '&parName'  
  6. 新值    3:  WHERE   x.indx = y.indx AND  ksppinm = '_complex_view_merging'  
  7.   
  8. KSPPINM         KSPPSTVL   KSPPDESC  
  9. --------------- ---------- --------------------------------------------------  
  10. _complex_view_m TRUE       enable complex view merging  
  11. erging  
  12.   
  13.   
  14. SQL> conn scott/tiger  
  15. 已连接。  
  16. SQL> select d.loc,v.avg_sal  
  17.   2  from dept d,( select deptno,avg(sal) avg_sal,min(sal) min_sal,max(sal) max_sal  
  18.   3   from emp group by deptno) v  
  19.   4  where d.deptno=v.deptno and d.loc='CHICAGO';  
  20.   
  21. 执行计划  
  22. ----------------------------------------------------------  
  23. Plan hash value: 2941989041  
  24.   
  25. ----------------------------------------------------------------------------------------  
  26. | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
  27. ----------------------------------------------------------------------------------------  
  28. |   0 | SELECT STATEMENT             |         |     1 |    37 |     7  (43)| 00:00:01 |  
  29. |   1 |  NESTED LOOPS                |         |     1 |    37 |     7  (43)| 00:00:01 |  
  30. |   2 |   VIEW                       |         |     3 |    78 |     6  (50)| 00:00:01 |  
  31. |   3 |    HASH GROUP BY             |         |     3 |    21 |     6  (50)| 00:00:01 |  
  32. |   4 |     TABLE ACCESS FULL        | EMP     | 10000 | 70000 |     4  (25)| 00:00:01 |  
  33. |*  5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |  
  34. |*  6 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |  
  35. ----------------------------------------------------------------------------------------  
  36.   
  37. Predicate Information (identified by operation id):  
  38. ---------------------------------------------------  
  39.   
  40.    5 - filter("D"."LOC"='CHICAGO')  
  41.    6 - access("D"."DEPTNO"="V"."DEPTNO")  
  42.   
  43. --手动改变统计信息  
  44. SQL>  exec dbms_stats.set_table_stats(user,'EMP',numrows=>100000);  
  45.   
  46. PL/SQL 过程已成功完成。  
  47.   
  48. --至此，应用了视图合并   
  49. SQL> select d.loc,v.avg_sal  
  50.   2  from dept d,( select deptno,avg(sal) avg_sal,min(sal) min_sal,max(sal) max_sal  
  51.   3   from emp group by deptno) v  
  52.   4  where d.deptno=v.deptno and d.loc='CHICAGO';  
  53.   
  54. 执行计划  
  55. ----------------------------------------------------------  
  56. Plan hash value: 2006461124  
  57.   
  58. ----------------------------------------------------------------------------  
  59. | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  60. ----------------------------------------------------------------------------  
  61. |   0 | SELECT STATEMENT    |      |     3 |    81 |    18  (67)| 00:00:01 |  
  62. |   1 |  HASH GROUP BY      |      |     3 |    81 |    18  (67)| 00:00:01 |  
  63. |*  2 |   HASH JOIN         |      | 33333 |   878K|    13  (54)| 00:00:01 |  
  64. |*  3 |    TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:00:01 |  
  65. |   4 |    TABLE ACCESS FULL| EMP  |   100K|   683K|     8  (63)| 00:00:01 |  
  66. ----------------------------------------------------------------------------  
  67.   
  68. Predicate Information (identified by operation id):  
  69. ---------------------------------------------------  
  70.   
  71.    2 - access("D"."DEPTNO"="DEPTNO")  
  72.    3 - filter("D"."LOC"='CHICAGO')  
  73.   
  74.      
  75. --将_complex_view_merging隐藏参数设置为false之后，即使使用merge引导优化器合并视图也是枉然。  
  76. SQL> alter session set "_complex_view_merging"=false;  
  77.   
  78. 会话已更改。  
  79.   
  80. SQL> select d.loc,v.avg_sal  
  81.   2  from dept d,( select deptno,avg(sal) avg_sal,min(sal) min_sal,max(sal) max_sal  
  82.   3   from emp group by deptno) v  
  83.   4  where d.deptno=v.deptno and d.loc='CHICAGO';  
  84.   
  85. 执行计划  
  86. ----------------------------------------------------------  
  87. Plan hash value: 2941989041  
  88.   
  89. ----------------------------------------------------------------------------------------  
  90. | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
  91. ----------------------------------------------------------------------------------------  
  92. |   0 | SELECT STATEMENT             |         |     1 |    37 |    23  (83)| 00:00:01 |  
  93. |   1 |  NESTED LOOPS                |         |     1 |    37 |    23  (83)| 00:00:01 |  
  94. |   2 |   VIEW                       |         |     3 |    78 |    22  (87)| 00:00:01 |  
  95. |   3 |    HASH GROUP BY             |         |     3 |    21 |    22  (87)| 00:00:01 |  
  96. |   4 |     TABLE ACCESS FULL        | EMP     |   100K|   683K|     8  (63)| 00:00:01 |  
  97. |*  5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |  
  98. |*  6 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |  
  99. ----------------------------------------------------------------------------------------  
  100.   
  101. Predicate Information (identified by operation id):  
  102. ---------------------------------------------------  
  103.   
  104.    5 - filter("D"."LOC"='CHICAGO')  
  105.    6 - access("D"."DEPTNO"="V"."DEPTNO")  
  106.   
  107. SQL> select /*+ merge(v)*/ d.loc,v.avg_sal  
  108.   2  from dept d,( select deptno,avg(sal) avg_sal,min(sal) min_sal,max(sal) max_sal  
  109.   3   from emp group by deptno) v  
  110.   4  where d.deptno=v.deptno and d.loc='CHICAGO';  
  111.   
  112. 执行计划  
  113. ----------------------------------------------------------  
  114. Plan hash value: 2941989041  
  115.   
  116. ----------------------------------------------------------------------------------------  
  117. | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
  118. ----------------------------------------------------------------------------------------  
  119. |   0 | SELECT STATEMENT             |         |     1 |    37 |    23  (83)| 00:00:01 |  
  120. |   1 |  NESTED LOOPS                |         |     1 |    37 |    23  (83)| 00:00:01 |  
  121. |   2 |   VIEW                       |         |     3 |    78 |    22  (87)| 00:00:01 |  
  122. |   3 |    HASH GROUP BY             |         |     3 |    21 |    22  (87)| 00:00:01 |  
  123. |   4 |     TABLE ACCESS FULL        | EMP     |   100K|   683K|     8  (63)| 00:00:01 |  
  124. |*  5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |  
  125. |*  6 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |  
  126. ----------------------------------------------------------------------------------------  
  127.   
  128. Predicate Information (identified by operation id):  
  129. ---------------------------------------------------  
  130.   
  131.    5 - filter("D"."LOC"='CHICAGO')  
  132.    6 - access("D"."DEPTNO"="V"."DEPTNO")  
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 merging
erging


SQL> 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] view plain copy print ?
  1. SQL> create index ind_emp_deptno on emp(deptno);  
  2.   
  3. 索引已创建。  
  4.   
  5. SQL> set autot traceonly explain  
  6. SQL> select d.loc,v.avg_sal  
  7.   2  from dept d,( select e.deptno,avg(e.sal) avg_sal,min(e.sal) min_sal,max(e.sal) max_sal  
  8.   3   from emp e group by e.deptno) v  
  9.   4  where d.deptno=v.deptno and v.deptno=20;  
  10.   
  11. 执行计划  
  12. ----------------------------------------------------------  
  13. Plan hash value: 3947471307  
  14.   
  15. ------------------------------------------------------------------------------------------------  
  16. | Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |  
  17. ------------------------------------------------------------------------------------------------  
  18. |   0 | SELECT STATEMENT              |                |     1 |    27 |     2   (0)| 00:00:01 |  
  19. |   1 |  HASH GROUP BY                |                |     1 |    27 |     2   (0)| 00:00:01 |  
  20. |   2 |   NESTED LOOPS                |                |     5 |   135 |     2   (0)| 00:00:01 |  
  21. |   3 |    TABLE ACCESS BY INDEX ROWID| DEPT           |     1 |    20 |     1   (0)| 00:00:01 |  
  22. |*  4 |     INDEX UNIQUE SCAN         | PK_DEPT        |     1 |       |     0   (0)| 00:00:01 |  
  23. |   5 |    TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    35 |     1   (0)| 00:00:01 |  
  24. |*  6 |     INDEX RANGE SCAN          | IND_EMP_DEPTNO |     5 |       |     0   (0)| 00:00:01 |  
  25. ------------------------------------------------------------------------------------------------  
  26.   
  27. Predicate Information (identified by operation id):  
  28. ---------------------------------------------------  
  29.   
  30.    4 - access("D"."DEPTNO"=20)  
  31.    6 - access("E"."DEPTNO"=20)  
  32.   
  33. SQL> select /*+ no_merge(v) */d.loc,v.avg_sal  
  34.   2  from dept d,( select e.deptno,avg(e.sal) avg_sal,min(e.sal) min_sal,max(e.sal) max_sal  
  35.   3   from emp e group by e.deptno) v  
  36.   4  where d.deptno=v.deptno and v.deptno=20;  
  37.   
  38. 执行计划  
  39. ----------------------------------------------------------  
  40. Plan hash value: 87641604  
  41.   
  42. -------------------------------------------------------------------------------------------------  
  43. | Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |  
  44. -------------------------------------------------------------------------------------------------  
  45. |   0 | SELECT STATEMENT               |                |     1 |    37 |     3   (0)| 00:00:01 |  
  46. |   1 |  NESTED LOOPS                  |                |     1 |    37 |     3   (0)| 00:00:01 |  
  47. |   2 |   TABLE ACCESS BY INDEX ROWID  | DEPT           |     1 |    11 |     1   (0)| 00:00:01 |  
  48. |*  3 |    INDEX UNIQUE SCAN           | PK_DEPT        |     1 |       |     0   (0)| 00:00:01 |  
  49. |   4 |   VIEW                         |                |     1 |    26 |     2   (0)| 00:00:01 |  
  50. |   5 |    SORT GROUP BY               |                |     1 |     7 |     2   (0)| 00:00:01 |  
  51. |   6 |     TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    35 |     2   (0)| 00:00:01 |  
  52. |*  7 |      INDEX RANGE SCAN          | IND_EMP_DEPTNO |     5 |       |     1   (0)| 00:00:01 |  
  53. -------------------------------------------------------------------------------------------------  
  54.   
  55. Predicate Information (identified by operation id):  
  56. ---------------------------------------------------  
  57.   
  58.    3 - access("D"."DEPTNO"=20)  
  59.    7 - access("E"."DEPTNO"=20)     --这里将谓词v.deptno=20推入到了基表e(emp)，从而能够使用索引访问。  
SQL> create index ind_emp_deptno on emp(deptno);

索引已创建。

SQL> set autot traceonly explain
SQL> 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] view plain copy print ?
  1. SQL> create index ind_emp_dept_no on emp(deptno);  
  2.   
  3. 索引已创建。  
  4.   
  5. SQL> set autot traceonly explain  
  6. SQL> select /*+ no_merge(v)  */  d.loc,v.ename   
  7.   2  from dept d,( select  * from emp) v  
  8.   3  where d.deptno=v.deptno(+) ;  
  9.   
  10. 执行计划  
  11. ----------------------------------------------------------  
  12. Plan hash value: 2615629228  
  13.   
  14. ----------------------------------------------------------------------------  
  15. | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  16. ----------------------------------------------------------------------------  
  17. |   0 | SELECT STATEMENT    |      |    14 |   434 |     7  (15)| 00:00:01 |  
  18. |*  1 |  HASH JOIN OUTER    |      |    14 |   434 |     7  (15)| 00:00:01 |  
  19. |   2 |   TABLE ACCESS FULL | DEPT |     4 |    44 |     3   (0)| 00:00:01 |  
  20. |   3 |   VIEW              |      |    14 |   280 |     3   (0)| 00:00:01 |  
  21. |   4 |    TABLE ACCESS FULL| EMP  |    14 |   126 |     3   (0)| 00:00:01 |  
  22. ----------------------------------------------------------------------------  
  23.   
  24. Predicate Information (identified by operation id):  
  25. ---------------------------------------------------  
  26.   
  27.    1 - access("D"."DEPTNO"="V"."DEPTNO"(+))  
  28.   
  29. --使用no_merge保证视图不能合并，push_pred 使join谓词推入  
  30. SQL> select /*+ no_merge(v) push_pred (v) */  d.loc,v.ename  
  31.   2  from dept d,( select  * from emp) v  
  32.   3  where d.deptno=v.deptno(+) ;  
  33.   
  34. 执行计划  
  35. ----------------------------------------------------------  
  36. Plan hash value: 114584144  
  37.   
  38. -------------------------------------------------------------------------------------------------  
  39. | Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |  
  40. -------------------------------------------------------------------------------------------------  
  41. |   0 | SELECT STATEMENT              |                 |    14 |   350 |    11   (0)| 00:00:01 |  
  42. |   1 |  NESTED LOOPS OUTER           |                 |    14 |   350 |    11   (0)| 00:00:01 |  
  43. |   2 |   TABLE ACCESS FULL           | DEPT            |     4 |    56 |     3   (0)| 00:00:01 |  
  44. |   3 |   VIEW PUSHED PREDICATE       |                 |     1 |    11 |     2   (0)| 00:00:01 |  
  45. |   4 |    TABLE ACCESS BY INDEX ROWID| EMP             |     5 |    60 |     2   (0)| 00:00:01 |  
  46. |*  5 |     INDEX RANGE SCAN          | IND_EMP_DEPT_NO |     5 |       |     1   (0)| 00:00:01 |  
  47. -------------------------------------------------------------------------------------------------  
  48.   
  49. Predicate Information (identified by operation id):  
  50. ---------------------------------------------------  
  51.   
  52.    5 - access("EMP"."DEPTNO"="D"."DEPTNO")   --join谓词已推入到基表，并访问基表的索引。  
  53.   
  54. SQL> select /*+ no_merge(v) no_push_pred (v) */  d.loc,v.ename  
  55.   2  from dept d,( select  * from emp) v  
  56.   3  where d.deptno=v.deptno(+) ;  
  57.   
  58. 执行计划  
  59. ----------------------------------------------------------  
  60. Plan hash value: 2615629228  
  61.   
  62. ----------------------------------------------------------------------------  
  63. | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  64. ----------------------------------------------------------------------------  
  65. |   0 | SELECT STATEMENT    |      |    14 |   434 |     7  (15)| 00:00:01 |  
  66. |*  1 |  HASH JOIN OUTER    |      |    14 |   434 |     7  (15)| 00:00:01 |  
  67. |   2 |   TABLE ACCESS FULL | DEPT |     4 |    44 |     3   (0)| 00:00:01 |  
  68. |   3 |   VIEW              |      |    14 |   280 |     3   (0)| 00:00:01 |  
  69. |   4 |    TABLE ACCESS FULL| EMP  |    14 |   126 |     3   (0)| 00:00:01 |  
  70. ----------------------------------------------------------------------------  
  71.   
  72. Predicate Information (identified by operation id):  
  73. ---------------------------------------------------  
  74.   
  75.    1 - access("D"."DEPTNO"="V"."DEPTNO"(+)) 
Flexes
关注
专栏目录
oracle多个视图整合,SQL关联方式-视图合并 （View Merging）
weixin_30670859的博客
04-06 1956
周末时，近日有网友朋友向我提问，“什么是视图合并？多次看到这个名词，但是每次都是扎入到执行计划中去分析了，但是对这个概念和功能完全不清楚，干啥用的？” 对于这个概念，趁赶着这个问题，自己也好好复习一下。一.什么是视图合并(View Merging)？我们通过一个SQL案例来说明，现有如下SQL：SQL1:select emp1.empno, emp1.ename, emp1.commfrom em...
ORACLE SQL调优之执行计划与隐藏参数_complex_view_merging
clm20482的博客
05-24 462
最近，赤峰windows 版的11.2.0.3的oracle数据库出现一条sql语句执行非常慢，需要1天的时间还出不来，但是观察服务器的IO和CPU都是很空闲，并且将 该sql语句涉及的对象全部导出，然后分别导入其他机...
浙大团队发表 3D 视图合成新方法，效果远超 NeRF、NV
HyperAI超神经
01-07 4377
By 超神经内容提要：只需几个不同视角的视频，就能把整个人体形象 360° 无死角地构建起来，不得不说，AI 的脑补能力越来越强大。这样的工具，今后将可能为影视业、体育节目呈现等带来新的...
海量数据库解决方案之视图合并应用原理
congxiebiao5773的博客
06-14 181
为了让视图或嵌套视图按照最优化的方式执行，可能需要对其进行必要的转换，下面我们就针对具体的转换方法予以详细说明。虽然这里的内容有些费解，但是它在帮助我们深刻理解执行计划方面有着非常大的作用。在这里我们将把视图查询分为两类：视...
hint oracle group by,ORACLE的视图合并HINT
weixin_33914982的博客
04-05 608
第一章ORACLE的视图合并介绍1.1视图合并首先介绍一下ORACLE的视图合并概念：视图合并(View Merging)是优化器处理带视图的目标SQL的一种优化手段，它是指优化器不再将目标SQL中视图的定义语句当做一个独立的处理单元来执行，而是会将其拆开，把其定义SQL语句的基表拿出来与外表合并，合并后的SQL将只剩下外部查询的表和原视图中的基表，不再有视图出现。视图合并的好处是让优化器有更多的...
一种实化视图的合并算法
05-08
3. **目标**: 本文旨在提出一种新的方法——视图合并(View Merging)，以进一步减少可能响应查询的候选视图数量，并提高查询性能。 #### 三、视图合并算法概述 ##### 3.1 视图合并定义 视图合并是指将具有相似属性...
视图合并和谓词推入
DATA
09-26 2308
------------------------------《海量数据库解决方案》读书笔记-----视图合并（View Merging)------------------------ 一、视图合并和谓词推入 1.视图查询分为两类： 视图查询和读取查询          视图查询：创建视图时所使用的select语句，存储在数据字典中          读取查询：执行该视图（或嵌套视图）的S
简单视图合并
weixin_30781631的博客
05-20 142
1　启发式查询转换 所有的启发式查询转换都是基于一套优化器内建的规则。在查询转换阶段，转换器会逐个针对这些规则对查询进行检查，确定其是否满足转换规则，一旦满足，转换器就对其进行转换。 1.1　简单视图合并 我们知道，视图（View）的实质就是一条查询语句。在解析阶段，语句中的每个视图都会被展开至一个查询块中。如果未做视图合并，优化器则会单独分析每个视图，并为定义视图的查询语句生成一个视图子计...
测试 达梦 complex_view_merging
cujiaowu9180的博客
01-14 188
select object_id from test_obj1 a left join(select sum(DATA_OBJECT_ID) sm,b.object_id from test_obj1 b group by b...
oracle 组合视图,查询转换之视图合并
weixin_29093017的博客
04-08 382
视图合并常常发生在当外部查询块的谓语出现下列项的时候能够在另一个查询块的索引中使用的列能够在另一个查询块的分区截断中使用的列在一个连接视图中能够返回限制行数SQL> set autotrace traceonlyexp stat;SQL> select * from t1 ,(select * from t2) v where t1.object_id=v.object_id an...
oracle执行计划px,ORACLE SQL调优之执行计划与隐藏参数_complex_view_merging
weixin_31495827的博客
04-04 475
最近，赤峰windows 版的11.2.0.3的oracle数据库出现一条sql语句执行非常慢，需要1天的时间还出不来，但是观察服务器的IO和CPU都是很空闲，并且将该sql语句涉及的对象全部导出，然后分别导入其他机器oracle数据库与出现性能问题的数据库，发现其他机器数据库执行非常快而问题数据库依旧很慢。尝试收集方案及表的统计信息后，再次执行sql语句还是没有改善，在问题处理的过程中，注意到一...
oracle 将表转为视图,oracle的查询转化之简单视图合并及外连接视图合并
weixin_29809927的博客
04-02 422
参考：https://blogs.oracle.com/optimizer/entry/optimizer_transformations_view_merging_part_1本文演示所用的表均来自Oracle sample schemas的HR用户。视图合并：优化器不再将目标sql中视图的定义当做一个独立的处理单元来单独执行，而是将其拆开，把其定义SQL中的基表拿出来与外部查询中的表合并。这样...
_complex_view_merging对执行计划的影响
12-09 442
以下是在10.2.0.1版本下测试，以下实验来着于Jonathan Lewis 《CBO》。1、创建测试表SQL>create table t1 ( id_par numb...
View Merging
cr13324309138的博客
06-20 176
1.定义： The purpose of view merging is to reduce the number of query blocks due to views and inline views ...
oracle的complex,Oracle 10g的隐含参数_complex_view_merging引发的性能问题
weixin_39873741的博客
04-05 335
今天在Oracle 10g上碰到一个奇怪的问题，有一条sql在数据库1上很快，在数据库2上很慢，数据库2的数据是从数据库1上导的，数据量差不多。在数据库1上执行0.01s。SQL> SELECT A.*,2 B.INCREASE_ID,3 B.TRANSACTION_ID,4 B.LINK_CARD_ID,5 B.VALIDAT...
oracle 10g的隐含参数_complex_view_merging引发的性能问题
关注系统性能调优
03-03 2955
今天在oracle10g上碰到一个奇怪的问题，有一条sql在数据库1上很快，在数据库2上很慢，数据库2的数据是从数据库1上导的，数据量差不多。 在数据库1上执行0.01s。 SQL> SELECT A.*,   2         B.INCREASE_ID,   3         B.TRANSACTION_ID,   4         B.LINK_CARD_ID,   5
12.查询转换-视图合并技术
最新发布
weixin_43346403的博客
01-24 487
如果不做视图合并最优:/*+no_merge(view_1)*/视图合并并不总是是最优的执行计划，要视图具体情况来看。如果视图合并最优:/*+merge(view_1)*/2.简单视图合并的例子。
ORA-01732: 此视图的数据操纵操作非法 解决方案 oracle10g
热门推荐
dacong的专栏
12-17 4万+
ORA-01732: 此视图的数据操纵操作非法 解决方案， ORA-01732: data manipulation operation not legal on this view Oracle 10g的隐含参数_complex_view_merging引发的性能问题
idea取消合并merging
09-17
当决定取消合并时，这可能是因为多种原因而做出的决策。首先，合并可能会导致资源和财务方面的风险。合并需要投入大量的时间、精力和资金，但结果并不总是成功。可能面临到合并方的抵抗、员工不适应和不稳定的市场...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值