使用dbms_advisor優化物化檢視

eric0435發表於2016-09-08

為了快速重新整理與查詢重寫優化物化檢視
dbms_mview有多個過程可以幫助你來建立物化檢視併為了快速重新整理與查詢重寫而進行優化。 explain_mview過程可以告訴你一個物化檢視是否可以快速重新整理或滿足一般的查詢重寫，而 explain_rewrite過程將會告訴你查詢重寫是否會執行。然而也會告訴你如何完成快速重新整理或查詢重寫。

為了能更好的使用物化檢視，dbms_advisor.tune_mview過程將會顯示如何來優化你的create materialized view語句來滿足其它的要求，比如物化檢視日誌與為了快速重新整理與常見查詢重重寫等價關係。dbms_advisor.tune_mview過程分析與處理create materialized view語句並生成兩組輸出：一組是物化檢視的實現與其它撤消建立物化檢視的操作。兩組輸出結果可以通過Oracle檢視來訪問或由SQL Access Advisor建立一個外部指令碼檔案。這些外部指令碼檔案是可以用來執行實現物化檢視的。

使用dbms_advisor.tune_mview過程，你將不再為了建立物化檢視而需要詳細理解物化檢視，因為物化視與它所需要的元件（比如物化檢視日誌）將會通過這個過程來正確的建立。

dbms_advisor.tune_mview的語法與操作
下面是dbms_advisor.tune_mview的語法:

dbms_advisor.tune_mview(task_name in out varchar2, mv_create_stmt in [clob | varchar2])

dbms_advisor.tune_mview有兩個輸入引數:task_name與mv_create_stmt。task_name是一個使用者提供的任務標識用來訪問輸出結果。mv_create_stmt是一個要被優化的完整的create materialized view 語句。如果輸入的create materialized view語句沒有包含refresh fast或enable query rewrite或者這兩者的話，dbms_advisor.tune_mview將使用預設子句refresh force與disable query rewrite 來優化語句使它能快速重新整理或者能完全重新整理。

dbms_advisor.tune_mview過程處理create materialized view語句的範圍比較廣，它可以是任意查詢。查詢可能是一個簡單的select語句或一個有集合操作，或內聯檢視的複雜查詢。當物化檢視的定義查詢包含refresh fast子句時，dbms_advisor.tune_mview過程將會分析查詢並且檢檢視是否能滿足快速重新整理。如果物化檢視已經滿足了快速重新整理的要求，那麼dbms_advisor.tune_mview過程將返回訊息說"這個物化檢視已經是優化的了並且將不會執行優化處理"，否則，dbms_advisor.tune_mview 過程將對指定的語句啟動優化操作。

dbms_advisor.tune_mview過程為了讓物化檢視能夠滿足fast refresh的要求可以通過增加額外的列比如聚合操作列或修復物化檢視日誌來生成正確定義的查詢語句。在物化檢視使用複雜查詢的情況下，dbms_advisor.tune_mview過程可能會分解查詢並且生成兩個或多個快速重新整理的物化檢視或者為了滿足快速重新整理的要求而重新定義物化檢視。dbms_advisor.tune_mview過程支援以下複雜的查詢結構:
.集合操作(union,union all,minus與intersect)
.count distinct
.select distinct
.內聯檢視

當物化檢視定義查詢語句指定了enable query rewrite子句時，dbms_advisor.tune_mview過程也將會使用類似於處理refresh fast的方式來修復語句，它將重新定義物化檢視，因此儘可能多的使用更先進的查詢重寫形式。

dbms_advisor.tune_mview過程將以可執行語句的方式來生成兩組輸出結果。一組輸出是物化檢視的實現與所請求的元件，比如物化檢視日誌或為了實現快速重新整理和儘可能的查詢重寫與原語句等價的重寫語句。另一組輸出是用於刪除物化檢視和與原語句等價的重寫語句。

