Oracle 11g merge into log error及并行注意事项

小灰灰 2022-07-13 12:25 556阅读 0赞

最近有一个业务使用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;