[ORACLE] BULK INSERT TEST

Bulk insert TEST 시나리오

1. create table

create user test_user01 identified by "test01#$";

grant connect, resource to test_user01;

drop table test_user01.test01;
create table test_user01.test01 
(id1 number, id2 number, name varchar2(10),  
date1 varchar2(8), date2 varchar2(8), date3 date, 
phone varchar2(13), price number, qty number, 
test1 number,  test2 varchar2(5), test3 varchar2(4)
)
nologging;


drop table test_user01.test02;
create table test_user01.test02 
(id1 number, id2 number, name varchar2(10),  
date1 varchar2(8), date2 varchar2(8), date3 date, 
phone varchar2(13), price number, qty number, 
test1 number,  test2 varchar2(5), test3 varchar2(4)
)
nologging;

2. test_user01.test01 Table에 data insert [100만건]

DECLARE
TYPE tbl_ins IS TABLE OF test_user01.test01%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..10 LOOP
FOR i IN 1..100000 LOOP 
   w_ins(i).id1   := i;
   w_ins(i).id2   := i||ceil(dbms_random.value(1, 10000000));
   w_ins(i).name  := dbms_random.string('x',5);
   w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
   w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
   w_ins(i).date3 := to_date(round(dbms_random.value(2010,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
   w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
   w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
   w_ins(i).qty   := ceil(dbms_random.value(1, 10));
   w_ins(i).test1 := 1234;
   w_ins(i).test2 := 'SQLP';
   w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..100000 INSERT INTO test_user01.test01 VALUES w_ins(i);
   COMMIT;
END LOOP;
END;
/

The source was executed successfully
Execution Time : 00:01:13.687

3. bulk insert 하는 procedure 생성

-. 4만개가 하나의 excution으로 실행되며 약 3초걸림 (100만개 데이터 insert)

CREATE OR REPLACE PROCEDURE TEST_USER01.BULT_INSERT_TEST01 AS
  TYPE Gv_DATA_LIST IS TABLE OF TEST_USER01.TEST01%ROWTYPE;  -- TYPE 선언
  Gv_DATA      Gv_DATA_LIST;                    -- TYPE변수 할당
  CUR1         SYS_REFCURSOR;                   -- 커서변수 선언
  Gv_SelectSQL LONG := ' ';
  --
BEGIN  
  Gv_SelectSQL := ' SELECT * FROM TEST_USER01.TEST01 ';
  -- 
  OPEN CUR1 FOR 
    Gv_SELECTSQL;  
  LOOP
    --
    FETCH CUR1 BULK COLLECT INTO Gv_DATA LIMIT 40000;
    --
    FORALL I IN Gv_DATA.FIRST..Gv_DATA.LAST
      INSERT INTO TEST_USER01.TEST02 VALUES Gv_DATA(I) ;
	  COMMIT;
    --
    EXIT WHEN CUR1%NOTFOUND;
    --
  END LOOP;
END;