實現過程的輸出語句包括:
.create materialized view log語句:建立為了快速重新整理而抽失的物化檢視日誌
.alter materialized view log force語句:修復任何物化檢視日誌為了快速重新整理而缺失的過濾列，序列等等
.一個或多個create materialized view語句:在這種情況下會輸出一個由原始查詢語句直接重寫與轉換而來的語句。簡單的查詢轉換可能是調整所請求的列。例如，為物化聯接檢視增加rowid列，並且為物化聚合檢視增加聚合列。在分解複雜語句的情況下，由原始語句會生成多個create materialized view語句並且通過一個新的頂級物化檢視來引用一個或多個子物化檢視來形成一個嵌套的物化檢視。這將盡可能地完成快速重新整理與查詢重寫。
.build_safe_rewrite_equivalence語句:使用子物化檢視來重寫頂級物化檢視。它被要求當組合出現時能夠使用查詢重寫。

分解的結果就子物化檢視沒有被共享。在分解情況下，dbms_advisor.tune_mview過程將總是包含新的子物化檢視並且它將在現有物化檢視中將不再被引用。

撤消操作的輸出語句包括:
.drop materialized view語句是為了撤消實現處理輸出中所建立的物化檢視。
.drop_rewrite_equivalence語句用來刪除實現處理輸出中與原始查詢等價的語句。

注意撤消處理不會包含刪除物化檢視日誌的操作。這是因為物化檢視日誌可以被多個不同的物化檢視所共享，有一些還可能儲存在遠端的資料庫中。

訪問dbms_advisor.tune_mview輸出結果
有兩種方法可以來訪問dbms_advisor.tune_mview輸出結果:
.使用dbms_advisor.get_task_script函式與dbms_advisor.create_file過程來生成指令碼。
.使用user_tune_mview或dba_tune_mview檢視。

user_tune_mview與dba_tune_mview檢視
在執行tune_mview後，輸出結果會插入到SQL Access Advisor檔案庫表中並且可以通過Oracle檢視 user_tune_mview與dba_tune_mview來進行訪問。

使用dbms_advisor函式與過程來生成指令碼
使用dbms_advisor.get_task_script過程來對建議生成指令碼最簡單的方式。下面是一個簡單的例子。首先，建立一個目錄用來儲存指令碼檔案:

create directory tune_results as '/tmp/script_dir';
grant read, write on directory tune_results to public;

再次就是生成實現與撤消指令碼並將它們分別儲存到/tmp/script_dir/mv_create.sql 與/tmp/script_dir/mv_undo.sql中。

execute dbms_advisor.create_file(dbms_advisor.get_task_script (:task_name),'TUNE_RESULTS', 'mv_create.sql');
execute dbms_advisor.create_file(dbms_advisor.get_task_script(:task_name,'UNDO'),  'TUNE_RESULTS', 'mv_undo.sql');

下面介紹幾個使用dbms_advisor.tune_mview過程來優化物化檢視的例子
1.對快速重新整理物化檢視的查詢語句進行優化
這個例子將介紹如何使用dbms_advisor.tune_mview過程來改變物化檢視的查詢語句來使用它滿足快速重新整理的要求。create materialized view語句使用變數create_mv_ddl來進行定義，它包含一個 fast refresh子句。這個查詢語句包含單個查詢塊，並且包含了一個聚合列sum(s.amount_sold)，但是聚合列不支援快速重新整理。如果對這個物化檢視建立語句執行dbms_advisor.tune_mview過程，那麼輸出結果是建議物化檢視快速重新整理。

SQL> variable task_cust_mv varchar2(30);
SQL> variable create_mv_ddl varchar2(4000);
SQL> execute :task_cust_mv := 'cust_mv';

PL/SQL procedure successfully completed.

SQL> execute :create_mv_ddl := 'create materialized view cust_mv refresh fast disable  query rewrite as select s.prod_id, s.cust_id, sum(s.amount_sold) sum_amount from sales  s, customers cs where s.cust_id = cs.cust_id group by s.prod_id, s.cust_id';

PL/SQL procedure successfully completed.

SQL> execute dbms_advisor.tune_mview(:task_cust_mv, :create_mv_ddl);

PL/SQL procedure successfully completed.

