Oracle查詢轉換（三）外連線檢視合併

檢視的定義及分類參考：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

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    45 |  1575 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER             |             |    45 |  1575 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | EMPLOYEES   |    45 |   855 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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的名字，表示最佳化器沒有對檢視進行檢視合併。