物化檢視快速重新整理與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、如果不考慮效能或者執行計劃問題，則物化檢視端的約束都可以去掉，再主表保證資料的合法性即可。

這個錯誤只會出現在快速重新整理中，完全重新整理是不存在這樣的問題的。