使用dbms_advisor.get_task_script函式與dbms_advisor.create_file過程來生成指令碼文字的方式來檢視dbms_advisor.tune_mview的輸出結果:

建立儲存指令碼檔案的目錄

SQL> create directory tune_results as '/backup';
grant read, write on directory tune_results to public;
Directory created.


SQL> grant read, write on directory tune_results to public;

Grant succeeded.

生成指令碼檔案

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('cust_mv'),'TUNE_RESULTS', 'jy_mv_create.sql');

PL/SQL procedure successfully completed.

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('cust_mv','UNDO'),'TUNE_RESULTS', 'jy_mv_undo_create.sql');

PL/SQL procedure successfully completed.

檢視指令碼檔案內容:

[root@weblogic28 backup]# cat jy_mv_create.sql
Rem  SQL Access Advisor: Version 10.2.0.5.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            cust_mv
Rem  Execution date:
Rem

CREATE MATERIALIZED VIEW LOG ON
    "SH"."SALES"
    WITH ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."SALES"
    ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."CUSTOMERS"
    WITH ROWID, SEQUENCE("CUST_ID")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."CUSTOMERS"
    ADD ROWID, SEQUENCE("CUST_ID")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW SH.CUST_MV
    REFRESH FAST WITH ROWID
    DISABLE QUERY REWRITE
    AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD")
       M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
       SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID,
       SH.SALES.PROD_ID;

使用user_tune_mview或dba_tune_mview檢視來檢視dbms_advisor.tune_mview過程的輸出結果:

SQL> set long 99999
SQL> col statement for a200
SQL> select statement from dba_tune_mview where owner='SH' and task_name='cust_mv' and  script_type='IMPLEMENTATION';

STATEMENT
---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ------------------------
CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" WITH ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS" WITH ROWID, SEQUENCE ("CUST_ID")   INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE ("CUST_ID")   INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW SH.CUST_MV   REFRESH FAST WITH ROWID DISABLE QUERY REWRITE AS  SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD") M1,  COUNT("SH"."SALES"."AMOUNT_S
OLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS, SH.SALES WHERE SH.SALES.CUST_ID =  SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID

dbms_advisor.tune_mview過程的輸出結果包含了一個優化後的物化檢視定義查詢語句如下:

CREATE MATERIALIZED VIEW SH.CUST_MV
    REFRESH FAST WITH ROWID
    DISABLE QUERY REWRITE
    AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD")
       M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
       SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID GROUP BY SH.SALES.CUST_ID,
       SH.SALES.PROD_ID;

dbms_advisor.tune_mview過程的撤消輸出結果下:

[root@weblogic28 backup]# cat jy_mv_undo_create.sql
Rem  SQL Access Advisor: Version 10.2.0.5.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            cust_mv
Rem  Execution date:
Rem

DROP MATERIALIZED VIEW SH.CUST_MV;

或者

SQL> select statement from dba_tune_mview where owner='SH' and task_name='cust_mv' and  script_type='UNDO';

STATEMENT
------------------------------------------------
DROP MATERIALIZED VIEW SH.CUST_MV

2.通過建立多個物化檢視來滿足查詢重寫的要求
這個例子介紹了一個使用了集合操作union的物化檢視查詢語句，它不支援查詢重寫，但通過分解成多個子物化檢視後可以滿足查詢重寫的要求。對於物化檢視所引用的基表sales,customers與 countries，它們沒有物化檢視日誌。

首先對建立物化檢視的語句執行dbms_advisor.tune_mview過程

SQL> variable task_cust_mv varchar2(30);
SQL> variable create_mv_ddl varchar2(4000);
SQL> execute :task_cust_mv := 'cust_mv_2';

PL/SQL procedure successfully completed.

SQL> execute :create_mv_ddl := 'create materialized view cust_mv enable query rewrite as  select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from sales s,  customers cs, countries cn where s.cust_id = cs.cust_id and cs.country_id =  cn.country_id and cn.country_name in (''usa'',''canada'') group by s.prod_id, s.cust_id  union select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from  sales s, customers cs where s.cust_id = cs.cust_id and s.cust_id in (1005,1010,1012)  group by s.prod_id, s.cust_id';

