你好,游客 登录 注册 搜索
背景:
阅读新闻

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

[日期:2017-01-20] 来源:Linux社区  作者:Linux [字体: ]

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

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-01/139745.htm

linux
相关资讯       Oracle并行  merge into 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款