不少人大概和我一样在创建物化视图的时候会犯头痛，怎样合理的改写SQL语句以及添加物化视图日志需要经过慎重精密的考虑。有了DBMS_ADVISOR.TUNE_MVIEW存储过程这个帮手后，极大地方便了DBA或应用设计人员创建和优化物化视图。该TUNE_MVIEW存储过程可以做到优化物化视图中的查询定义，修正物化视图日志的问题，此外它还能为原先不能refresh fast的物化视图提出建议以使得其可以快速刷新。
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
SQL>  CREATE  MATERIALIZED  VIEW  MACLEAN.STRMTS
   2  USING  INDEX  REFRESH FAST  ON  DEMAND
   3  ENABLE QUERY REWRITE
   4   AS  select  distinct  t1,t2  from  MACLEAN.strb;
AS  select  distinct  t1,t2  from  MACLEAN.strb
                                       *
ERROR  at  line 4:
ORA-12015: cannot  create  a fast refresh materialized  view  from  a complex query
 
 
/* 以 select  distinct 查询语句为例，该语句本身不符合refresh fast的标准，但TUNE_MVIEW存储过程
     可以将这种查询变形使得满足快速刷新的条件 */
 
--    PROCEDURE DBMS_ADVISOR.TUNE_MVIEW
--    PURPOSE: Tune a Create Materialized View statement to
--    ADVISOR SUPPORT:        SQL Access Advisor
--    PARAMETERS:
--         TASK_NAME
--            The user can pass in a user-defined task name or
--            get a returned system-generated task name.
--         MV_CREATE_STMT
--            CREATE MATERIALIZED VIEW SQL statement to tune
 
procedure  tune_mview (task_name       in  out  varchar2,
                       mv_create_stmt  in      clob);
 
SQL>  set  serveroutput  on ;
 
