改造DISTINCT物化视图（一）

包含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/