Oracle 11g merge into log error及并行注意事项
来源:互联网 发布:淘宝店铺首页公告大全 编辑:程序博客网 时间:2024/05/21 15:38
最近有一个业务使用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 11g merge into log error及并行注意事项
- Oracle使用Merge into 注意事项
- ORACLE 10 g的 merge into 用法
- 用wm_concat合并行及merge into更新
- oracle merge into用法及例子
- oracle 11g 并行DML
- oracle 11g 并行DDL
- Oracle 11g 并行DML
- Oracle select --merge into:
- oracle merge into 测试
- oracle merge into
- oracle merge into 实例
- Oracle 之 MERGE INTO
- Oracle:merge into
- Oracle数据库merge into
- oracle merge into
- oracle merge into
- oracle merge into用法
- string.xml中的一些格式化符号
- C# Object.GetType()获取对象的类类型/获取类的类型
- 用python发送邮件
- CF卡镜像备份及恢复系统
- android switch 的终极解决方案
- Oracle 11g merge into log error及并行注意事项
- 仿陌陌发送语音控件
- android面试题
- Spring中bean的作用域
- ios小联系
- 深入理解Java中的final关键字
- Java六大设计原则-开闭原则
- php 序列化 反序列化 __sleep __wakeup
- VMware虚拟机三种网络模式详解 Bridged(桥接模式)