ITPub博客

首页 > IT职业 > IT生活 > Merge into 学习

Merge into 学习

原创 IT生活 作者:kl911 时间:2009-12-26 09:06:28 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-03

  • 博文量
    20
  • 访问量
    318514