Oracle查询转换（三）外连接视图合并

最新推荐文章于 2021-04-06 13:39:07 发布

cuishan5183

最新推荐文章于 2021-04-06 13:39:07 发布

阅读量265

点赞数

文章标签：数据库 java

本文介绍了数据库优化器如何处理包含外连接的视图，并详细解释了在外连接视图合并过程中语义一致性的重要性。通过实际案例展示了视图作为驱动表或被驱动表时，优化器的不同行为。

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

视图的定义及分类参考：http://blog.itpub.net/28536251/viewspace-1871039/
如果视图定义中包含外连接或者视图与目标SQL中其他表做外连接，优化器会评估视图合并后的语义是否与目标SQL一致，如果一致就进行视图合并，也就是外连接视图合并。那么什么情况下语义一致呢，有以下两种情况：
● 视图的基表只有一个表。
● 视图作为外连接的驱动表。

SQL> select employee_id,last_name,salary,department_name from employees_50_vw e,departments d where e.department_id=d.department_id(+);

45 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2408298835

SQL> select d.department_name,e.sal_sum from employees_vw e,departments d where e.department_id=d.department_id(+) and department_name='IT';

Execution Plan
----------------------------------------------------------
Plan hash value: 3808327043

---------------------------------------------------------------------------------------------------
| Id | Operation                     | Name              | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     8 |   224 |     5 (20)| 00:00:01 |
|   1 | HASH GROUP BY                |                   |     8 |   224 |     5 (20)| 00:00:01 |
|   2 |   NESTED LOOPS                |                   |    10 |   280 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS               |                   |    10 |   280 |     4   (0)| 00:00:01 |
|* 4 |     TABLE ACCESS FULL         | DEPARTMENTS       |     1 |    21 |     3   (0)| 00:00:01 |
|* 5 |     INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |    70 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

以上视图employees_50_vw和employees_vw的基表都只有一个表，所以不论是作为驱动表还是被驱动表，都可以进行外连接视图合并。

创建一个有外连接的视图。
SQL> create view emp_dept_vw as select employee_id,last_name,department_name,location_id from employees e,departments d where e.department_id=d.department_id(+);

View created.

SQL> select last_name,department_name,city from emp_dept_vw e,locations l where e.location_id=l.location_id(+);

107 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 807041693

--------------------------------------------------------------------------------------------
| Id | Operation               | Name             | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |   107 | 4494 |     8   (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER        |                  |   107 | 4494 |     8   (0)| 00:00:01 |
|* 2 |   HASH JOIN OUTER       |                  |   107 | 3210 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL    | EMPLOYEES        |   107 | 1177 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL    | DEPARTMENTS      |    27 |   513 |     3   (0)| 00:00:01 |
|   5 |   VIEW                  | index$_join$_002 |    23 |   276 |     2   (0)| 00:00:01 |
|* 6 |    HASH JOIN            |                  |       |       |            |          |
|   7 |     INDEX FAST FULL SCAN| LOC_CITY_IX      |    23 |   276 |     1   (0)| 00:00:01 |
|   8 |     INDEX FAST FULL SCAN| LOC_ID_PK        |    23 |   276 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

此处视图emp_dept_vw作为驱动表，在执行计划中没有视图emp_dept_vw的名字，表示优化器已经做了视图合并。

SQL> select last_name,department_name,city from emp_dept_vw e,locations l where e.location_id(+)=l.location_id;

122 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3490089952

--------------------------------------------------------------------------------------------
| Id | Operation               | Name             | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |   107 | 5992 |     8   (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER        |                  |   107 | 5992 |     8   (0)| 00:00:01 |
|   2 |   VIEW                  | index$_join$_002 |    23 |   276 |     2   (0)| 00:00:01 |
|* 3 |    HASH JOIN            |                  |       |       |            |          |
|   4 |     INDEX FAST FULL SCAN| LOC_CITY_IX      |    23 |   276 |     1   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| LOC_ID_PK        |    23 |   276 |     1   (0)| 00:00:01 |
|   6 |   VIEW                  | EMP_DEPT_VW      |   107 | 4708 |     6   (0)| 00:00:01 |
|* 7 |    HASH JOIN OUTER      |                  |   107 | 3210 |     6   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL   | EMPLOYEES        |   107 | 1177 |     3   (0)| 00:00:01 |
|   9 |     TABLE ACCESS FULL   | DEPARTMENTS      |    27 |   513 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

此处视图emp_dept_vw作为被驱动表，在执行计划中有视图emp_dept_vw的名字，表示优化器没有对视图进行视图合并。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28536251/viewspace-2140778/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28536251/viewspace-2140778/