대량 데이터 삽입 insert 빠르게 실행 벌크 insert

 

 

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

설명 : 대량 데이터 삽입 insert 빠르게 실행 벌크 insert

배치 처리 또는 장부 작성처럼 대량의 레코드를 생성해야 할 때가 있음

 

대부분 1개의 레코드씩 INSERT 구문을 실행함 

이때 (1개의 레코드) = (1개의 쿼리)이므로 대량의 레코드를 생성하려 한다면 그만큼 같은 수의 쿼리를 실행해야함 

이방법은 부하가 매우 큰작업임. 이러한 경우를 위해서 미리 배열 변수에 여러 개의 레코드를 설정하고

하나의 쿼리로 INSERT 할수 있는 기능이 벌크(Bulk) Insert 임

벌크 Insert는 테이블 레코드 형식의 배열에 값을 저장하고 한번에 Insert 구문을 실행함

 

 

테스트

테이블 생성

CREATE TABLE MAXTEST (COLA NUMBER, COLB NUMBER, COLC NUMBER);
CREATE TABLE MAXTEST2 (COLA NUMBER, COLB NUMBER, COLC NUMBER);

 

벌크 INSERT 실행

DECLARE
-- 1. MAXTEST 레코드 형식의 배열 선언
TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
-- 2. 배열에 값 설정
FOR i IN 1..1000000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=10;
   w_ins(i).COLC :=99;
END LOOP;
-- 3. 벌크 INSERT 실행
   FORALL i in 1..1000000 INSERT INTO MAXTEST VALUES w_ins(i);
   COMMIT;
END;
/

 

 

일반 insert

BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO MAXTEST2 VALUES (i, 10, 99);
END LOOP;
COMMIT;
END;
/

 

sql trace로 본 성능 차이

벌크 insert sql trace
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.79       1.01          0       4082      23177     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.79       1.01          0       4082      23177     1000000
 
 
일반 insert sql trace
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 1000000     12.39      18.37          0       5366    1034644     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   1000001     12.39      18.37          0       5366    1034644     1000000

일반 INSERT에서는 Execute의 Count값이 100만개 이지만 벌크 INSERT에서는 1로 나옴

또한 cpu 값이 일반 INSERT에서는 12.39초이지만 벌크 INSERT에서는 0.79초임

 

 

결과 : 대량의 레코드를 생성할 때 벌크 INSERT를 사용하면 SQL 실행 횟수가 줄어들고 cpu 시간도 짧아짐

 

 

 

참조 : 오라클 레벨업 서적

https://positivemh.tistory.com/808

대량 샘플데이터 생성용 쿼리 벌크 인서트

https://positivemh.tistory.com/242

오라클 샘플 데이터 만들기, 테스트 데이터 벌크인서트 + dbms_random