最近有一个业务使用merge into报主键冲突的错误。各地市将数据汇总到省，省的数据是按照局编码分区，由于不同的地市，有主键相同的数据，应该是垃圾数据。

--初始化数据

drop table T_LIST purge;
drop table T_LIST1 purge;
?CREATE TABLE T_LIST
?(
? ? ID? NUMBER(7) NOT NULL PRIMARY KEY,
? ? CITY VARCHAR2(10),
? ? sort number
?)
?PARTITION BY LIST (CITY)
?(
? ? ? PARTITION P_BEIJING? VALUES ('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
?(
? ? ID? NUMBER(7) PRIMARY KEY,
? ? CITY VARCHAR2(10),
? ? sort number
?)
?PARTITION BY LIST (CITY)
?(
? ? ? PARTITION P_BEIJING? VALUES ('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_SQL? VARCHAR2(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 LIMIT? UNLIMITED';
?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$? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ID? ? ? ? CITY
?-------------------------------------------------- ---------- ----------
?ORA-00001: 违反唯一约束条件 (TEST.SYS_C0011594)? ? 3? ? ? ? ? SHANGHAI
?
ORA-00001: 违反唯一约束条件 (TEST.SYS_C0011594)? ? 2? ? ? ? ? GUANGZHOU

当然，在数据量大的情况下要使用并行，有可能会有问题，因为并行默认是直接路径读。

alter session enable parallel dml;
?declare
? ? Type city is table of varchar2(10);
? ? v_city city := city('BEIJING', 'SHANGHAI', 'GUANGZHOU');
? ? V_SQL? VARCHAR2(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_SQL? VARCHAR2(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;