ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 改造DISTINCT物化视图(二)

改造DISTINCT物化视图(二)

原创 Linux操作系统 作者:yangtingkun 时间:2009-10-15 23:18:32 0 删除 编辑

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

这一篇介绍COUNTSUM聚集函数中的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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10405808