现在对第一页中基表进行移动(Move)操作, 会发现不能进行快速刷新, 必须进行全部(Complete)刷新才行. 如下所示:
SQL> ALTER TABLE T_MVLOG MOVE;
Table altered.
SQL> EXEC DBMS_MVIEW.REFRESH('MV_T_MVLOG','FAST');
BEGIN DBMS_MVIEW.REFRESH('MV_T_MVLOG','FAST'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "ANYSQL"."T_MVLOG" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1
为什么失败, 可以仔细一下基于ROWID的MVIEW LOG被刷新的过程, 当基表移动后, ROWID的值变了, 因此不能再继续支持主表的UPDATE/DELETE这样的操作了. 下面将这个实体化视图重新定义成基于主键(Primary Key)的, 首先删除现有的实体化视图及日志, 再为表加一个主键, 重新创建实体化视图 :
SQL> alter table t_mvlog modify col1 not null;
Table altered.
SQL> alter table t_mvlog add primary key (col1);
Table altered.
SQL> create materialized view log on t_mvlog with primary key, sequence;
Materialized view log created.
SQL> CREATE MATERIALIZED VIEW MV_T_MVLOG
2 REFRESH FAST WITH PRIMARY KEY
3 AS SELECT ROWID R_ID, A.* FROM T_MVLOG A;
Materialized view created.
再对基表作移动(Move)操作后, 就可以进行增量刷新了.
SQL> alter table t_mvlog move;
Table altered.
SQL> exec dbms_mview.refresh('MV_T_MVLOG','FAST');
PL/SQL procedure successfully completed.
选择基于ROWID还是基于主键(Primary Key)的实体化视图日志, 还是很重要的.
