[Oracle] 대량 데이터 인서트 하기 (Bulk Data Insert)

[Oracle] 대량 데이터 인서트 하기 (Bulk Data Insert)

---

속도 : 4 > 3 > 2 > 1

 

1. INSERT INTO로 건별 INSERT

건별 Commit으로 속도가 느림

INSERT INTO [TABLE] VALUES ('val1','val2','val3');
INSERT INTO [TABLE] VALUES ('val1','val2','val3');
INSERT INTO [TABLE] VALUES ('val1','val2','val3');
INSERT INTO [TABLE] VALUES ('val1','val2','val3');
INSERT INTO [TABLE] VALUES ('val1','val2','val3');

 

2. INSERT ALL

1번보다 빠름, 1번의 commit

INSERT ALL 
    INTO [TABLE] (col1, col2, col3) VALUES ('val1','val2', 'val3')
    INTO [TABLE] (col1, col2, col3) VALUES ('val1','val2', 'val3')
    INTO [TABLE] (col1, col2, col3) VALUES ('val1','val2', 'val3')
    INTO [TABLE] (col1, col2, col3) VALUES ('val1','val2', 'val3')
    INTO [TABLE] (col1, col2, col3) VALUES ('val1','val2', 'val3')
SELECT * FROM DUAL;

 

3. UNION ALL

2번보다 빠름, 1번의 commit

INSERT INTO [TABLE] (col1, col2, col3)
SELECT 'val1', 'val2', 'val3' FROM DUAL UNION ALL
SELECT 'val1', 'val2', 'val3' FROM DUAL UNION ALL
SELECT 'val1', 'val2', 'val3' FROM DUAL UNION ALL
SELECT 'val1', 'val2', 'val3' FROM DUAL UNION ALL
SELECT 'val1', 'val2', 'val3' FROM DUAL;

 

4. SQL LOADER

1) 아래 구문을 .ctl 확장자로 저장

options(skip =1)  //머릿글 1행 제외
load data
infile 'data Location\[data File Name].dat'  
append   
into table [TABLE]
fields terminated by ',' //구분자

 

2) cmd창 실행

!sqlldr userid= [userid/password] control ='[.ctl File Location]\[ctl File Name].ctl' log ='[실패시 기록될 로그위치]\[Log Name].log'