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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/661919/viewspace-1029997/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/661919/viewspace-1029997/