oracle 开并行merge,Oracle 11g merge into log error¼°²¢ÐÐ×¢ÒâÊÂÏî

¼°²¢ÐÐ×¢ÒâÊÂÏ×î½üÓÐÒ»¸öÒµÎñʹÓÃmerge into±¨Ö÷¼ü³åÍ»µÄ´íÎ󡣸÷µØÊн«Êý¾Ý»ã×ܵ½Ê¡£¬Ê¡µÄÊý¾ÝÊÇ°´ÕÕ¾Ö±àÂë·ÖÇø£¬ÓÉÓÚ²»Í¬µÄµØÊУ¬ÓÐÖ÷¼üÏàͬµÄÊý¾Ý£¬Ó¦¸ÃÊÇÀ¬»øÊý¾Ý¡£

--³õʼ»¯Êý¾Ý

drop table T_LIST purge;

drop table T_LIST1 purge;

CREATE TABLE T_LIST

(

IDNUMBER(7) NOT NULL PRIMARY KEY,

CITY VARCHAR2(10),

sort number

)

PARTITION BY LIST (CITY)

(

PARTITION P_BEIJINGVALUES ('BEIJING') ,

PARTITION P_SHANGHAI VALUES ('SHANGHAI'),

PARTITION P_GUANGZHOU VALUES ('GUANGZHOU')

);

insert into T_LIST values(1,'BEIJING',11);

insert into T_LIST values(2,'SHANGHAI',22);

insert into T_LIST values(3,'GUANGZHOU',33);

commit;

CREATE TABLE T_LIST1

(

IDNUMBER(7) PRIMARY KEY,

CITY VARCHAR2(10),

sort number

)

PARTITION BY LIST (CITY)

(

PARTITION P_BEIJINGVALUES ('BEIJING') ,

PARTITION P_SHANGHAI VALUES ('SHANGHAI'),

PARTITION P_GUANGZHOU VALUES ('GUANGZHOU')

);

insert into T_LIST1 values(1,'BEIJING',111);

insert into T_LIST1 values(3,'SHANGHAI',222);

insert into T_LIST1 values(2,'GUANGZHOU',333);

commit;

--½¨Á¢´íÎóÈÕÖ¾±í

EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T_LIST', 'T_ERROR_LOG');

declare

Type city is table of varchar2(10);

v_city city := city('BEIJING', 'SHANGHAI', 'GUANGZHOU');

V_SQLVARCHAR2(4000) :=

'merge into T_LIST a using(select * from T_LIST1 where CITY = :1) b

on (a.id =b.id and a.city =b.city and a.city = :2)

when matched then

update set a.sort=b.sort

when not matched then

insert values(b.id,b.city,b.sort) LOG ERRORS INTO T_ERROR_LOG REJECT LIMITUNLIMITED';

begin

for i in v_city.first .. v_city.last loop

execute immediate V_SQL using v_city(i),v_city(i);

end loop;

commit;

end;

SQL> select ORA_ERR_MESG$,id,city from T_ERROR_LOG;

ORA_ERR_MESG$IDCITY

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

ORA-00001:Î¥·´Î¨Ò»Ô¼ÊøÌõ¼þ(TEST.SYS_C0011594)3SHANGHAI

ORA-00001:Î¥·´Î¨Ò»Ô¼ÊøÌõ¼þ(TEST.SYS_C0011594)2GUANGZHOU

µ±È»£¬ÔÚÊý¾ÝÁ¿´óµÄÇé¿öÏÂҪʹÓò¢ÐУ¬ÓпÉÄÜ»áÓÐÎÊÌ⣬ÒòΪ²¢ÐÐĬÈÏÊÇÖ±½Ó·¾¶¶Á¡£

alter session enable parallel dml;

declare

Type city is table of varchar2(10);

v_city city := city('BEIJING', 'SHANGHAI', 'GUANGZHOU');

V_SQLVARCHAR2(4000) :=

'merge /*+parallel(2) */ into T_LIST a using(select * from T_LIST1 where CITY = :1) b

on (a.id =b.id and a.city =b.city and a.city = :2)

when matched then

update set a.sort=b.sort

when not matched then

insert values(b.id,b.city,b.sort) LOG ERRORS INTO T_ERROR_LOG REJECT LIMIT UNLIMITED';

begin

for i in v_city.first .. v_city.last loop

execute immediate V_SQL using v_city(i),v_city(i);

commit;

end loop;

end;

ORA-12801:²¢Ðвéѯ·þÎñÆ÷P000Öз¢³ö´íÎóÐźÅ

ORA-00001:Î¥·´Î¨Ò»Ô¼ÊøÌõ¼þ(TEST.SYS_C0011594)

ORA-06512:ÔÚline 14

½â¾ö·½°¸ÊÇ£º¼ÓÒ»¸önoappendµÄhint£¬²¢ÐÐÒ²¿ÉÒÔ¸ÄΪmerge /*+parallel(a)parallel(b) noappend*/ into.

declare

Type city is table of varchar2(10);

v_city city := city('BEIJING', 'SHANGHAI', 'GUANGZHOU');

V_SQLVARCHAR2(4000) :=

'merge /*+parallel(2) noappend*/ into T_LIST a using(select * from T_LIST1 where CITY = :1) b

on (a.id = b.id and a.city =b.city and a.city = :2)

when matched then

update set a.sort=b.sort

when not matched then

insert values(b.id,b.city,b.sort) LOG ERRORS INTO T_ERROR_LOG REJECT LIMIT UNLIMITED';

begin

for i in v_city.first .. v_city.last loop

execute immediate V_SQL using v_city(i),v_city(i);

commit;

end loop;

end;