Oracle 12CR2查詢轉換之視圖合并

這里的測試數據庫版本為12.2.0.1,在視圖合并中，優化器代表視圖的查詢塊到包含視國的查詢塊中。視圖合并通過讓優化器考慮額外的連接順序，訪問方法與其它轉換來提高性能。例如，在一個視圖被合并后并且在一個查詢塊中有多個表，內置在視圖中的一個表可以允許優化器使用連接消除來刪除視圖外部的一個表。

對于特定的簡單視圖執行視圖合并總是會生成更好的執行計劃，優化器自動合并視圖不會考慮成本。另外的優化器使用成本來進行決定。由于許多原因，包括成本或有效的限制優化器可能選擇不執行視圖合并。

如果optimizer_secure_view_merging設置為true(缺省值)，那么Oracle數據庫將執行檢查來確保視圖合并與謂詞推送不會違反視圖創建者的安全意圖。為了對特定視圖禁用這些額外的安全檢查，可以給創建視圖的用戶授予merge view權限。為了對特定的用戶的所有視圖禁用額外的安全檢查，可以給用戶授予merge any view權限。

視圖合并之查詢塊
優化器通過單獨的查詢塊來代表每個嵌套子查詢或未合并視圖。數據庫自下而上優化每一個單獨的查詢塊。因此，數據庫首先優化最內部的查詢塊，生成執行計劃的一部分，然后為外部的查詢塊生成執行計劃。解析器展開查詢中的每個視圖成為了一個單獨的查詢塊。查詢塊的本質代表了視圖定義和視國結果。優化器的一個選項是用來分別分析視圖查詢塊，生成一個視圖子執行計劃，然后通過使用視圖子計劃來處理查詢中的剩余部分來生成整個查詢的執行計劃。然而，這種技術因為導致了視圖被分別優化而可能產生次優的執行計劃。視圖合并有些可能會提高性能。

簡單視圖合并
在簡單視圖合并中，優化器合并select-project-join視圖。例如，查詢employees表的一個查詢包含一個子查詢連接departments與locations表。

因為在視圖合并后有額外的連接順序與訪問路徑可用所以簡單視圖合并通常會生成更優化的執行計劃。對于簡單視圖合并不生效，因為:
.視圖包含了不允許出現在select-project-join視圖中的結構，比如:
-group by
-distinct
-Outer join
-MODEL
-connect by
-Set operators
-Aggregation
-

.視圖出現在semijoin或antijoin的右邊
.在select列表中包含子查詢
.外部查詢塊包含PL/SQL函數
.視圖參與外連接并且不滿足視圖被合并的幾個條件中的任何一個

下面的查詢連接hr.employees表與dept_locs_v視圖，查詢將返回每個部門的街道地址。dept_locs_v視圖連接departments與locations表。

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
         WHERE d.location_id = l.location_id) dept_locs_v
 WHERE dept_locs_v.department_id = e.department_id
   AND e.last_name = 'Smith';

數據庫執行上面的查詢通過連接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

連接方法也受到約束。對于以employees表開始的連接順序基于索引的嵌套循環不合適因為對于視圖中的列不存在索引。不使用視圖合并，優化器生成的執行計劃如下:

-----------------------------------------------------------------
| Id | Operation                   | Name        | Cost (%CPU)|
-----------------------------------------------------------------
|  0 | SELECT STATEMENT            |             |      7 (15)|
|* 1 |  HASH JOIN                  |             |      7 (15)|
|  2 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |      2  (0)|
|* 3 |   INDEX RANGE SCAN          | EMP_NAME_IX |      1  (0)|
|  4 |  VIEW                       |             |      5 (20)|
|* 5 |   HASH JOIN                 |             |      5 (20)|
|  6 |    TABLE ACCESS FULL        | LOCATIONS   |      2  (0)|
|  7 |    TABLE ACCESS FULL        | DEPARTMENTS |      2  (0)|
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - access("E"."LAST_NAME"='Smith')
5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

視圖合并將視圖中的表合并到外部查詢塊中，并刪除內部查詢塊。在視圖合并之后，查詢語句如下:

