今天看到yangtingkun的博客中,关于物化视图的文章,相当不错
原文地址:http://yangtingkun.itpub.net/post/468/501542
物化视图的快速刷新(一):http://yangtingkun.itpub.net/post/468/14245
物化视图的快速刷新(二):http://yangtingkun.itpub.net/post/468/16456
物化视图的快速刷新(三):http://yangtingkun.itpub.net/post/468/16496
.
改造包含MAX或MIN的物化视图:http://yangtingkun.itpub.net/post/468/492826
一个经过简化的例子:
CREATE TABLE t1(c1 NUMBER);
CREATE TABLE t2(c1 NUMBER);
CREATE TABLE t3(c1 NUMBER,c2 NUMBER);
-- 创建mv的查询
SELECT t2.c1
,t4.c1
,MAX(t3.c1)
FROM (SELECT MAX(t1.c1) c1
FROM t1) t4
,t2
,t3
WHERE t3.c1 > t4.c1
AND t2.c1 = t3.c2
GROUP BY t2.c1
,t4.c1
对于这种包含两层嵌套的查询,直接建立成快速刷新方式的物化视图显然是不可能的,但是可以利用中间物化视图进行过渡,而且对于10.2中,物化视图快速刷新的限制条件进一步放宽,使得包含MAX/MIN聚集函数的物化视图快速刷新的条件得以放宽:
SQL> CREATE TABLE T1(C1 NUMBER);
Table created.
SQL> CREATE TABLE T2(C1 NUMBER);
Table created.
SQL> CREATE TABLE T3(C1 NUMBER,C2 NUMBER);
Table created.
SQL> CREATE MATERIALIZED VIEW LOG ON T1
2 WITH ROWID, SEQUENCE (C1)
3 INCLUDING NEW VALUES;
Materialized view log created.
SQL> CREATE MATERIALIZED VIEW LOG ON T2
2 WITH ROWID, SEQUENCE (C1)
3 INCLUDING NEW VALUES;
Materialized view log created.
SQL> CREATE MATERIALIZED VIEW LOG ON T3
2 WITH ROWID, SEQUENCE (C1, C2)
3 INCLUDING NEW VALUES;
Materialized view log created.
SQL> CREATE MATERIALIZED VIEW MV_T4
2 REFRESH FAST AS
3 SELECT COUNT(*) CN, COUNT(C1), MAX(T1.C1) C1
4 FROM T1;
Materialized view created.
SQL> CREATE MATERIALIZED VIEW LOG ON MV_T4
2 WITH ROWID, SEQUENCE (C1)
3 INCLUDING NEW VALUES;
Materialized view log created.
SQL> CREATE MATERIALIZED VIEW MV_T123
2 REFRESH FAST AS
3 SELECT T2.C1 T2_C1, MV_T4.C1 T4_C1, COUNT(*) CNT, COUNT(T3.C1), MAX(T3.C1)
4 FROM MV_T4, T2, T3
5 WHERE T3.C1 > MV_T4.C1
6 AND T2.C1 = T3.C2
7 GROUP BY T2.C1, MV_T4.C1;
Materialized view created.
利用中间物化视图,这种多层的快速刷新物化视图可以很容易的建立。
不过需要注意的是,包含MAX/MIN的物化视图,仍然需要INSERT ONLY物化视图,对于包含UPDATE和DELETE操作的情况是不使用的。
SQL> @?/rdbms/admin/utlxmv
Table created.
SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('MV_T123')
PL/SQL procedure successfully completed.
SQL> SELECT CAPABILITY_NAME, POSSIBLE, RELATED_TEXT, MSGTXT
2 FROM MV_CAPABILITIES_TABLE
3 WHERE CAPABILITY_NAME NOT LIKE '%PCT%'
4 AND CAPABILITY_NAME LIKE 'REFRESH%';
CAPABILITY_NAME P RELATED_TEXT MSGTXT
----------------------------- - ------------ ----------------------------------------------------------
REFRESH_COMPLETE Y
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML N MAX(T3.C1) mv uses the MIN or MAX aggregate functions
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
对于MAX/MIN的INSERT ONLY特性,没有什么太好的办法,这里介绍了一个例子,可以在一定程度上解决这个问题:
改造包含MAX或MIN的物化视图:http://yangtingkun.itpub.net/post/468/492826
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8183550/viewspace-666953/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8183550/viewspace-666953/