改造DISTINCT物化视图（二）

包含DISTINCT的物化视图无法快速刷新，但是有些情况可以转化为可快速刷新的物化视图。

这一篇介绍COUNT或SUM聚集函数中的DISTINCT操作。

改造DISTINCT物化视图（一）：http://yangtingkun.itpub.net/post/468/492698

 

 

如果物化视图包括COUNT(DISTINCT COLUMN)、SUM(DISTINCT COLUMN)或者AVG(DISTINCT COLUMN)，则物化视图也是无法快速刷新的：

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, OBJECT_ID)
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST
  2  AS SELECT OWNER,
  3  OBJECT_TYPE,
  4  COUNT(*) COUNT,
  5  COUNT(OBJECT_ID) COUNT_ID,
  6  SUM(DISTINCT OBJECT_ID) SUM_ID
  7  FROM T
  8  GROUP BY OWNER, OBJECT_TYPE;
FROM T
     *
ERROR at line 7:
ORA-12015: cannot create a fast refresh materialized view from a complex query

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST
  2  AS SELECT OWNER,
  3  OBJECT_TYPE,
  4  COUNT(*) COUNT,
  5  COUNT(OBJECT_ID) COUNT_ID,
  6  SUM(OBJECT_ID) SUM_ID
  7  FROM T
  8  GROUP BY OWNER, OBJECT_TYPE;

Materialized view created.

对于这种情况，可以将一个物化视图转化为两个物化视图：

SQL> CREATE MATERIALIZED VIEW MV_T_INTER REFRESH FAST AS
  2  SELECT OWNER,
  3  OBJECT_TYPE,
  4  OBJECT_ID,
  5  COUNT(*) COUNT
  6  FROM T
  7  GROUP BY OWNER, OBJECT_TYPE, OBJECT_ID;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW LOG ON MV_T_INTER
  2  WITH ROWID, SEQUENCE (OWNER, OBJECT_TYPE, OBJECT_ID) 
  3  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T1 REFRESH FAST AS
  2  SELECT OWNER,
  3  OBJECT_TYPE,
  4  COUNT(*) COUNT,
  5  COUNT(OBJECT_ID) COUNT_ID,
  6  SUM(OBJECT_ID) SUM_ID
  7  FROM MV_T_INTER
  8  GROUP BY OWNER, OBJECT_TYPE;

Materialized view created.

第二个物化视图和前面创建失败的物化视图是等价的。同样AVG(DISTINCT COLUMN)也可以在这个物化视图中获得。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-616691/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-616691/