PL/SQL procedure successfully completed.

SQL> execute dbms_advisor.tune_mview(:task_cust_mv, :create_mv_ddl);

PL/SQL procedure successfully completed.

檢視dbms_advisor.tune_mview優化的實現輸出結果

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('cust_mv_2'),'TUNE_RESULTS', 'jy_mv_2_create.sql');

PL/SQL procedure successfully completed.

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('cust_mv_2','UNDO'),'TUNE_RESULTS', 'jy_mv_2_undo_create.sql');

PL/SQL procedure successfully completed.

[root@localhost backup]# cat jy_mv_2_create.sql
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            cust_mv_2
Rem  Execution date:
Rem

CREATE MATERIALIZED VIEW LOG ON
    "SH"."COUNTRIES"
    WITH ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."COUNTRIES"
    ADD ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_NAME")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."SALES"
    WITH ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."SALES"
    ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."CUSTOMERS"
    WITH ROWID, SEQUENCE("CUST_ID","COUNTRY_ID")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."CUSTOMERS"
    ADD ROWID, SEQUENCE("CUST_ID","COUNTRY_ID")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."SALES"
    ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."CUSTOMERS"
    ADD ROWID, SEQUENCE("CUST_ID")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1
    REFRESH FAST WITH ROWID ON COMMIT
    ENABLE QUERY REWRITE
    AS SELECT SH.COUNTRIES.COUNTRY_NAME C1, SH.SALES.CUST_ID C2, SH.SALES.PROD_ID C3,
       SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD")
       M2, COUNT(*) M3 FROM SH.CUSTOMERS, SH.SALES, SH.COUNTRIES WHERE SH.SALES.CUST_ID
       = SH.CUSTOMERS.CUST_ID AND SH.COUNTRIES.COUNTRY_ID = SH.CUSTOMERS.COUNTRY_ID
       AND (SH.COUNTRIES.COUNTRY_NAME IN ('usa', 'canada')) GROUP BY  SH.COUNTRIES.COUNTRY_NAME,
       SH.SALES.CUST_ID, SH.SALES.PROD_ID;

CREATE MATERIALIZED VIEW SH.CUST_MV$SUB2
    REFRESH FAST WITH ROWID ON COMMIT
    ENABLE QUERY REWRITE
    AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD")
       M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
       SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND (SH.SALES.CUST_ID
       IN (1012, 1010, 1005)) GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID;

