벌크 insert 빠르게 하는 방법

기본적으로 100만개의 레코드를 벌크 인서트 할 때 아래 방법을 사용 합니다.

create table LARGE_TBL (id number, value varchar2(50));   begin         for i in 1 .. 1000000 loop                 insert into large_tbl values (i,'foobar');         end loop;         commit; end; /   PL/SQL procedure successfully completed.   Elapsed: 00:00:40.53   call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute 1000000     15.67      15.42         11       2838    1040617     1000000 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total   1000001     15.67      15.42         11       2838    1040617     1000000

 

그럼 테이블 레코드 형식의 배열에 값을 저장하고 한번에 insert 하면,

declare         TYPE tbl_ins IS TABLE OF LARGE_TBL%ROWTYPE index by binary_integer;         w_ins tbl_ins; begin         for i in 1 .. 1000000 loop                 w_ins(i).id := i;                 w_ins(i).value := 'foobar';         end loop;                 forall i in 1 .. 1000000 insert into large_tbl values w_ins(i);         commit; end; /   PL/SQL procedure successfully completed.   Elapsed: 00:00:01.26   call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.49       0.50         11       4804      24093     1000000 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2      0.49       0.51         11       4804      24093     1000000

 

느낌이 오시나요?

sql 실행 횟수가 줄어 듭니다.