Oracle 12CR2查詢(xún)轉(zhuǎn)換之視圖合并

這里的測(cè)試數(shù)據(jù)庫(kù)版本為12.2.0.1,在視圖合并中，優(yōu)化器代表視圖的查詢(xún)塊到包含視國(guó)的查詢(xún)塊中。視圖合并通過(guò)讓優(yōu)化器考慮額外的連接順序，訪(fǎng)問(wèn)方法與其它轉(zhuǎn)換來(lái)提高性能。例如，在一個(gè)視圖被合并后并且在一個(gè)查詢(xún)塊中有多個(gè)表，內(nèi)置在視圖中的一個(gè)表可以允許優(yōu)化器使用連接消除來(lái)刪除視圖外部的一個(gè)表。

對(duì)于特定的簡(jiǎn)單視圖執(zhí)行視圖合并總是會(huì)生成更好的執(zhí)行計(jì)劃，優(yōu)化器自動(dòng)合并視圖不會(huì)考慮成本。另外的優(yōu)化器使用成本來(lái)進(jìn)行決定。由于許多原因，包括成本或有效的限制優(yōu)化器可能選擇不執(zhí)行視圖合并。

如果optimizer_secure_view_merging設(shè)置為true(缺省值)，那么Oracle數(shù)據(jù)庫(kù)將執(zhí)行檢查來(lái)確保視圖合并與謂詞推送不會(huì)違反視圖創(chuàng)建者的安全意圖。為了對(duì)特定視圖禁用這些額外的安全檢查，可以給創(chuàng)建視圖的用戶(hù)授予merge view權(quán)限。為了對(duì)特定的用戶(hù)的所有視圖禁用額外的安全檢查，可以給用戶(hù)授予merge any view權(quán)限。

視圖合并之查詢(xún)塊
優(yōu)化器通過(guò)單獨(dú)的查詢(xún)塊來(lái)代表每個(gè)嵌套子查詢(xún)或未合并視圖。數(shù)據(jù)庫(kù)自下而上優(yōu)化每一個(gè)單獨(dú)的查詢(xún)塊。因此，數(shù)據(jù)庫(kù)首先優(yōu)化最內(nèi)部的查詢(xún)塊，生成執(zhí)行計(jì)劃的一部分，然后為外部的查詢(xún)塊生成執(zhí)行計(jì)劃。解析器展開(kāi)查詢(xún)中的每個(gè)視圖成為了一個(gè)單獨(dú)的查詢(xún)塊。查詢(xún)塊的本質(zhì)代表了視圖定義和視國(guó)結(jié)果。優(yōu)化器的一個(gè)選項(xiàng)是用來(lái)分別分析視圖查詢(xún)塊，生成一個(gè)視圖子執(zhí)行計(jì)劃，然后通過(guò)使用視圖子計(jì)劃來(lái)處理查詢(xún)中的剩余部分來(lái)生成整個(gè)查詢(xún)的執(zhí)行計(jì)劃。然而，這種技術(shù)因?yàn)閷?dǎo)致了視圖被分別優(yōu)化而可能產(chǎn)生次優(yōu)的執(zhí)行計(jì)劃。視圖合并有些可能會(huì)提高性能。

簡(jiǎn)單視圖合并
在簡(jiǎn)單視圖合并中，優(yōu)化器合并select-project-join視圖。例如，查詢(xún)employees表的一個(gè)查詢(xún)包含一個(gè)子查詢(xún)連接departments與locations表。

因?yàn)樵谝晥D合并后有額外的連接順序與訪(fǎng)問(wèn)路徑可用所以簡(jiǎn)單視圖合并通常會(huì)生成更優(yōu)化的執(zhí)行計(jì)劃。對(duì)于簡(jiǎn)單視圖合并不生效，因?yàn)?
.視圖包含了不允許出現(xiàn)在select-project-join視圖中的結(jié)構(gòu)，比如:
-group by
-distinct
-Outer join
-MODEL
-connect by
-Set operators
-Aggregation
-

.視圖出現(xiàn)在semijoin或antijoin的右邊
.在select列表中包含子查詢(xún)
.外部查詢(xún)塊包含PL/SQL函數(shù)
.視圖參與外連接并且不滿(mǎn)足視圖被合并的幾個(gè)條件中的任何一個(gè)

下面的查詢(xún)連接hr.employees表與dept_locs_v視圖，查詢(xún)將返回每個(gè)部門(mén)的街道地址。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';

數(shù)據(jù)庫(kù)執(zhí)行上面的查詢(xún)通過(guò)連接departments與locations表來(lái)為視圖生成行記錄，然后用這個(gè)結(jié)果與employees表連接。因?yàn)椴樵?xún)包含視圖dept_locs_v，并且這個(gè)視圖包含兩個(gè)表，優(yōu)化器必須使用以下一種連接順序:
.employees,dept_locs_v(departments,locations)
.employees,dept_locs_v(locations,departments)
.dept_locs_v(departments,locations),employees
.dept_locs_v(locations,departments),employees

