oracle物化视图主键快速刷新,物化视图快速刷新与ORA-00001

今天上班收到告警邮件，是一个定时刷新物化视图的job引起的错误：

ORA-00001: unique constraint (NDMAIN.I_GPO_USR_USER_IDXU_CODE) violated

检查两边约束，发现完全一致，经过10046跟踪，发现根本原因。

下面举例重现错误：

SQL> create table test(id int,code varchar2(10));

Table created

SQL> alter table test add constraint pk_test primary key(id);

Table altered

SQL> alter table test add constraint un_test_code unique(code);

Table altered

SQL> create materialized view log on test;

Materialized view log created

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

1 row inserted

SQL> insert into test values(2,'code2');

1 row inserted

SQL> commit;

Commit complete

--创建快速刷新的物化视图

SQL> create materialized view mv_test refresh fast as select * from test;

Materialized view created

SQL> alter table mv_test add constraint un_mv_test_code unique(code);

Table altered

SQL> create materialized view log on mv_test;

Materialized view log created

--把code1和code2对换

SQL> update test set code='code3' where id=1;

1 row updated

SQL> update test set code='code1' where id=2;

1 row updated

SQL> update test set code='code2' where id=1;

1 row updated

SQL> commit;

Commit complete

SQL> select * from mlog$_test;

ID SNAPTIME$$  DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$

---------- ----------- --------- --------- --------------------

1 2008-3-31 2 U         U         04

2 2008-3-31 2 U         U         04

1 2008-3-31 2 U         U         04

--快速刷新物化视图

SQL> exec dbms_mview.refresh('mv_test')

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

ORA-12008: 实体化视图的刷新路径中存在错误

ORA-00001: 违反唯一约束条件 (SUK.UN_MV_TEST_CODE)

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2255

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2461

ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2430

ORA-06512: 在 line 1

从后台跟踪的10046trade文件可以看到下面的信息：

--原始trace内容

=====================

PARSING IN CURSOR #18 len=64 dep=2 uid=29 ct=6 lid=29 tim=10217293845 hv=3019709084 ad='204ef138'

UPDATE "SUK"."MV_TEST" SET "ID" = :1,"CODE" = :2 WHERE "ID" = :1

END OF STMT

PARSE #18:c=0,e=172,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,tim=10217293838

BINDS #18:

kkscoacd

Bind#0

acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

acflg=13 fl2=0001 frm=00 csi=00 siz=24 ff=0

kxsbbbfp=0789b014  bln=22  avl=02  flg=09

value=1 --ID的值

Bind#1

acdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00

acflg=13 fl2=0001 frm=01 csi=852 siz=32 ff=0

kxsbbbfp=0789b02c  bln=32  avl=05  flg=09

value="code2" --code的值

Bind#2

No oacdef for this bind.

--格式化后的trace内容

UPDATE "SUK"."MV_TEST" SET "ID" = :1,"CODE" = :2

WHERE

"ID" = :1

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.01       0.00          0          1          5           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.01       0.00          0          1          5           0

--只执行一次

分析以上内容可以得到如下结论：对ID=1的数据刷新只做了一次。

其实oracle这样做是很有道理的：如果oracle完全根据MLOG$记录的日志顺序刷新数据，对于那些被多次更新的数据，不得不刷新多次，这对效率影响很大。

实际上，MLOG$只是记录对基表的那些列执行了那些操作，并不记录更新前后的值，所以无法也没必要完全重演主表的数据更新历史，所以，对于相同主键的数据执行了多次更新，只需要根据主键和主表同步一次数据即可实现与主表的数据同步。

这个机制也是导致文中开头错误的根源：

mv_test刷新数据时，对于ID=1的数据，只执行一次数据刷新：update mv_test set name='code2' where id=1;

此时，mv_test中id=2的name='code2'(还没有变成code1)，这必然违反code上的唯一性约束。

为了避免这个错误，可以有以下解决方法：

1、如果是唯一性约束，把这个约束设置为延迟性约束(在提交时才检验数据的合法性)

2、如果是唯一性索引，则先创建一个普通索引，然后用这个索引创建一个延迟唯一性约束。

3、如果不考虑性能或者执行计划问题，则物化视图端的约束都可以去掉，再主表保证数据的合法性即可。

这个错误只会出现在快速刷新中，完全刷新是不存在这样的问题的。