ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【物化视图】速刷新物化视图都必须满足的条件

【物化视图】速刷新物化视图都必须满足的条件

原创 Linux操作系统 作者:杨奇龙 时间:2011-04-12 17:52:04 0 删除 编辑
所有类型的快速刷新物化视图都必须满足的条件:
1.物化视图不能包含对不重复表达式的引用,如SYSDATE和ROWNUM;
2.物化视图不能包含对LONG和LONG RAW数据类型的引用。但是从测试环境上看支持对raw类型的引用。

1.不能包括GROUP BY语句或聚集操作;
yang@rac1>create materialized view mv_yang_err refresh fast on commit with rowid
  2  as select id ,count(1)  from yang_a group by id;
as select id ,count(1)  from yang_a group by id
                             *
第 2 行出现错误:
ORA-32401: "YANG"."YANG_A" 上的实体化视图日志没有新值

yang@rac1>create materialized view mv_yang_pk refresh fast on commit
  2  as select id ,count(1) from yang_pk group by id;
as select id ,count(1) from yang_pk group by id
                            *
ERROR at line 2:
ORA-32401: materialized view log on "YANG"."YANG_PK" does not have new values

2.如果在WHERE语句中包含外连接,那么唯一约束必须存在于连接中内表的连接列上:
yang@rac1>drop materialized view mv_fact_err;
Materialized view dropped.
yang@rac1>alter table fact drop constraint fact_fk_aid;
Table altered.
yang@rac1>alter table yang_a drop primary key ;
Table altered.
yang@rac1>create materialized view mv_fact_err refresh fast on commit as
  2    select f.rowid frid,a.rowid arid,b.rowid brid,f.id,
  3    a.name aname,b.name bname ,val
  4    from fact f ,yang_a a,yang_b b
  5    where f.aid=a.id(+)
  6    and f.bid=b.id;
Materialized view created.
物化视图可以被建立,看来条件不成立。
3.如果不包含外连接,那么WHERE语句没有限制,如果包含外连接,那么WHERE语句中只能使用AND连接,并且只能使用“=”操作:
yang@rac1>create materialized view mv_fact_err refresh fast on commit as
  2    select f.rowid frid,a.rowid arid,b.rowid brid,f.id,
  3    a.name aname,b.name bname ,val
  4    from fact f ,yang_a a,yang_b b
  5    where f.aid=a.id(+)
  6    and f.bid != b.id;

Materialized view created.
此条件不成立。

4.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中:
yang@rac1>create materialized view mv_fact_err refresh fast on commit as
  2    select b.rowid brid,f.id,
  3    a.name aname,b.name bname ,val
  4    from fact f ,yang_a a,yang_b b
  5    where f.aid=a.id(+)
  6    and f.bid != b.id;
  from fact f ,yang_a a,yang_b b
       *
ERROR at line 4:
ORA-12052: cannot fast refresh materialized view YANG.MV_FACT_ERR

5.FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。
yang@rac1>create materialized view mv_fact_err refresh fast on commit as
  2    select b.rowid brid,f.id,
  3    a.name aname,b.name bname ,val
  4    from fact f ,yang_a a,yang_b b
  5    where f.aid=a.id(+)
  6    and f.bid != b.id;
  from fact f ,yang_a a,yang_b b
       *
ERROR at line 4:
ORA-12052: cannot fast refresh materialized view YANG.MV_FACT_ERR

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

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7717783