测试说明:
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;
来源: https://www.cnblogs.com/kingstarer/p/10865941.html