CREATE MATERIALIZED VIEW SH.CUST_MV
    REFRESH FORCE WITH ROWID
    ENABLE QUERY REWRITE
    AS (SELECT "CUST_MV$SUB1"."C3" "PROD_ID","CUST_MV$SUB1"."C2" "CUST_ID",SUM("CUST_MV $SUB1"."M3")
       "CNT",SUM("CUST_MV$SUB1"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV $SUB1"
       GROUP BY "CUST_MV$SUB1"."C3","CUST_MV$SUB1"."C2")UNION (SELECT "CUST_MV $SUB2"."C2"
       "PROD_ID","CUST_MV$SUB2"."C1" "CUST_ID","CUST_MV$SUB2"."M3" "CNT","CUST_MV $SUB2"."M1"
       "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB2" "CUST_MV$SUB2");

begin
  sys.dbms_advanced_rewrite.build_safe_rewrite_equivalence('SH.CUST_MV$RWEQ','select  s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from sales s,  customers cs, countries cn where s.cust_id = cs.cust_id and cs.country_id =  cn.country_id and cn.country_name in (''usa'',''canada'') group by s.prod_id, s.cust_id  union select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from  sales s, customers cs where s.cust_id = cs.cust_id and s.cust_id in (1005,1010,1012)  group by s.prod_id, s.cust_id',' (SELECT "CUST_MV$SUB1"."C3" "PROD_ID","CUST_MV $SUB1"."C2" "CUST_ID",SUM("CUST_MV$SUB1"."M3") "CNT",SUM("CUST_MV$SUB1"."M1")  "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" GROUP BY "CUST_MV $SUB1"."C3","CUST_MV$SUB1"."C2")UNION (SELECT "CUST_MV$SUB2"."C2" "PROD_ID","CUST_MV $SUB2"."C1" "CUST_ID","CUST_MV$SUB2"."M3" "CNT","CUST_MV$SUB2"."M1" "SUM_AMOUNT" FROM  "SH"."CUST_MV$SUB2" "CUST_MV$SUB2")',-2066096392);
end
;
/

或者

SQL> select statement from dba_tune_mview where owner='SH' and task_name='cust_mv_2' and  script_type='IMPLEMENTATION';

STATEMENT
---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ------------------------------------------------
CREATE MATERIALIZED VIEW LOG ON "SH"."COUNTRIES" WITH ROWID, SEQUENCE  ("COUNTRY_ID","COUNTRY_NAME")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."COUNTRIES" ADD ROWID, SEQUENCE  ("COUNTRY_ID","COUNTRY_NAME")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" WITH ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS" WITH ROWID, SEQUENCE  ("CUST_ID","COUNTRY_ID")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE  ("CUST_ID","COUNTRY_ID")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE ("CUST_ID")   INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1   REFRESH FAST WITH ROWID ON COMMIT ENABLE  QUERY REWRITE AS SELECT SH.COUNTRIES.COUNTRY_NAME C1, SH.SALES.CUST_ID C2,  SH.SALES.PROD_ID C3, SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT ("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS, SH.SALES, SH.COUNTRIES  WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND SH.COUNTRIES.COUNTRY_ID =  SH.CUSTOMERS.COU
NTRY_ID AND (SH.COUNTRIES.COUNTRY_NAME IN ('usa', 'canada')) GROUP BY  SH.COUNTRIES.COUNTRY_NAME, SH.SALES.CUST_ID, SH.SALES.PROD_ID

CREATE MATERIALIZED VIEW SH.CUST_MV$SUB2   REFRESH FAST WITH ROWID ON COMMIT ENABLE  QUERY REWRITE AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM ("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM  SH.CUSTOMERS, SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND  (SH.SALES.CUST_ID IN (1012, 1010, 1005)) GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID
CREATE MATERIALIZED VIEW SH.CUST_MV   REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS   (SELECT "CUST_MV$SUB1"."C3" "PROD_ID","CUST_MV$SUB1"."C2" "CUST_ID",SUM("CUST_MV $SUB1"."M3") "CNT",SUM("CUST_MV$SUB1"."M1") "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1"  "CUST_MV$SUB1" GROUP BY "CUST_MV$SUB1"."C3","CUST_MV$SUB1"."C2")UNION (SELECT "CUST_MV $SUB2"."C2" "PROD_ID","CUST_MV$SUB2"."C1" "CUST_ID","CUST_MV$SUB
2"."M3" "CNT","CUST_MV$SUB2"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB2" "CUST_MV$SUB2")

DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('SH.CUST_MV$RWEQ','select  s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from sales s,  customers cs, countries cn where s.cust_id = cs.cust_id and cs.country_id =  cn.country_id and cn.country_name in ('usa','canada') group by s.prod_id, s.cust_id  union select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount f
rom sales s, customers cs where s.cust_id = cs.cust_id and s.cust_id in (1005,1010,1012)  group by s.prod_id, s.cust_id',' (SELECT "CUST_MV$SUB1"."C3" "PROD_ID","CUST_MV $SUB1"."C2" "CUST_ID",SUM("CUST_MV$SUB1"."M3") "CNT",SUM("CUST_MV$SUB1"."M1")  "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" GROUP BY "CUST_MV $SUB1"."C3","CUST_MV$SUB1"."C2")UNION (SELECT "CUST_MV$SUB2"."C2" "PROD_ID","CUST_MV
$SUB2"."C1" "CUST_ID","CUST_MV$SUB2"."M3" "CNT","CUST_MV$SUB2"."M1" "SUM_AMOUNT" FROM  "SH"."CUST_MV$SUB2" "CUST_MV$SUB2")',-2066096392)

檢視dbms_advisor.tune_mview優化的撤消輸出結果

[root@localhost backup]# cat jy_mv_2_undo_create.sql
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            cust_mv_2
Rem  Execution date:
Rem

DROP MATERIALIZED VIEW SH.CUST_MV$SUB1;

DROP MATERIALIZED VIEW SH.CUST_MV$SUB2;

DROP MATERIALIZED VIEW SH.CUST_MV;

begin
  sys.dbms_advanced_rewrite.drop_rewrite_equivalence('SH.CUST_MV$RWEQ');
end;
/

原始物化檢視cust_mv的查詢語句已經被分解成了兩個子物化檢視cust_mv$sub1和cust_mv$sub2。有一個額外的count(amount_sold)列增加到了cust_mv$sub1中讓物化檢視滿足快速重新整理要求。

原始物化檢視cust_mv的查詢語句已經變成引用兩個子物化檢視了，使用兩個子物化檢視來滿足快速重新整理與查詢重寫的需要。

為了讓兩個子物化檢視能夠滿足快速重新整理，需要對所引用的基表增加物化檢視日誌。注意，每個基表會生成兩個建立物化檢視日誌的語句：一個是create materialized view log語句，另一個是alter materialized view log force語句。這可以確保create指令碼可以執行多次。

dbms_advanced_rewrite.build_safe_rewrite_equivalence所建立的語句結合了原始物化檢視查詢語句來定義新的頂級物化檢視查詢語句。它可以確保使用新的頂級物化檢視可以滿足查詢重寫的需要。

使用優化的子物化檢視來滿足快速重新整理的例子
這個例子介紹如何使用dbms_advisor.tune_mview來優化物化檢視來滿足快速重新整理的需要。在這個例子中，物化檢視的查詢語句使用了集合操作，它被換為一個子物化檢視與一個新的頂級物化檢視。

這個例子中的物化檢視的查詢語句包含union集合操作，因此物化檢視本身不能滿足快速重新整理。然而物化檢視中的兩個子查詢可以合成一個單獨的查詢語句。

SQL> variable task_cust_mv varchar2(30);
SQL> variable create_mv_ddl varchar2(4000);
SQL> execute :task_cust_mv := 'cust_mv3';

PL/SQL procedure successfully completed.

SQL> execute :create_mv_ddl := 'create materialized view cust_mv refresh fast on demand  enable query rewrite as select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold)  sum_amount from sales s, customers cs where s.cust_id = cs.cust_id and s.cust_id in  (2005,1020) group by s.prod_id, s.cust_id union select s.prod_id, s.cust_id, count(*)  cnt, sum(s.amount_sold) sum_amount from sales s, customers cs where s.cust_id =  cs.cust_id and s.cust_id in (1005,1010,1012) group by s.prod_id, s.cust_id';

PL/SQL procedure successfully completed.



SQL> execute dbms_advisor.tune_mview(:task_cust_mv, :create_mv_ddl);

PL/SQL procedure successfully completed.

檢視dbms_advisor.tune_mview優化的實現輸出結果
下面的建議使用一個優化的子物化檢視（包含兩個子查詢）與引用子物化檢視的新的頂級物化檢視

SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('cust_mv3'),'TUNE_RESULTS', 'jy_mv3_create.sql');

PL/SQL procedure successfully completed.



SQL> execute dbms_advisor.create_file(dbms_advisor.get_task_script ('cust_mv3','UNDO'),'TUNE_RESULTS', 'jy_mv3_undo_create.sql');

PL/SQL procedure successfully completed.


[root@localhost backup]# cat jy_mv3_create.sql
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            cust_mv3
Rem  Execution date:
Rem

CREATE MATERIALIZED VIEW LOG ON
    "SH"."SALES"
    WITH ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."SALES"
    ADD ROWID, SEQUENCE("PROD_ID","CUST_ID","AMOUNT_SOLD")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."CUSTOMERS"
    WITH ROWID, SEQUENCE("CUST_ID")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."CUSTOMERS"
    ADD ROWID, SEQUENCE("CUST_ID")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1
    REFRESH FAST WITH ROWID ON COMMIT
    ENABLE QUERY REWRITE
    AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM("SH"."SALES"."AMOUNT_SOLD")
       M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM SH.CUSTOMERS,
       SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND (SH.SALES.CUST_ID
       IN (2005, 1020, 1012, 1010, 1005)) GROUP BY SH.SALES.CUST_ID, SH.SALES.PROD_ID;

CREATE MATERIALIZED VIEW SH.CUST_MV
    REFRESH FORCE WITH ROWID
    ENABLE QUERY REWRITE
    AS (SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV $SUB1"."M3"
       "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1"
       WHERE "CUST_MV$SUB1"."C1"=2005 OR "CUST_MV$SUB1"."C1"=1020)UNION (SELECT
       "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV$SUB1"."M3"
       "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1"
       WHERE "CUST_MV$SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR "CUST_MV $SUB1"."C1"=1005);

begin
  sys.dbms_advanced_rewrite.build_safe_rewrite_equivalence('SH.CUST_MV$RWEQ','select  s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from sales s,  customers cs where s.cust_id = cs.cust_id and s.cust_id in (2005,1020) group by  s.prod_id, s.cust_id union select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold)  sum_amount from sales s, customers cs where s.cust_id = cs.cust_id and s.cust_id in  (1005,1010,1012) group by s.prod_id, s.cust_id',' (SELECT "CUST_MV$SUB1"."C2"  "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1"  "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" WHERE "CUST_MV$SUB1"."C1"=2005 OR  "CUST_MV$SUB1"."C1"=1020)UNION (SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1"  "CUST_ID","CUST_MV$SUB1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV $SUB1" "CUST_MV$SUB1" WHERE "CUST_MV$SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR  "CUST_MV$SUB1"."C1"=1005)',-179817297);
end
;
/

或者

SQL> select statement from dba_tune_mview where owner='SH' and task_name='cust_mv3' and  script_type='IMPLEMENTATION';

STATEMENT
---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ------------------------------------------------
CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" WITH ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."SALES" ADD ROWID, SEQUENCE  ("PROD_ID","CUST_ID","AMOUNT_SOLD")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS" WITH ROWID, SEQUENCE ("CUST_ID")   INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "SH"."CUSTOMERS" ADD ROWID, SEQUENCE ("CUST_ID")   INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW SH.CUST_MV$SUB1   REFRESH FAST WITH ROWID ON COMMIT ENABLE  QUERY REWRITE AS SELECT SH.SALES.CUST_ID C1, SH.SALES.PROD_ID C2, SUM ("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2, COUNT(*) M3 FROM  SH.CUSTOMERS, SH.SALES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND  (SH.SALES.CUST_ID IN (2005, 1020, 1012, 1010, 1005)) GROUP BY SH.SALES.CUST_ID, SH.SAL
ES.PROD_ID

CREATE MATERIALIZED VIEW SH.CUST_MV   REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS   (SELECT "CUST_MV$SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV$SUB1"."M3"  "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" WHERE  "CUST_MV$SUB1"."C1"=2005 OR "CUST_MV$SUB1"."C1"=1020)UNION (SELECT "CUST_MV$SUB1"."C2"  "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV$SUB
1"."M3" "CNT","CUST_MV$SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1"  WHERE "CUST_MV$SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR "CUST_MV$SUB1"."C1"=1005)

DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('SH.CUST_MV$RWEQ','select  s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold) sum_amount from sales s,  customers cs where s.cust_id = cs.cust_id and s.cust_id in (2005,1020) group by  s.prod_id, s.cust_id union select s.prod_id, s.cust_id, count(*) cnt, sum(s.amount_sold)  sum_amount from sales s, customers cs where s.cust_id = cs.cust_id and
s.cust_id in (1005,1010,1012) group by s.prod_id, s.cust_id',' (SELECT "CUST_MV $SUB1"."C2" "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV$SUB1"."M3" "CNT","CUST_MV $SUB1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" WHERE "CUST_MV $SUB1"."C1"=2005 OR "CUST_MV$SUB1"."C1"=1020)UNION (SELECT "CUST_MV$SUB1"."C2"  "PROD_ID","CUST_MV$SUB1"."C1" "CUST_ID","CUST_MV$SUB1"."M3" "CNT","CUST_MV$SU
B1"."M1" "SUM_AMOUNT" FROM "SH"."CUST_MV$SUB1" "CUST_MV$SUB1" WHERE "CUST_MV $SUB1"."C1"=1012 OR "CUST_MV$SUB1"."C1"=1010 OR "CUST_MV$SUB1"."C1"=1005)',-179817297)

原始物化檢視cust_mv查詢被優化成一個包含謂詞條件的兩個子查詢的子物化檢視cust_mv$sub1。為了能讓子物化檢視滿足快速重新整理的需要，對所引用的基表增加物化檢視日誌。

檢視dbms_advisor.tune_mview過程的撤消輸出結果:

[root@localhost backup]# cat jy_mv3_undo_create.sql
Rem  SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem
Rem  Username:        SH
Rem  Task:            cust_mv3
Rem  Execution date:
Rem

DROP MATERIALIZED VIEW SH.CUST_MV$SUB1;

DROP MATERIALIZED VIEW SH.CUST_MV;

begin
  sys.dbms_advanced_rewrite.drop_rewrite_equivalence('SH.CUST_MV$RWEQ');
end;
/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2124722/，如需轉載，請註明出處，否則將追究法律責任。

ClickHouse效能優化？試試物化檢視
2021-04-07
優化
ClickHouse 效能優化？試試物化檢視
2022-07-12
優化
物化檢視妙用__表同步使用物化檢視方法
2017-09-01
使用物化檢視查詢重寫優化對於 UNION ALL檢視的CONNECT BY查詢
2013-02-26
優化
物化檢視上使用bitmap索引
2009-07-17
索引
【筆記】使用物化檢視（一）
2009-08-31
筆記
【物化檢視】幾種物化檢視日誌分析
2011-04-11
【物化檢視】根據物化檢視日誌快速重新整理物化檢視的過程
2011-04-12
oracle物化檢視
2009-12-11
Oracle
使用 on prebuilt table 建立物化檢視 (ZT)
2006-04-26
UI
使用 on prebuilt table 建立物化檢視(zt)
2005-01-20
UI
Oracle物化檢視的建立及使用（二）
2018-07-17
Oracle
Oracle物化檢視的建立及使用（一）
2018-07-17
Oracle
物化檢視詳解
2016-11-08
oracle 建立物化檢視
2014-06-03
Oracle
Oracle 物化檢視建立
2012-11-28
Oracle
materialized view (物化檢視)
2004-12-02
ZedView
物化檢視 on commit
2011-12-28
MIT
物化檢視日誌表被DROP後建立物化檢視報錯
2009-10-28
Oracle如何根據物化檢視日誌快速重新整理物化檢視
2016-07-18
Oracle
物化檢視的快速重新整理測試與物化檢視日誌
2014-11-12
普通檢視和物化檢視的區別
2017-05-12
calcite物化檢視詳解
2022-03-20
Oracle物化檢視詳解
2018-02-28
Oracle
ORACLE物化檢視測試
2013-11-26
Oracle
Oracle 物化檢視案例分享
2015-01-18
Oracle
物化檢視梳理總結
2014-11-27
ZT 物化檢視詳解
2009-02-06
Oracle物化檢視語法
2012-04-06
Oracle
ORACLE物化檢視入門
2016-03-22
Oracle
物化檢視-學習篇
2013-04-08
刷物化檢視並行
2009-12-10
並行
oracle物化檢視系列(二)
2013-02-21
Oracle
oracle物化檢視系列(一)
2013-02-21
Oracle
12c 物化檢視 - 理解完全重新整理的物化檢視工作原理
2018-01-23
物化檢視匯出匯入可能導致物化檢視日誌的失效
2010-07-21
DB2資料庫物化檢視：MQT物化查詢表的使用
2008-08-20
DB2資料庫MQQT
Oracle普通檢視和物化檢視的區別
2018-07-17
Oracle

使用dbms_advisor優化物化檢視

相關文章