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

所有类型的快速刷新物化视图都必须满足的条件：
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-692350/