Oracle查詢轉換（二）複雜檢視合併

檢視的定義及分類參考：http://blog.itpub.net/28536251/viewspace-1871039/
如果目標SQL中包含了檢視，且該檢視為一個複雜檢視，則最佳化器會評估檢視合併的成本，如果成本低於原SQL的成本，才會做檢視合併。

 

建立一個複雜檢視：
SQL> create view employees_vw as select department_id,sum(salary) sal_sum from employees group by department_id;

View created.

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_vw的名字，且將檢視中的group by放到了兩個表連線之後執行，表示最佳化器已經做了檢視合併。可以使用NO_MERGE Hint來禁止最佳化器進行檢視合併：

 

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

-----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |     1 |    29 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                   |                   |     1 |    29 |     5   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL             | DEPARTMENTS       |     1 |    16 |     3   (0)| 00:00:01 |
|   3 |   VIEW PUSHED PREDICATE         | EMPLOYEES_VW      |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |    FILTER                       |                   |       |       |            |          |
|   5 |     SORT AGGREGATE              |                   |     1 |     7 |            |          |
|   6 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |    70 |     2   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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