SQL>  declare
   2    tn varchar2(200);
   3   begin
   4    DBMS_ADVISOR.TUNE_MVIEW(tn,
   5                            mv_create_stmt =>  'CREATE MATERIALIZED VIEW MACLEAN.STRMTS
   6                     USING INDEX REFRESH FAST ON DEMAND
   7                     ENABLE QUERY REWRITE
   8                     AS select distinct t1,t2 from MACLEAN.strb' );
   9    dbms_output.put_line(tn);
  10   end ;
  11  /
TASK_484
 
PL/SQL  procedure  successfully completed.
 
SQL>  select  script_type,statement
   2     from  dba_tune_mview
   3    where  task_name =  'TASK_484'
   4    order  by  action_id;
 
SCRIPT_TYPE    STATEMENT
 
IMPLEMENTATION  CREATE  MATERIALIZED  VIEW  LOG  ON  "MACLEAN" . "STRB"  WITH  ROWID,  SEQUENCE  ( "T1" , "T2" )  INCLUDING NEW  VALUES
IMPLEMENTATION  ALTER  MATERIALIZED  VIEW  LOG  FORCE  ON  "MACLEAN" . "STRB"  ADD  ROWID,  SEQUENCE  ( "T1" , "T2" )  INCLUDING NEW  VALUES
IMPLEMENTATION  CREATE  MATERIALIZED  VIEW  MACLEAN.STRMTS USING  INDEX   REFRESH FAST  WITH  ROWID ENABLE QUERY REWRITE  AS  SELECT  MACLEAN.STRB.T2 C1, MACLEAN.STRB.T1 C2,  COUNT (*) M1  FROM  MACLEAN.STRB  GROUP  BY  MACLEAN.STRB.T2, MACLEAN.STRB.T1
UNDO            DROP  MATERIALIZED  VIEW  MACLEAN.STRMTS
 
/* 可以看到TUNE_MVIEW存储过程将原查询变形为 SELECT ... GROUP  BY 的形式 */
 
SQL>  CREATE  MATERIALIZED  VIEW  LOG  ON  "MACLEAN" . "STRB"  WITH  ROWID,  SEQUENCE  ( "T1" , "T2" )  INCLUDING NEW  VALUES ;
Materialized  view  log created.
 
SQL>  ALTER  MATERIALIZED  VIEW  LOG  FORCE  ON  "MACLEAN" . "STRB"  ADD  ROWID,  SEQUENCE  ( "T1" , "T2" )  INCLUDING NEW  VALUES ;
Materialized  view  log altered.
 
SQL>  CREATE  MATERIALIZED  VIEW  MACLEAN.STRMTS USING  INDEX   REFRESH FAST  WITH  ROWID
ENABLE QUERY REWRITE  AS  SELECT  MACLEAN.STRB.T2 C1, MACLEAN.STRB.T1 C2,  COUNT (*) M1
FROM  MACLEAN.STRB  GROUP  BY  MACLEAN.STRB.T2, MACLEAN.STRB.T1;
 
Materialized  view  created.
针对那些确实无法快速刷新的复杂查询，TUNE_MVIEW过程也可能给出将一个查询分解为多个物化视图达到快速刷新和查询重写的目的:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
SQL>  CREATE  MATERIALIZED  VIEW  MACLEAN.STRMTD
   2  USING  INDEX  REFRESH FAST
   3   ON  DEMAND ENABLE QUERY REWRITE  AS
   4   select  t2,t3, count (*)  from  strc  group  by  t2,t3
   5   union  all
   6   select  t2,t3, count (*)  from  strd  group  by  t2,t3;
select  t2,t3, count (*)  from  strd  group  by  t2,t3
                            *
ERROR  at  line 6:
ORA-12015: cannot  create  a fast refresh materialized  view  from  a complex query
 
 
SQL>  set  serveroutput  on ;
SQL>  declare
   2    tn varchar2(200);
   3   begin
   4    DBMS_ADVISOR.TUNE_MVIEW(tn,
   5                            mv_create_stmt =>  'CREATE MATERIALIZED VIEW MACLEAN.STRMTC
   6                                                   USING INDEX REFRESH FAST
   7                                                   ON DEMAND ENABLE QUERY REWRITE AS
   8                                                   select t2,t3,count(*) from strc group by t2,t3
   9                                                   union all
  10                                                  select t2,t3,count(*) from strd group by t2,t3' );
  11    dbms_output.put_line(tn);
  12   end ;
  13  /
TASK_547
 
PL/SQL  procedure  successfully completed.
 
SQL>  select  statement
   2     from  dba_tune_mview
   3    where  task_name =  'TASK_547'
   4    order  by  action_id;
 
CREATE  MATERIALIZED  VIEW  LOG  ON  "MACLEAN" . "STRC"  WITH  ROWID,  SEQUENCE  ( "T2" , "T3" )  INCLUDING NEW  VALUES
ALTER  MATERIALIZED  VIEW  LOG  FORCE  ON  "MACLEAN" . "STRC"  ADD  ROWID,  SEQUENCE  ( "T2" , "T3" )  INCLUDING NEW  VALUES
CREATE  MATERIALIZED  VIEW  LOG  ON  "MACLEAN" . "STRD"  WITH  ROWID,  SEQUENCE  ( "T2" , "T3" )  INCLUDING NEW  VALUES
ALTER  MATERIALIZED  VIEW  LOG  FORCE  ON  "MACLEAN" . "STRD"  ADD  ROWID,  SEQUENCE  ( "T2" , "T3" )  INCLUDING NEW  VALUES
CREATE  MATERIALIZED  VIEW  MACLEAN.STRMTC$SUB1 USING  INDEX   REFRESH FAST  WITH  ROWID  ON  COMMIT  ENABLE QUERY REWRITE  AS  SELECT  MACLEAN.STRC.T3 C1, MACLEAN.STRC.T2 C2,  COUNT (*) M1  FROM  MACLEAN.STRC  GROUP  BY  MACLEAN.STRC.T3, MACLEAN.STRC.T2
DROP  MATERIALIZED  VIEW  MACLEAN.STRMTC$SUB1
CREATE  MATERIALIZED  VIEW  MACLEAN.STRMTC$SUB2 USING  INDEX   REFRESH FAST  WITH  ROWID  ON  COMMIT  ENABLE QUERY REWRITE  AS  SELECT  MACLEAN.STRD.T3 C1, MACLEAN.STRD.T2 C2,  COUNT (*) M1  FROM  MACLEAN.STRD  GROUP  BY  MACLEAN.STRD.T3, MACLEAN.STRD.T2
DROP  MATERIALIZED  VIEW  MACLEAN.STRMTC$SUB2
CREATE  MATERIALIZED  VIEW  MACLEAN.STRMTC USING  INDEX   REFRESH  FORCE  WITH  ROWID ENABLE QUERY REWRITE  AS   ( SELECT  "STRMTC$SUB1" . "C2"  "T2" , "STRMTC$SUB1" . "C1"  "T3" , "STRMTC$SUB1" . "M1"  "COUNT(*)"  FROM  "MACLEAN" . "STRMTC$SUB1"  "STRMTC$SUB1" )  UNION  ALL   ( SELECT  "STRMTC$SUB2" . "C2"  "T2" , "STRMTC$SUB2" . "C1"  "T3" , "STRMTC$SUB2" . "M1"  "COUNT(*)"  FROM  "MACLEAN" . "STRMTC$SUB2"  "STRMTC$SUB2" )
DROP  MATERIALIZED  VIEW  MACLEAN.STRMTC
DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ( 'MACLEAN.STRMTC$RWEQ' , 'select t2,t3,count(*) from strc group by t2,t3
                                                  union all
                                                 select t2,t3,count(*) from strd group by t2,t3' , ' (SELECT "STRMTC$SUB1"."C2" "T2","STRMTC$SUB1"."C1" "T3","STRMTC$SUB1"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB1" "STRMTC$SUB1") UNION ALL  (SELECT "STRMTC$SUB2"."C2" "T2","STRMTC$SUB2"."C1" "T3","STRMTC$SUB2"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB2" "STRMTC$SUB2")' ,600916906)
 
DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE( 'MACLEAN.STRMTC$RWEQ' )
 
SQL>  CREATE  MATERIALIZED  VIEW  LOG  ON  "MACLEAN" . "STRC"  WITH  ROWID,  SEQUENCE  ( "T2" , "T3" )  INCLUDING NEW  VALUES ;
 
Materialized  view  log created.
 
SQL>  ALTER  MATERIALIZED  VIEW  LOG  FORCE  ON  "MACLEAN" . "STRC"  ADD  ROWID,  SEQUENCE  ( "T2" , "T3" )  INCLUDING NEW  VALUES ;
 
Materialized  view  log altered.
 
SQL>  CREATE  MATERIALIZED  VIEW  LOG  ON  "MACLEAN" . "STRD"  WITH  ROWID,  SEQUENCE  ( "T2" , "T3" )  INCLUDING NEW  VALUES ;
 
Materialized  view  log created.
 
SQL>  ALTER  MATERIALIZED  VIEW  LOG  FORCE  ON  "MACLEAN" . "STRD"  ADD  ROWID,  SEQUENCE  ( "T2" , "T3" )  INCLUDING NEW  VALUES ;
 
Materialized  view  log altered.
 
SQL>  CREATE  MATERIALIZED  VIEW  MACLEAN.STRMTC$SUB1 USING  INDEX   REFRESH FAST  WITH  ROWID
ON  COMMIT  ENABLE QUERY REWRITE  AS  SELECT  MACLEAN.STRC.T3 C1, MACLEAN.STRC.T2 C2,  COUNT (*) M1
FROM  MACLEAN.STRC  GROUP  BY  MACLEAN.STRC.T3, MACLEAN.STRC.T2;
 
Materialized  view  created.
 
SQL>  CREATE  MATERIALIZED  VIEW  MACLEAN.STRMTC$SUB2 USING  INDEX   REFRESH FAST  WITH  ROWID
ON  COMMIT  ENABLE QUERY REWRITE  AS  SELECT  MACLEAN.STRD.T3 C1, MACLEAN.STRD.T2 C2,  COUNT (*) M1
FROM  MACLEAN.STRD  GROUP  BY  MACLEAN.STRD.T3, MACLEAN.STRD.T2;
 
Materialized  view  created.
 
SQL>  CREATE  MATERIALIZED  VIEW  MACLEAN.STRMTC USING  INDEX   REFRESH  FORCE  WITH  ROWID
ENABLE QUERY REWRITE  AS
( SELECT  "STRMTC$SUB1" . "C2"  "T2" , "STRMTC$SUB1" . "C1"  "T3" , "STRMTC$SUB1" . "M1"  "COUNT(*)"  FROM  "MACLEAN" . "STRMTC$SUB1"  "STRMTC$SUB1" )
UNION  ALL
( SELECT  "STRMTC$SUB2" . "C2"  "T2" , "STRMTC$SUB2" . "C1"  "T3" , "STRMTC$SUB2" . "M1"  "COUNT(*)"  FROM  "MACLEAN" . "STRMTC$SUB2"  "STRMTC$SUB2" );
 
Materialized  view  created.
 
declare
   v_state varchar2(2000);
begin
   select  statement
     into  v_state
     from  dba_tune_mview
    where  task_name =  'TASK_547'
      and  action_id = 15;
   v_state :=  'begin '  || v_state ||  '; end;' ;
   dbms_output.put_line(v_state);
   execute  immediate v_state;
end ;
PL/SQL  procedure  successfully completed.
 
SQL>  set  linesize 200 pagesize 1400;
SQL>  select  t2,t3, count (*)  from  strc  group  by  t2,t3
   2   union  all
   3   select  t2,t3, count (*)  from  strd  group  by  t2,t3;
no  rows  selected
 
---------------------------------------------------------------------------------------------
| Id  | Operation                     |  Name         |  Rows   | Bytes | Cost (%CPU)|  Time      |
---------------------------------------------------------------------------------------------
|   0 |  SELECT  STATEMENT              |             |     2 |    74 |     4  (50)| 00:00:01 |
|   1 |   UNION - ALL                     |             |       |       |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS  FULL | STRMTC$SUB1 |     1 |    37 |     2   (0)| 00:00:01 |
|   3 |   MAT_VIEW REWRITE ACCESS  FULL | STRMTC$SUB2 |     1 |    37 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
/*　可以看到查询成功被rewrite为对2个物化视图的扫描 */