物化视图同步问题

当物化视图或物化视图的基表（reprebuilt table）上有唯一索引或约束时刷新容易出错！
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (TABLE_NAME) violated
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
下面实例说明及解决方案！
SQL> create unique index uk_test_01 on test (f1, f2);

Index created

SQL> insert into test values(1,1) ;

1 row inserted

SQL> insert into test values(1,2) ;

1 row inserted

SQL> commit;

Commit complete

SQL> create materialized view log on test
2 with sequence, rowid including new values ;

Materialized view log created

SQL> create materialized view mv_test
2 refresh fast on DEMAND
3 with ROWID
4 AS
5 select * from test ;

Materialized view created

SQL> create unique index uk_mv_test on mv_test(f1,f2) ;

Index created

SQL> update test
2 SET
3 f2 = decode(f2, 1, 2, 2, 1)
4 WHERE
5 f1 in (1 , 2);

2 rows updated

SQL> commit;

Commit complete

SQL> exec dbms_mview.refresh('mv_test' , 'f') ;

begin dbms_mview.refresh('mv_test' , 'f'); end;

ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (UK_MV_TEST) violated
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

SQL> drop index uk_mv_test;

Index dropped

SQL> alter table mv_test add constraint uk_mv_test unique (f1,f2)deferrable;

Table altered

SQL> exec dbms_mview.refresh('mv_test' , 'f') ;

PL/SQL procedure successfully completed

出错的原因是物化视图刷新是根据物化视图日志单条更新的，不是以一组一组的更新的，所以会出错，加deferrable可以避免这个错误，下面引用一段E文做参考:
Note particularly thedeferrable– when we do our fast refresh there is a moment where we have to have a duplicate row in the table – either we change the 1 to a 2 first, or we change the 2 to a 1 first – the refresh updates one row at a time, it doesn’t do an array update. By making the constraint deferrable we get a non-unique index protecting the constraint, and Oracle only re-checks the validity of the constraint on thecommit, not for each update statement. I believe there is something in the data warehouse manuals about making unique (and primary key) constraints onmaterialized viewsdeferrablefor exactly this reason.