包含复杂查询的快速刷新的物化视图

今天看到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/