Oracle查詢轉換（一）簡單檢視合併

 

檢視的定義及分類參考：http://blog.itpub.net/28536251/viewspace-1871039/
如果目標SQL中包含了檢視，且該檢視為一個簡單檢視，則最佳化器會將檢視拆開，直接使用檢視的基表來生成執行計劃，故在執行計劃中將看不到檢視的名字了。

 

建立一個簡單檢視：
SQL> create view employees_50_vw as select employee_id,last_name,job_id,salary,commission_pct,department_id from employees where department_id=50;

View created.

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: 1492013603

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

 

可以看到執行計劃中沒有檢視employees_50_vw的名字，表示最佳化器已經做了檢視合併。可以使用NO_MERGE Hint來禁止最佳化器進行檢視合併：

 

SQL> select /*+ no_merge(e) */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: 3057875378

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |    45 |  3105 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |                 |    45 |  3105 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS     |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK      |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |                 |    45 |  2385 |     4  (25)| 00:00:01 |
|   5 |    VIEW                      | EMPLOYEES_50_VW |    45 |  2385 |     3   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL        | EMPLOYEES       |    45 |   855 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

 

可以看到執行計劃中出現了檢視employees_50_vw的名字，則表示最佳化器沒有對檢視進行檢視合併。