亚洲小说网,黑客网站,戚大江
测试说明：
merge是oracle提供的一种特殊的sql语法，非常适用于数据同步场景，即： （把a表数据插到b表，如果b表存在相同主键的记录则使用a表数据对b表进行更新） 数据同步的常规做法是先尝试插入，插入失败再进行更新，merge比这种常规做法效率高很多。 （特别是a与b表基本一致，同步时主键冲突比较多的情况，效率能相差10倍以上）
为了验证merge效率，我建了两张表，tab_test_c（初始化生成50000条记录）和tab_test_q（初始化从tab_test_c生成40000条记录）， 写了两个plsql脚本，分别将tab_test_c的数据同步到tab_test_q，看它们效率区别。
第一个脚本使用merge语法，第二个脚本使用常规先插入，出现主键冲突的操作。
测试结果：
使用merge语法的脚本同步数据耗时0.04秒，使用常规操作耗时14.77秒，效率差369倍
测试脚本：
set serveroutput on -- 启动计时 以便观察脚本执行时间 set timing on set time on
我要评论-- 数据初始化
drop table tab_test_c;
create table tab_test_c
(
c1 varchar2(512),
c2 varchar2(512),
c3 varchar2(512),
c4 varchar2(512),
c5 varchar2(512),
c6 varchar2(512),
c7 varchar2(512),
c8 varchar2(512),
c9 varchar2(512),
c10 varchar2(512)
);
declare
v_total number;
begin
v_total := 0;
loop
exit when v_total >= 50000;
for cur in (select owner, object_name, subobject_name, object_id, data_object_id, object_type,
created, last_ddl_time, timestamp from all_objects where rownum < 101)
loop
insert into tab_test_c values (cur.owner, cur.object_name, cur.subobject_name,
cur.object_id, cur.data_object_id,
cur.object_type, cur.created,
cur.last_ddl_time, cur.timestamp, v_total);
v_total := v_total + 1;
end loop;
end loop;
commit;
end;
/
-- 建唯一索引
select count(1) from tab_test_c;
create unique index uid_test_c_1 on tab_test_c(c10);
--初始化tab_test_q表数据，先从tab_test_c生成同步40000条数据，剩下10000条数据使用脚本同步过来 drop table tab_test_q; create table tab_test_q as select * from tab_test_c where rownum < 40001; create unique index uid_test_q_1 on tab_test_q(c10); -- 验证数据未同步成功 此时记录数差1000 select count(*) from tab_test_q;
-- 使用merge语法同步tab_test_c的数据到tab_test_q
declare
cursor cur is select * from tab_test_c;
type mergearray_t is table of tab_test_c % rowtype index by binary_integer;
mergearray mergearray_t;
begin
open cur;
loop
exit when cur % notfound;
fetch cur bulk collect into mergearray limit 16; -- 每次限十几条记录，不要占用太多内存 这个数字调大点效率会更高
begin
forall rw in 1 .. mergearray.count
merge into tab_test_q a
using (select mergearray(rw).c1 c1, mergearray(rw).c2 c2, mergearray(rw).c3 c3, mergearray(rw).c4 c4,
mergearray(rw).c5 c5, mergearray(rw).c6 c6, mergearray(rw).c7 c7, mergearray(rw).c8 c8,
mergearray(rw).c9 c9, mergearray(rw).c10 c10 from dual) b
on (a.c10 = b.c10)
when matched then
update set a.c1 = mergearray(rw).c1, a.c2 = mergearray(rw).c2, a.c3 = mergearray(rw).c3,
a.c4 = mergearray(rw).c4, a.c5 = mergearray(rw).c5,
a.c6 = mergearray(rw).c6, a.c7 = mergearray(rw).c7, a.c8 = mergearray(rw).c8,
a.c9 = mergearray(rw).c9
when not matched then
insert (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10) values(mergearray(rw).c1, mergearray(rw).c2,
mergearray(rw).c3, mergearray(rw).c4, mergearray(rw).c5, mergearray(rw).c6,
mergearray(rw).c7, mergearray(rw).c8, mergearray(rw).c9, mergearray(rw).c10);
-- dbms_output.put_line(mergearray.count);
exception
when others then
dbms_output.put_line('error1');
end;
end loop;
close cur;
commit;
end;
/
--耗时0.04秒 -- 验证数据同步成功 select count(*) from tab_test_q;
--初始化tab_test_q表数据，先从tab_test_c生成同步40000条数据，剩下10000条数据使用脚本同步过来 drop table tab_test_q; create table tab_test_q as select * from tab_test_c where rownum < 40001; create unique index uid_test_q_1 on tab_test_q(c10); -- 验证数据未同步成功 此时记录数差1000 select count(*) from tab_test_q;
-- 使用常规语法同步tab_test_c的数据到tab_test_q
begin
for cur in (select * from tab_test_c)
loop
begin
insert into tab_test_q(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10)
values(cur.c1, cur.c2, cur.c3, cur.c4, cur.c5, cur.c6, cur.c7, cur.c8, cur.c9, cur.c10);
exception
when dup_val_on_index then --唯一索引冲突时更新
update tab_test_q set c1 = cur.c1, c2 = cur.c2, c3 = cur.c3, c4 = cur.c4, c5 = cur.c5, c6 = cur.c6, c7 = cur.c7, c8 = cur.c8, c9 = cur.c9
where c10 = cur.c10;
when others then
dbms_output.put_line('error1');
end;
end loop;
commit;
end;
/
--耗时14.77秒 -- 验证数据同步成功 select count(*) from tab_test_q;
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
Oracle字符串函数length、upper、lower、initcap、 concat、instr、replace
网友评论