使用dbms_advisor优化物化视图 – 但行好事莫问前程

为了快速刷新与查询重写优化物化视图
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;
/

发表评论 取消回复

发表评论取消回复