Oracle的实体化视图(MVIEW)的深入研究之四

现在对 第一篇中基表进行移动(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)的实体化视图日志, 还是很重要的.