在檢視合併中，最佳化器將表示檢視的查詢塊合併到包含該檢視的查詢塊中。

對於某些簡單的檢視，合併總是導致更好的計劃，最佳化器會自動合併檢視，而不考慮成本。否則，最佳化器將使用成本進行確定。最佳化器可能出於許多原因選擇不合並檢視，包括成本或有效性限制

如果OPTIMIZER_SECURE_VIEW_MERGING是true（預設），則 Oracle 資料庫執行檢查以確保檢視合併和謂詞推送不會違反檢視建立者的安全意圖。要為特定檢視禁用這些額外的安全檢查，您可以向MERGE VIEW使用者授予此檢視的許可權。要對特定使用者的所有檢視禁用附加安全檢查，您可以MERGE ANY VIEW向該使用者授予許可權。

#### 檢視合併中的查詢塊
最佳化器透過單獨的查詢塊表示每個巢狀的子查詢或未合併的檢視。

資料庫自下而上分別最佳化查詢塊。因此，資料庫首先最佳化最內層的查詢塊，為其生成部分計劃，然後再為外層的查詢塊生成計劃，代表整個查詢。

解析器將查詢中引用的每個檢視擴充套件為單獨的查詢塊。塊本質上代表檢視定義，因此代表檢視的結果。最佳化器的一種選擇是單獨分析檢視查詢塊，生成檢視子計劃，然後使用檢視子計劃處理查詢的其餘部分以生成整體執行計劃。然而，這種技術可能會導致一個次優的執行計劃，因為檢視是單獨最佳化的。

#### 簡單的檢視合併

檢視對於簡單的檢視合併可能無效，因為：
- 該檢視包含未包含在select-project-join檢視重點 構造，包括（group by/distinct/外連線/model/connect by/設定運算子/聚合）
- 檢視顯示在一個右側半連線或返連線
- 外部查詢塊包含PL/SQL函式
- 檢視參與外部聯結，並且不滿足確定是否可以合併檢視的幾個附加有效性要求之一

示例：
``` sql
-以下查詢將hr.employees表與dept_locs_v檢視連線起來，該檢視返回每個部門的街道地址。dept_locs_v是departments和locations表的連線。
SQL> set lines 200
SQL> set pages 999
SQL> explain plan for SELECT e.first_name, e.last_name, dept_locs_v.street_address,
       dept_locs_v.postal_code
FROM   employees e,
      ( SELECT d.department_id, d.department_name, 
               l.street_address, l.postal_code
        FROM   departments d, locations l
    2    3    4    5    6    7        WHERE  d.location_id = l.location_id ) dept_locs_v
WHERE  dept_locs_v.department_id = e.department_id
AND    e.last_name = 'Smith';  8    9

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1591417065

--------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name		 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 		 |   972 | 46656 |    18   (6)| 00:00:01 |
|*  1 |  HASH JOIN		      | 		 |   972 | 46656 |    18   (6)| 00:00:01 |
|   2 |   MERGE JOIN		      | 		 |    27 |  1026 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| LOCATIONS	 |    23 |   713 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN	      | LOC_ID_PK	 |    23 |	 |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN		      | 		 |    27 |   189 |     3  (34)| 00:00:01 |
|   6 |     VIEW		      | index$_join$_003 |    27 |   189 |     2   (0)| 00:00:01 |
|*  7 |      HASH JOIN		      | 		 |	 |	 |	      | 	 |
|   8 |       INDEX FAST FULL SCAN    | DEPT_ID_PK	 |    27 |   189 |     1   (0)| 00:00:01 |
|   9 |       INDEX FAST FULL SCAN    | DEPT_LOCATION_IX |    27 |   189 |     1   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES	 |   972 |  9720 |    13   (0)| 00:00:01 |
|* 11 |    INDEX RANGE SCAN	      | EMP_NAME_IX	 |   972 |	 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
       filter("D"."LOCATION_ID"="L"."LOCATION_ID")
   7 - access(ROWID=ROWID)
  11 - access("E"."LAST_NAME"='Smith')

27 rows selected.
```
資料庫可以透過加入departments並locations生成檢視的行，然後將此結果加入到來執行前面的查詢employees。因為查詢包含檢視dept_locs_v，並且該檢視包含兩個表，所以最佳化器必須使用以下連線順序之一：
- employees, dept_locs_v( departments, locations)
- employees, dept_locs_v( locations, departments)
- dept_locs_v( departments, locations),employees
- dept_locs_v( locations, departments),employees

#### 複雜的檢視合併
在檢視合併中，最佳化器合併檢視包含GROUP BY和DISTINCT檢視。與簡單的檢視合併一樣，複雜的合併使最佳化器能夠考慮額外的連線順序和訪問路徑。

除成本之外，最佳化器可能無法執行復雜的檢視合併，原因如下：
- 外部查詢表沒有 rowid 或唯一列。
- 該檢視出現在CONNECT BY查詢塊中。
- 該檢視包含GROUPING SETS，ROLLUP或PIVOT條款。
- 檢視或外部查詢塊包含MODEL子句。

以下檢視使用GROUP BY子句：
``` sql
CREATE VIEW cust_prod_totals_v AS
SELECT SUM(s.quantity_sold) total, s.cust_id, s.prod_id
FROM   sales s
GROUP BY s.cust_id, s.prod_id;
```

