Merge into 學習

Merge在plsql的指令碼的出現頻率還是很高的，這裡做個簡單的小實驗，記錄一下我對merge的使用，以防將來遺忘. 另外還有一個有趣的現象，當merge和hint /*+ append */合起來用的時候，就會出現一個比較怪的現象， select count(*) from ... 結果為0， 但select * from 有行返回:

kl@k02> desc t1_1;
Name Type
------------------ -------- -----
A NUMBER
B VARCHAR2(128)


kl@k02> select * from t1_1;

A B
---------- ------------------
17868 JEFF
22868 CANDY
23508 CATHY
9800 MESSI

merge into t1_1 a
using
(.....)
on (....)
when matched (....)
when not matched (....)

merge into t1_1 a
using (select object_id, object_name from user_objects where object_type='TABLE' and rownum<10) s
on (a.a=s.object_id)
when matched then update set a.b='AAAAA'
when not matched then insert (a.a,a.b) values (s.object_id, s.object_name);

##########
不知道為什麼，想要模擬count(*) =0就是模擬不出來，以前出問題的DEV環境也10.2.0.3的，那張表的資料量也比較大，>=100w rows, 總之，如果遇到類似情況，大家可以考慮去掉hint append:

kl@k02> create unique index t1_1_ind on t1_1(a);

Index created.

kl@k02> truncate table t1_1;

Table truncated.

kl@k02> exec dbms_stats.gather_table_stats(user,'T1_1',cascade=>TRUE);

PL/SQL procedure successfully completed.

kl@k02> merge /*+ append */ into t1_1 a
2 using (select object_id, object_name from dba_objects where object_type='TABLE') s
3 on (a.a=s.object_id)
4 when matched then update set a.b='AAAAA'
5 when not matched then insert (a.a,a.b) values (s.object_id, s.object_name);

789 rows merged.

kl@k02> select count(*) from t1_1;
select count(*) from t1_1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

kl@k02> commit;

Commit complete.

kl@k02> select count(*) from t1_1;

COUNT(*)
----------
789 ---- (遇到問題是這個值為0)

正常的update不會遇到這個ORA-12838: cannot read/modify an object after modifying it in parallel 錯誤.
所以當要做merge時，還是要慎用 APPEND Hint!

kl@k02> update /*+ append */ t1_1 set b='AAAAAAA';

789 rows updated.

kl@k02> select count(*) from t1_1;

COUNT(*)
----------
789[@more@]