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操作可能會延遲到所有連線完成之後。