[Oracle] 대량 데이터 insert 하기. UNION ALL 사용.

나의 상황 : 대량의 데이터를 테이블에 insert 해야하는 상황. (대부분 1000건 정도의 데이터지만 간혹 10000건 이상의 데이터도 insert 해야함.)

생각 과정 : 

1. service 단에서 for 문을 이용하여 insert 요청 계속 날리기.. vs SQL 에 foreach를 사용하여 한번에 insert 하기.

-> 적은 건수면 서비스로직에서 해도 상관없겠지만 건 수가 많으면 connection 문제가 생길 거 같았다. 속도차이도 많이 날 것이라 생각해서 패쓰. 

(직접 테스트해보신 글을 발견해 첨부한다. https://yzink.tistory.com/50 역시나 속도차이가 많이 난다!)

2. SQL 에서 foreach를 사용해서 한번에 insert 하겠다고 결정. 

3. INSERT ALL vs UNION ALL 두 가지 방식이 나왔다. 둘은 직접 테스트 해보기로 했다.

<INSERT ALL>

INSERT ALL 
    INTO [테이블] ( [컬럼1], [컬럼2], ...) VALUES ( [VALUE1], [VALUE2], ...)
	INTO [테이블] ( [컬럼1], [컬럼2], ...) VALUES ( [VALUE1], [VALUE2], ...)
	...
    INTO [테이블] ( [컬럼1], [컬럼2], ...) VALUES ( [VALUE1], [VALUE2], ...)
SELECT * FROM DUAL;
)

-내 구현 코드
<insert id="insertStatList" parameterType="java.util.List">
	<foreach collection="list" item="vo" open="INSERT ALL" close="SELECT * FROM SYS.DUAL" separator=" ">
        INTO STAT_MIN 
            (PROVIDER_ID, SERVICE_TYPE, REG_CNT, SUCCESS_CNT, FAIL_CNT)
        VALUES
        (#{vo.provider_id}, #{vo.service_type}, #{vo.reg_cnt}, #{vo.success_cnt}, #{vo.fail_cnt})
    </foreach>
</insert>

<UNION ALL>

INSERT INTO [테이블] ([컬럼1], [컬럼2], ...) 
	SELECT ([VALUE1], [VALUE2], ...) FROM DUAL UNION ALL 
	SELECT ([VALUE1], [VALUE2], ...) FROM DUAL UNION ALL
	...
	SELECT ([VALUE1], [VALUE2], ...) FROM DUAL
)

-내 구현 코드
<insert id="insertStatList" parameterType="java.util.List">
    INSERT INTO STAT_MIN 
        (PROVIDER_ID, SERVICE_TYPE, REQ_CNT, SUCCESS_CNT, FAIL_CNT)
        <foreach collection="list" item="vo" separator="UNION ALL">
            SELECT 
                 #{vo.provider_id},
                 #{vo.service_type},
                 #{vo.req_cnt},
                 #{vo.success_cnt},
                 #{vo.fail_cnt},
            FROM DUAL
        </foreach>
</insert>

결론 : UNION ALL 을 사용하는 방식이 훨씬 빨랐다. 그냥 UNION ALL 을 사용하자!

> 만 건 이상 테스트를 해보니 INSERT ALL 을 하는 도중에 서버가 몇번이나 멈춰버렸다. 10분이상 기다리가다 강제로 서버를 재기동하기를 반복했더니.. 오라클의 해당 테이블이 락이 걸려 오라클 서버를 내렸다 올리는 일도 있었다...

UNION ALL 이 훨씬 빨랐으나 이것도 2000건이 넘어가니 체감상 몇초 정도 로딩이 걸리길래.. 로그가 바로바로 안찍히면 답답한 나는 10번 이내의 connection 은 괜찮겠지 싶어서 서비스 로직에서 insert 할 데이터를 2000건씩 끊어서 insert 요청을 날리기로  결정! 

 

 

** DB 서버와의 connection 을 한번만 하고 여러 쿼리를 날릴 수 있는 방법이 있음!! ** 

2022.04.07 - [개발/DB] - MyBatis 대량 데이터 INSERT - SqlSessionFactory 이용!