以下查詢查詢來自美國且購買了至少 100 件毛皮邊飾毛衣的所有客戶：
``` sql
SELECT c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
FROM   customers c, products p, cust_prod_totals_v
WHERE  c.country_id = 52790
AND    c.cust_id = cust_prod_totals_v.cust_id
AND    cust_prod_totals_v.total > 100
AND    cust_prod_totals_v.prod_id = p.prod_id
AND    p.prod_name = 'T3 Faux Fur-Trimmed Sweater';
```
該cust_prod_totals_v檢視符合複雜檢視合併的條件。合併後查詢如下：
```sql
SELECT c.cust_id, cust_first_name, cust_last_name, cust_email
FROM   customers c, products p, sales s
WHERE  c.country_id = 52790
AND    c.cust_id = s.cust_id
AND    s.prod_id = p.prod_id
AND    p.prod_name = 'T3 Faux Fur-Trimmed Sweater'
GROUP BY s.cust_id, s.prod_id, p.rowid, c.rowid, c.cust_email, c.cust_last_name, 
         c.cust_first_name, c.cust_id
HAVING SUM(s.quantity_sold) > 100;
```
轉換後的查詢效果更好，因此最佳化器選擇合併檢視。在未轉換的查詢中，GROUP BY運算子應用於sales檢視中的整個表。在轉換後的查詢中，連線products並customers過濾掉表中的大部分行sales，因此GROUP BY操作成本較低。join比較貴是因為sales表沒有縮小，但也不是貴很多，因為GROUP BY在原查詢中操作並沒有把行集的大小縮小很多。如果上述任何特徵發生變化，合併檢視可能不再是更低的成本。最終方案如下：
``` sql
--------------------------------------------------------
| Id  | Operation             | Name      | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT      |           |  2101  (18)|
|*  1 |  FILTER               |           |            |
|   2 |   HASH GROUP BY       |           |  2101  (18)|
|*  3 |    HASH JOIN          |           |  2099  (18)|
|*  4 |     HASH JOIN         |           |  1801  (19)|
|*  5 |      TABLE ACCESS FULL| PRODUCTS  |    96   (5)|
|   6 |      TABLE ACCESS FULL| SALES     |  1620  (15)|
|*  7 |     TABLE ACCESS FULL | CUSTOMERS |   296  (11)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("QUANTITY_SOLD")>100)
3 - access("C"."CUST_ID"="CUST_ID")
4 - access("PROD_ID"="P"."PROD_ID")
5 - filter("P"."PROD_NAME"='T3 Faux Fur-Trimmed Sweater')
7 - filter("C"."COUNTRY_ID"='US')
```

#### 使用distinct的複雜檢視連線
cust_prod_v檢視的以下查詢使用DISTINCT運算子：
``` sql
SELECT c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
FROM   customers c, products p,
       ( SELECT DISTINCT s.cust_id, s.prod_id
         FROM   sales s) cust_prod_v
WHERE  c.country_id = 52790
AND    c.cust_id = cust_prod_v.cust_id
AND    cust_prod_v.prod_id = p.prod_id
AND    p.prod_name = 'T3 Faux Fur-Trimmed Sweater';
```
在確定檢視合併產生一個低成本的計劃後，最佳化器將查詢重寫為這個等效的查詢：
``` sql
SELECT nwvw.cust_id, nwvw.cust_first_name, nwvw.cust_last_name, nwvw.cust_email
FROM   ( SELECT DISTINCT(c.rowid), p.rowid, s.prod_id, s.cust_id,
                c.cust_first_name, c.cust_last_name, c.cust_email
         FROM   customers c, products p, sales s
         WHERE  c.country_id = 52790
         AND    c.cust_id = s.cust_id
         AND    s.prod_id = p.prod_id
         AND    p.prod_name = 'T3 Faux Fur-Trimmed Sweater' ) nwvw;
		 
--執行計劃如下
-------------------------------------------
| Id  | Operation             | Name      |
-------------------------------------------
|   0 | SELECT STATEMENT      |           |
|   1 |  VIEW                 | VM_NWVW_1 |
|   2 |   HASH UNIQUE         |           |
|*  3 |    HASH JOIN          |           |
|*  4 |     HASH JOIN         |           |
|*  5 |      TABLE ACCESS FULL| PRODUCTS  |
|   6 |      TABLE ACCESS FULL| SALES     |
|*  7 |     TABLE ACCESS FULL | CUSTOMERS |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
  3 - access("C"."CUST_ID"="S"."CUST_ID")
  4 - access("S"."PROD_ID"="P"."PROD_ID")
  5 - filter("P"."PROD_NAME"='T3 Faux Fur-Trimmed Sweater')
  7 - filter("C"."COUNTRY_ID"='US')
```

如果不檢視合併，那整個檢視就會當成一整塊，在sql執行的時候，這個檢視就是一個結果集，然後再去和另一個結果集關聯。如果合併了的話，那這個檢視就會被拆散，檢視裡面的關聯就會分開run，並不是每次檢視合併都是高效的。

在執行計劃中，如果看到view關鍵字（一般情況下），說明檢視沒有展開，也就是檢視沒有合併，如果本來sql中有內聯檢視或者檢視，但執行計劃中沒有看到view關鍵字，那這個sql就進行了檢視合併。

此外還需要注意的是，如果sql中的內聯檢視有聚合等操作，比如rownum，start with，connect by，union，union all，rollup，cube等，這種內聯檢視就不能展開，因為內聯檢視被固化了，碰到這種情況就需要注意，如果內聯檢視中結果集很大，那sql估計就要改寫了，因為這個內聯檢視會最先執行

【SQL】Oracle查詢轉換之檢視合併

相關文章