视图合并（View Merging）

最新推荐文章于 2024-03-07 11:28:46 发布

原创

最新推荐文章于 2024-03-07 11:28:46 发布 · 8.2k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#access #loops #table #nested #merge #join

Oracle在查询涉及视图时会尝试进行视图合并以优化执行计划。但某些操作如集合运算、聚合函数、ROWNUM等阻止视图合并。视图合并受_complex_view_merging参数影响，当为true时，包含GROUP BY或DISTINCT的复杂视图可能合并。如果不能合并，查询转换器会执行谓词推入，将条件推入视图的基表以利用索引。此外，特定类型的视图如UNION ALL和JOIN谓词推入视图在无法合并时也可能受益于这种转换。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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