oracle基于3种方法的大数据量插入更新
过程插入更新的3种方法：
a、逐条检查插入或更新，同时执行插入或更新
b、逐条merge into（逐条是为了记录过程日志与错误信息）
c、基于关联数组的检查插入、更新，通过forall批量sql执行
以下为模拟步骤：
1、创建模拟大表，数据量1亿不分区
create table big_table as SELECT ROWNUM RN,'A' A,'B' B,'C' C,'D' D FROM ( SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 1000) T1， ( SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 1000) T2， ( SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 100) T3;
说明：用connect by方法创建测试大表相对较快
2、给大表加上一个主键，因为插入更新基于主键
alter table BIG_TABLE2 add constraint PK_RN2 primary key (RN)
3、创建模拟的更新表，数据量20万
CREATE TABLE UP_TABLE ( RN NUMBER,A VARCHAR2(20),B VARCHAR2(20),C VARCHAR2(20),D VARCHAR2(20))
4、给更新表加数据，10万更新，10万插入，写的相对烦人些。。。代码不贴出来了
5、日志表结构
6、第一种方法的过程如下：
create or replace procedure sp_ins_up is v_bz char(1);--插入更新标志 v_stime pls_integer;--过程起始时间 i pls_integer := 0; --执行数量 v_up pls_integer := 0;--更新数量 v_ins pls_integer := 0;--插入数量 v_time pls_integer;--循环当前循环起始时间 begin v_stime := dbms_utility.get_time();--过程起始时间 v_time := dbms_utility.get_time();--当前循环起始时间 for rec in (select * from up_table) loop--开始循环 i := i + 1; begin--当前数据执行插入更行判断 select '1' into v_bz from big_table1 a where a.rn = rec.rn; exception when no_data_found then v_bz := 0; end; if v_bz = 1 then--更新 v_up := v_up + 1; update big_table1 a set a.a = rec.a, a.b = rec.b, a.c = rec.c, a.d = rec.d where a.rn = rec.rn; else--插入 v_ins := v_ins + 1; insert into big_table1 values (rec.rn, rec.a, rec.b, rec.c, rec.d); end if; if mod(i, 5000) = 0 then--一定数量提交 insert into INS_UP_LOG values ('sp_ins_up', round((dbms_utility.get_time - v_time) / 100, 2), v_up + v_ins, v_up, v_ins); commit; v_up := 0; v_ins := 0; v_time := dbms_utility.get_time; end if; end loop; commit; insert into ins_up_log--过程总时间记录 values ('sp_ins_up', round((dbms_utility.get_time - v_stime) / 100, 2), '', '', ''); commit; end sp_ins_up;
7、merge into方法过程如下：
create or replace procedure sp_merge is v_stime pls_integer; --过程起始时间 i pls_integer := 0; --执行数量 v_time pls_integer; --循环当前循环起始时间 v_rn up_table.rn%type; --缓存值 v_errmsg varchar2(500); --当前错误信息 begin v_stime := dbms_utility.get_time(); v_time := dbms_utility.get_time(); for rec in (select * from up_table) loop i := i + 1; v_rn := rec.rn; --缓存当前主键 merge into big_table3 t --merge into using (select * from up_table where rn = rec.rn) a on (t.rn = a.rn) when matched then update set t.a = a.a, t.b = a.b, t.c = a.c, t.d = a.d when not matched then insert values (a.rn, a.a, a.b, a.c, a.d); v_errmsg := sqlerrm; if mod(i, 5000) = 0 then insert into ins_up_log values ('sp_merge', round((dbms_utility.get_time - v_time) / 100, 2), i, i / 2, --此处插入数量不必在意 i / 2); commit; v_time := dbms_utility.get_time(); end if; end loop; insert into ins_up_log --过程总时间记录 values ('sp_merge', round((dbms_utility.get_time - v_stime) / 100, 2), i, '', ''); commit; exception when others then insert into ins_up_err values ('sp_merge', v_rn, v_errmsg); end sp_merge;
8、forall方法，结合第一种方法的判断方式：
create or replace procedure sp_forall_new is type table_type is table of up_table%rowtype index by pls_integer; --增量表缓存 tab_all table_type; --增量表全 tab_up table_type; --增量表-更新 tab_ins table_type; --增量表-插入 v_bz char(1); --存在标志 cursor cur_up is select * from up_table; --更新表游标 v_stime pls_integer; --过程开始时间 v_time pls_integer; --每次循环开始时间 v_num_ins pls_integer := 0; --每次循环插入数量 v_num_up pls_integer := 0; --每次循环更新数量 v_num number; --数量缓存值 v_err_msg number; --forall中错误信息 begin v_stime := dbms_utility.get_time(); --过程开始 open cur_up; --打开游标 loop --开始循环游标 v_time := dbms_utility.get_time(); --当前循环开始时间 v_num_ins := 0; --每次循环置零 v_num_up := 0; --每次循环置零 fetch cur_up bulk collect into tab_all limit 5000; --批量插入缓存表，每次限定5000条 exit when tab_all.count = 0; --当缓存表中无数据，退出循环 -- for i in 1 .. tab_all.count loop --此循环功能：判断是否存在目标表中 begin select '1' into v_bz from big_table2 t where t.rn = tab_all(i).rn; --存在 exception when no_data_found then v_bz := '0'; --不存在 end; -- if v_bz = '1' then --存在 v_num_up := v_num_up + 1; --更新数值+1 tab_up(v_num_up) := tab_all(i); --复制到更新缓存表 else --不存在 v_num_ins := v_num_ins + 1; --插入数值+1 tab_ins(v_num_ins) := tab_all(i); --复制到插入缓存表 end if; -- end loop; -- --批量SQL，且在批量执行中错误的信息记录在sql%bulk_exceptions缓存表中 if tab_up.count <> 0 then forall i in tab_up.first .. tab_up.last save exceptions update big_table2 t set t.a = tab_up(i).a, t.b = tab_up(i).b, t.c = tab_up(i).c, t.d = tab_up(i).d where t.rn = tab_up(i).rn; for i in 1 .. sql%bulk_exceptions.count loop v_num := sql%bulk_exceptions(i).error_index; v_err_msg := sqlerrm(-sql%bulk_exceptions(i).error_code); insert into ins_up_err values ('sp_forall', tab_up(v_num).rn, v_err_msg); end loop; commit; end if; --更新批量结束 --批量SQL，且在批量执行中错误的信息记录在sql%bulk_exceptions缓存表中 if tab_ins.count <> 0 then forall i in tab_ins.first .. tab_ins.last save exceptions insert into big_table2 values (tab_ins(i).rn, tab_ins(i).a, tab_ins(i).b, tab_ins(i).c, tab_ins(i).d); for i in 1 .. sql%bulk_exceptions.count loop v_num := sql%bulk_exceptions(i).error_index; v_err_msg := sqlerrm(-sql%bulk_exceptions(i).error_code); insert into ins_up_err values ('sp_forall', tab_ins(v_num).rn, v_err_msg); end loop; commit; end if; --插入批量结束 insert into ins_up_log --记录日志 values ('sp_forall', round((dbms_utility.get_time - v_time) / 100, 2), 5000, v_num_up, v_num_ins); commit; --清空当前循环插入、更新缓存表数据（不清空，下次循环重复执行） tab_up.delete; tab_ins.delete; end loop; close cur_up; --关闭游标 insert into ins_up_log --过程总时间记录 values ('sp_forall', round((dbms_utility.get_time - v_stime) / 100, 2), '', '', ''); commit; end sp_forall_new;
最后结果：同样对一亿的表插入更新20万条数据,多次执行平均时间
sp_merge 14.48秒
sp_forall 6.63秒
sp_ins_up 44.33秒
从每5000条提交一次的时间可以得出来，forall最稳定，其次merge稍有起伏，手动执行插入更新浮动最大从0.3秒到6.9秒不等。
效率方面：forall优势明显，其次merge也不差，手动插入更新最慢且不稳定
代码方面：merge into与手动插入更新 相对简洁，forall就比较复杂
扩展性：手动插入更新可以加上许多业务性功能，forall方式目前oracle也提供了相当多的函数用于数据处理，所以次之，merge into就个人而言，相对笨重一些了。