連接方法也受到約束。對(duì)于以employees表開(kāi)始的連接順序基于索引的嵌套循環(huán)不合適因?yàn)閷?duì)于視圖中的列不存在索引。不使用視圖合并，優(yōu)化器生成的執(zhí)行計(jì)劃如下:

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

視圖合并將視圖中的表合并到外部查詢(xún)塊中，并刪除內(nèi)部查詢(xún)塊。在視圖合并之后，查詢(xún)語(yǔ)句如下:

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

因?yàn)樗腥齻€(gè)表都出現(xiàn)在一個(gè)查詢(xún)塊，優(yōu)化器可以從以下6種連接順序中選擇一種:
.employees, departments, locations
.employees, locations, departments
.departments, employees, locations
.departments, locations, employees
.locations, employees, departments
.locations, departments, employees

連接employees與departments表現(xiàn)在可以使用索引，在視圖合并之后，優(yōu)化器將選擇更有效的執(zhí)行計(jì)劃，使用嵌套循環(huán)連接:

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

復(fù)雜視圖合并
在視圖合并中，優(yōu)化器合并包含group by與distinct操作的視圖，像簡(jiǎn)單視圖合燕一樣，復(fù)雜視圖合并能讓優(yōu)化器考慮額外的連接順序和訪(fǎng)問(wèn)路徑。

優(yōu)化器可能會(huì)延遲對(duì)group by或distinct操作進(jìn)行評(píng)估直到優(yōu)化器評(píng)估完連接之后。延遲這些操作可能提高或損害性能這依賴(lài)于數(shù)據(jù)的特征。如果連接使用過(guò)濾，那么延遲這些操作在連接之后可以減少這些操作將要處理的數(shù)據(jù)集。盡
早評(píng)估可以減少后續(xù)連接所要處理的數(shù)據(jù)量或者連接可能增加這些操作所要處理的數(shù)據(jù)量。優(yōu)化器使用成本來(lái)評(píng)估視圖合并并且只有當(dāng)合并操作之后成本更低才會(huì)執(zhí)行。

除了成本之外，由于以下原因成本可能不會(huì)執(zhí)行復(fù)雜視圖合并操作:
.外部查詢(xún)表沒(méi)有rowid或唯一約束列
.視圖出現(xiàn)在connect by查詢(xún)塊中
.視圖包含grouping sets,rollup或pivot子句
.視圖或外部查詢(xún)塊包含model子句

包含group by子句的復(fù)雜視圖連接下面的查詢(xún)使用了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;

下面的查詢(xún)將找出來(lái)那些自United States并且買(mǎi)了至少100件毛衣的所有客戶(hù):

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視圖滿(mǎn)足復(fù)雜視圖合并的條件。在合并之后，查詢(xún)語(yǔ)句如下:

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;

轉(zhuǎn)換后的查詢(xún)成本比沒(méi)轉(zhuǎn)換的查詢(xún)成本要低，因此優(yōu)化器選擇了合并視圖。在沒(méi)有轉(zhuǎn)換的語(yǔ)句中，group by操作是對(duì)視圖中的整個(gè)sales表進(jìn)行操作。在轉(zhuǎn)換后的查詢(xún)中，連接products與customers表過(guò)濾掉了sales表中的大部分?jǐn)?shù)據(jù)，因此group by操作的成本低。連接成本更高因?yàn)閟ales表沒(méi)有被減少，但它的成本并不會(huì)高很多，因?yàn)間roup by操作不會(huì)在原始查詢(xún)中減少太多的行記錄。如果之前的特征發(fā)生了改變，合并視圖后的成本將不會(huì)減少。最終的執(zhí)行計(jì)劃不包含視圖，如下:

--------------------------------------------------------
| 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的復(fù)雜視圖連接
下面的查詢(xún)對(duì)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';

在決定視圖合并后生成的執(zhí)行計(jì)劃成本更低，優(yōu)化器使用以下等價(jià)查詢(xún)來(lái)重寫(xiě)原始查詢(xún):

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;

上面查詢(xún)的執(zhí)行計(jì)劃如下:

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

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

在上面的projection視圖中，當(dāng)優(yōu)化器合并視圖時(shí)，它將distinct操作移動(dòng)到外部查詢(xún)塊中，并且增加了幾個(gè)額外列來(lái)維護(hù)與原始查詢(xún)的等價(jià)性。在這之后，查詢(xún)可以只從外部查詢(xún)塊中的select列表中選擇所需要的列。優(yōu)化器保留了視圖合并的所有好處:一個(gè)查詢(xún)塊中的所有表，優(yōu)化器可能會(huì)在最終的連接順序中變換它們的順序，并且distinct操作可能會(huì)延遲到所有連接完成之后。