ITPub博客

可更新的物化视图

原创 Oracle 作者:jeanron100 时间:2013-11-23 18:20:54 0 删除 编辑
快速刷新

可更新的物化视图


SQL> exec dbms_mview.refresh('MV_T','F');
BEGIN dbms_mview.refresh('MV_T','F'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "JEANRON"."T" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1

create materialized view before materialized view log

exec dbms_mview.refresh('MV_T','C');

使用primary key

使用rowid.
如果使用rowid,需要考虑是否适合做快速刷新。如果有频繁的truncate之类的操作,rowid会发生变化。快速刷新就不适用了。
SQL> select rowid,object_id from t;


ROWID               OBJECT_ID
------------------ ----------
AAASx8AAGAAAi+NAAA        258
AAASx8AAGAAAi+NAAB        259
AAASx8AAGAAAi+NAAC        311
AAASx8AAGAAAi+NAAD        313
AAASx8AAGAAAi+NAAE        314
AAASx8AAGAAAi+NAAF        316
AAASx8AAGAAAi+NAAG        317
AAASx8AAGAAAi+NAAH        319
AAASx8AAGAAAi+NAAI        605

9 rows selected.

SQL> create table t2 as select *from t;
Table created.

SQL> truncate table t;
Table truncated.

SQL> insert into t select *from t2;
9 rows created.

SQL> commit;
Commit complete.

SQL> select rowid,object_id from t;
ROWID               OBJECT_ID
------------------ ----------
AAASyDAAGAAAi+aAAA        258
AAASyDAAGAAAi+aAAB        259
AAASyDAAGAAAi+aAAC        311
AAASyDAAGAAAi+aAAD        313
AAASyDAAGAAAi+aAAE        314
AAASyDAAGAAAi+aAAF        316
AAASyDAAGAAAi+aAAG        317
AAASyDAAGAAAi+aAAH        319
AAASyDAAGAAAi+aAAI        605

9 rows selected.



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

请登录后发表评论 登录
全部评论
技术文章每天更新,阵地已转移到微信公众号端。 公众号:jianrong-notes

注册时间:2012-05-14

  • 博文量
    1667
  • 访问量
    14215842