暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Oracle10g Materialized View enhanced

原创 eygle 2005-12-13
699

今天Kamus在Gtalk上让我帮忙测试,是关于物化视图的:



  1. truncate分区以后,物化视图快速刷新出错

  2. drop分区以后,物化视图快速刷新出错


测试Oralce9i的情况:









SQL> create table T_PART
  2  (
  3    C1 NUMBER,
  4    C2 NUMBER,
  5    C3 NUMBER
  6  )
  7  partition by range (C2)
  8  (
  9    partition T_P2 values less than (20)
 10  ,
 11    partition T_P3 values less than (30)
 12  )
 13  ;

Table created.
SQL> insert into t_part values(1,2,3);

1 row created.

SQL> insert into t_part values(1,25,3);

1 row created.

SQL> insert into t_part values(1,18,3);

1 row created.

SQL> commit;

Commit complete.

SQL> create materialized view log on t_part with rowid;

Materialized view log created.

SQL> create materialized view mv_t_part refresh with rowid as select * from t_part;

Materialized view created.

SQL> select * from t_part;

        C1         C2         C3
---------- ---------- ----------
         1          2          3
         1         18          3
         1         25          3

SQL> select * from mv_t_part;

        C1         C2         C3
---------- ---------- ----------
         1          2          3
         1         18          3
         1         25          3

SQL> alter table t_part truncate partition t_p2;

Table truncated.

SQL> exec dbms_mview.refresh('mv_t_part','f');
BEGIN dbms_mview.refresh('mv_t_part','f'); END;

*
ERROR at line 1:
ORA-32313: REFRESH FAST of "SYS"."MV_T_PART" unsupported after PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1


这里出现错误。


ORA-32313 REFRESH FAST of "string"."string" unsupported after PMOPs



Cause: A Partition Maintenance Operation (PMOP) has been performed on a detail table, and the specified materialized view does not support fast refersh after PMOPs.



Action: Use REFRESH COMPLETE. You can determine why your materialized view does not support fast refresh after PMOPs using the DBMS_MVIEW.EXPLAIN_MVIEW() API.


再来测试Oracle10g的:


「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论