不少人大概和我一样在创建物化视图的时候会犯头痛，怎样合理的改写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个物化视图的扫描 */