物化视图同步问题
来源:互联网 发布:无硬件基础学linux 编辑:程序博客网 时间:2024/05/15 19:48
当物化视图或物化视图的基表(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.
- 物化视图同步问题
- 物化视图的刷新问题
- 用物化视图同步两边数据库
- 物化视图快速刷新的一个问题
- 物化视图引起的性能问题
- 物化视图
- 物化视图
- 物化视图
- 物化视图
- 物化视图
- 物化视图
- 物化视图
- 物化视图
- 物化视图
- 物化视图
- 物化视图
- 物化视图
- 物化视图
- Struts2中的拦截器
- apk签名方法
- 在oracle中将长sql输出,类似sql server的print
- Oracle中LOB 处理
- poj1568 极大极小搜索
- 物化视图同步问题
- Delphi与Oracle中BLOB字段的操作
- 软件测试工作内容
- XPathDocument 读取带命名空间的节点
- csdn管理员脑子怎么了?不让我把文章发布到首页???
- photoshop使用小技巧
- 如何在C#.net中自定义和使用特性
- Emit进阶 创建自定义委托Delegate
- ThreadLocal与synchronized