Oracle 11g merge into log error及并行注意事项
最近有一个业务使用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, PARTITION P_GUANGZHOU VALUES ('GUANGZHOU') ); insert into T_LIST1 values(1,111); insert into T_LIST1 values(3,222); insert into T_LIST1 values(2,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','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; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |