oracle_insert into select、exists、merge into使用

oracle批量插入、更新 insert into select、exists、merge into

最近项目迭代，需要将原有的数据进行导入和更新，目标有两百万条数据，执行插入的时候跑了一个多小时，都没有跑完，接着点两下子，发现链接断了，，，

一、批量插入insert into select

目标从table_A表选取某列数据，导入到新建的table_B表

批量插入，原语句

思路：将table_A数据存入缓存表中，并根据序列号逐条插入table_B中

• 优点：有序

• 缺点：速度巨慢。。。

    --新建缓存表
    create table test_temp(
     seq varchar2(20),
     id varchar2(20)
    );
    create sequence test_temp_seq
    minvalue 1
    maxvalue 999999999999999999999999999
    start with 1
    increment by 1
    cache 100;
    commit;
  
    --数据迁移
    declare
    i number;
    begin
      for i in 1..2097838 loop 
        insert into test_temp(seq, id) values (test_temp_seq.Nextval, (select id from (select rownum as seq, id from table_A) tem where tem.seq = i) );
        insert into table_B(id, a, b, c)
        values ((select id from test_temp where seq=i), 'F', 'F', 'F');
      end loop;
    end;
  

批量插入，优化后语句

• 缺点：无序

• 优点：速度快

• 效率：182万数据，用了16s

    INSERT INTO table_B SELECT id, 'F', 'F', 'F' FROM table_A;
  

二、批量更新exists

• 目标：将table_A中的colA列更新，如果table_B中的有数据的话

• 效率：170万数据，用了30秒

    UPDATE table_A t_a SET t_a.status = 'T' WHERE EXISTS
    (
    	SELECT t_b.status
    	FROM table_b t_b
    	WHERE t_b.id = t_a.id
    );
  

三、批量导入merge into

• 目标：将table_B中的数据导入到table_A中

• 效率：813万条数据，用了104s(测试环境)

    MERGE INTO table_A t_A USING (
    	select t_B.id,  t_B.name, t_B.phone, t_B.age, t_B.adr from table_B t_B) t_C 
    ON (t_A.id = t_C.id) 
    WHEN MATCHED THEN 
      
    UPDATE SET 
    name = c.name,
    phone = c.phone,
    age = c.age,
    addr = c.addr