包含DISTINCT的物化视图无法快速刷新,但是有些情况可以转化为可快速刷新的物化视图。
这一篇介绍SELECT语句中的DISTINCT操作。
其实这种情况最简单,因为如果只是包含一个DISTINCT操作,那么完全可以转换为GROUP BY语句:
SQL> CREATE TABLE T AS
2 SELECT *
3 FROM DBA_OBJECTS;
Table created.
SQL> CREATE MATERIALIZED VIEW LOG ON T
2 WITH ROWID, SEQUENCE (OWNER, OBJECT_TYPE)
3 INCLUDING NEW VALUES;
Materialized view log created.
SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST
2 AS SELECT DISTINCT OWNER, OBJECT_TYPE
3 FROM T;
FROM T
*
ERROR at line 3:
ORA-12015: cannot create a fast refresh materialized view from a complex query
SQL> CREATE MATERIALIZED VIEW MV_T_COMPLETE
2 AS SELECT DISTINCT OWNER, OBJECT_TYPE
3 FROM T;
Materialized view created.
显然包含DISTINCT的物化视图只能完全刷新而不能快速刷新,不过这种物化视图可以很轻松的改成下面的写法:
SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST
2 AS SELECT OWNER, OBJECT_TYPE, COUNT(*) COUNT
3 FROM T
4 GROUP BY OWNER, OBJECT_TYPE;
Materialized view created.
物化视图使用GROUP BY的写法和使用DISTINCT的写法是等价的,而且可以使用快速刷新。唯一不同是多了一个COUNT(*)的字段,而一般情况下,物化视图的额外列没有多大影响。如果基表不包括UPDATE和DELETE,只有INSERT操作,那么物化视图中可以去掉COUNT(*)列。
SQL> CREATE MATERIALIZED VIEW MV_T1 REFRESH FAST
2 AS SELECT OWNER, OBJECT_TYPE
3 FROM T
4 GROUP BY OWNER, OBJECT_TYPE;
Materialized view created.
SQL> DELETE T WHERE ROWNUM = 1;
1 row deleted.
SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_MVIEW.REFRESH('MV_T1')
BEGIN DBMS_MVIEW.REFRESH('MV_T1'); END;
*
ERROR at line 1:
ORA-32314: REFRESH FAST of "TEST"."MV_T1" unsupported after deletes/updates
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429
ORA-06512: at line 1
可以看到如果不包括COUNT(*),则物化视图不支持UPDATE和DELETE语句的快速刷新。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-616575/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-616575/