SELECT e.first_name, e.last_name, l.street_address, l.postal_code
FROM employees e, departments d, locations l
WHERE d.location_id = l.location_id
AND d.department_id = e.department_id
AND e.last_name = 'Smith';

因為所有三個表都出現在一個查詢塊，優化器可以從以下6種連接順序中選擇一種:
.employees, departments, locations
.employees, locations, departments
.departments, employees, locations
.departments, locations, employees
.locations, employees, departments
.locations, departments, employees

連接employees與departments表現在可以使用索引，在視圖合并之后，優化器將選擇更有效的執行計劃，使用嵌套循環連接:

-------------------------------------------------------------------
| Id | Operation                      | Name        | Cost (%CPU)|
-------------------------------------------------------------------
|  0 | SELECT STATEMENT               |             |       4 (0)|
|  1 |  NESTED LOOPS                  |             |            |
|  2 |   NESTED LOOPS                 |             |       4 (0)|
|  3 |    NESTED LOOPS                |             |       3 (0)|
|  4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |       2 (0)|
|* 5 |      INDEX RANGE SCAN          | EMP_NAME_IX |       1 (0)|
|  6 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |       1 (0)|
|* 7 |      INDEX UNIQUE SCAN         | DEPT_ID_PK  |       0 (0)|
|* 8 |    INDEX UNIQUE SCAN           | LOC_ID_PK   |       0 (0)|
|  9 |   TABLE ACCESS BY INDEX ROWID  | LOCATIONS   |       1 (0)|
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."LAST_NAME"='Smith')
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

復雜視圖合并
在視圖合并中，優化器合并包含group by與distinct操作的視圖，像簡單視圖合燕一樣，復雜視圖合并能讓優化器考慮額外的連接順序和訪問路徑。

優化器可能會延遲對group by或distinct操作進行評估直到優化器評估完連接之后。延遲這些操作可能提高或損害性能這依賴于數據的特征。如果連接使用過濾，那么延遲這些操作在連接之后可以減少這些操作將要處理的數據集。盡
早評估可以減少后續連接所要處理的數據量或者連接可能增加這些操作所要處理的數據量。優化器使用成本來評估視圖合并并且只有當合并操作之后成本更低才會執行。

除了成本之外，由于以下原因成本可能不會執行復雜視圖合并操作:
.外部查詢表沒有rowid或唯一約束列
.視圖出現在connect by查詢塊中
.視圖包含grouping sets,rollup或pivot子句
.視圖或外部查詢塊包含model子句

包含group by子句的復雜視圖連接下面的查詢使用了group by子句

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;

下面的查詢將找出來那些自United States并且買了至少100件毛衣的所有客戶:

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視圖滿足復雜視圖合并的條件。在合并之后，查詢語句如下:

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操作的成本低。連接成本更高因為sales表沒有被減少，但它的成本并不會高很多，因為group by操作不會在原始查詢中減少太多的行記錄。如果之前的特征發生了改變，合并視圖后的成本將不會減少。最終的執行計劃不包含視圖，如下:

--------------------------------------------------------
| 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操作:

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';

在決定視圖合并后生成的執行計劃成本更低，優化器使用以下等價查詢來重寫原始查詢:

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')

上面的執行計劃即使在視圖合并后還是包含了一個名叫vm_nwvw_1的視圖，也叫projection view。在查詢中的distinct視圖已經合并后出現了projection視圖，或者group by視圖被合并到外部查詢塊并且包含group by,having或聚合操作。在后一種情況下，projection視圖包含了group by,having和原始外部查詢塊中的聚合操作。

在上面的projection視圖中，當優化器合并視圖時，它將distinct操作移動到外部查詢塊中，并且增加了幾個額外列來維護與原始查詢的等價性。在這之后，查詢可以只從外部查詢塊中的select列表中選擇所需要的列。優化器保留了視圖合并的所有好處:一個查詢塊中的所有表，優化器可能會在最終的連接順序中變換它們的順序，并且distinct操作可能會延遲到所有連接完成之后。