100만건의 데이터를 삽입하는 법 (Oracle)

약 100만건의 데이터를 삽입해야 하는 상황

회사에서 현재 하는 프로젝트에서 학습 데이터를 다뤄야 하는 일이 생겼고 데이터의 양은 약 100만건, 이 데이터를 데이터베이스에 넣는 과정이 있었다.

처음에는 100만건의 데이터를 삽입하는데 있어서 단일 Insert 문으로 데이터를 삽입했으나 10만건의 데이터가 들어가는 시점에서 약 한시간이 소요되는 상황이 발생했다. 단순 계산으로 100만건의 데이터를 더 삽입하기 위해서는 9시간이 추가적으로 걸릴 것으로 보여졌고 다른 방법을 모색 했어야만 했다.

INSERT INTO TABLE_NAME ([컬럼1], [컬럼2], [컬럼3]) VALUES ('데이터1', '데이터2', '데이터3');

100만건의 대량의 데이터를 삽입하는 것은 처음 있었던 일이었고 접해볼 경험이 없었기에 너무 쉽게 생각했던 것 같다.

개선 방향

구글링을 해보니 단일 쿼리를 던지는 것 보단 다중 INSERT문을 통해 삽입하면 훨씬 빠르다는 것을 찾았다. 당연한 얘기인데 왜 생각을 못 했을까..

INSERT INTO TABLE_NAME ([컬럼1], [컬럼2], [컬럼3])
    SELECT [데이터1], [데이터2], [데이터3] FROM DUAL UNION ALL
    SELECT [데이터4], [데이터5], [데이터6] FROM DUAL UNION ALL
    ...
    SELECT [데이터7], [데이터8], [데이터9] FROM DUAL

위와 같은 방식으로 다중 쿼리를 던졌고 그 결과 10만건에 약 1시간이 걸리던 과정이 100만건에 약 11분으로 단순 계산으로 하면 10만건에 1분도 안되는 시간으로 확연하게 줄었다. 이 과정에서 DB Connection 시간이 포함되어 있으니 뺀다면 10만건에 40초 남짓이었을 것 같다.

위 과정에서 겪었던 경험들

MySQL은 되는데 Oracle은 안되는 것..

MySQL은 다중 Insert 작성 시

INSERT INTO TABLE_NAME ([컬럼1], [컬럼2], [컬럼3]) VALUES
    ([데이터1], [데이터2], [데이터3]), ([데이터4], [데이터5], [데이터6]), ... ,
    ([데이터7], [데이터8], [데이터9]);

위 쿼리로 Insert가 가능하지만

Oracle에서는 안된다. 애초에 위 방법이 안될 것이라는 생각을 하지않았어서 검색을 할 때 Oracle, MySQL 다중 Insert 라는 키워드는 고려도 안했는데.. 어지럽다..

Oracle 다중 Insert 작성 시 주의 점

INSERT INTO TABLE_NAME ([컬럼1], [컬럼2], [컬럼3])
    SELECT ([데이터1], [데이터2], [데이터3]) FROM DUAL UNION ALL
    SELECT ([데이터4], [데이터5], [데이터6]) FROM DUAL UNION ALL
    ...
    SELECT ([데이터7], [데이터8], [데이터9]) FROM DUAL

처음에는 위 코드와 같이 SELECT 문에 소괄호를 작성하여 진행했는데 우괄호가 누락 됐다는 오류와 함께 죽어도 삽입이 안되더라..

SELECT문에는 괄호를 작성하면 애초에 우괄호 에러가 난다. 어떤 사이트를 봐도 다 소괄호를 작성하여 써놨던데.. 왜 그럴까? 다행히 괄호를 지우니 정상적으로 동작했다.

참고 자료

• Oracle에서 다중 Insert하는 방법

[Oracle] 다 건 삽입(MULTIPLE INSERT) 속도 비교

• Oracle에서 MySQL 다중 insert를 작성했을 때 발생한 에러

[오라클/Oracle] ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다 에러 해결

++

추가 검색을 해보니, 보통 다량의 데이터를 삽입할 때는 CSV로 한번에 밀어넣는 방식을 많이 사용하나보다.

하지만 회사 내부망에 문서 DRM 까지 걸려버려서 CSV를 저장하면 암호화가 걸리면서 프로그램에서 해당 파일을 열지 못하게 되어 어쩔 수 없이 자동화 로직을 짜서 txt 파일로 Insert 구문을 만들고 OJDBC를 통해서 Statement를 사용하여 삽입하는 방식을 사용했다.

다음에 내부망이 아닌 곳에서 이와 같은 일이 발생하면 CSV를 사용하여 삽입해 봐야겠다.