[Oracle/Mybatis] 다중 Insertion과 Auto Increment 구현

웹 어플리케이션에서 다중 insertion을 수행하는 raw query를 구현해 주어야 하는 이유가 있을까? 사용자의 개별 입력을 받는 일반적인 형태에서는 크게 고민할 부분이 아니겠지만, 별도의 라이브러리를 활용해서 Excel 파일을 업로드하는 등의 대량 입력 기능을 구현한다면 효율성의 문제를 생각할 필요가 생긴다. service 단에서 한 건 단위의 insertion을 수행하는 mapper를 각각 호출한다면 insertion 시마다 데이터베이스와의 연결을 수행해야 하기 때문에 당연히 속도와 관련된 issue가 발생할 수밖에 없다. 적은 건수라면 큰 문제가 없겠지만, 수천에서 수만 건에 이르는 데이터라면 다중 insertion query를 사용해야 한다. 이 글에서는 mybatis 문법을 사용해 다중 insertion query를 build하는 방법에 대해 다루어 보려고 한다.

다중 Insertion 구현

다중 insertion을 구현하기 위해서는 INSERT ALL 또는 UNION ALL을 사용한다. 일반적으로 INSERT ALL 방식보다는 UNION ALL 방식이 속도가 더 빠른 것으로 알려져 있다. 두 가지 방식 모두 mybatis 문법으로 구현이 가능하기 때문에 상황에 맞는 구문을 사용하면 된다.

INSERT ALL

<insert id="insertItems">
	INSERT ALL
	<foreach collection="items" item="item">
		INTO tbl (col1, col2, ..., coln)
			VALUES (#{item.val1}, #{item.val2}, ..., #{item.valn})
	</foreach>
	SELECT * FROM DUAL
</insert>

mybatis 문법 중 foreach tag를 사용하면 인자로 받은 list 형태의 item을 loop하며 query를 build한다.

INSERT ALL
	INTO tbl (col1, col2, ..., coln)
		VALUES (item1.val1, item1.val2, ... item1.valn)
	INTO tbl (col1, col2, ..., coln)
		VALUES (item2.val1, item2.val2, ... item2.valn)
...
	INTO tbl (col1, col2, ..., coln)
		VALUES (itemn.val1, itemn.val2, ... itemn.valn)
SELECT * FROM DUAL

UNION ALL

<insert id="insertItems">
	INSERT INTO tbl (col1, col2, ..., coln)
	<foreach collection="items" item="item" separator="UNION ALL">
		SELECT #{item.val1}, #{item.val2}, ..., #{item.valn} FROM DUAL
	</foreach>
</insert>

mybatis 문법 중 foreach tag룰 사용하는 것은 동일하지만 query statement에서 UNION ALL을 구분자로 사용한다는 점에 차이가 있다. 구분자는 foreach tag에 separator 인자를 명시하는 것으로 구현할 수 있다.

INSERT INTO tbl (col1, col2, ..., coln)
SELECT (item1.val1, item1.val2, ... item1.valn) FROM DUAL
	UNION ALL
SELECT (item2.val1, item2.val2, ... item2.valn) FROM DUAL
	UNION ALL
...
	UNION ALL
SELECT (itemn.val1, itemn.val2, ... itemn.valn) FROM DUAL

수만 건 정도의 데이터는 문제 없이 일반적인 sql 문법의 다중 insertion statement로 처리가 가능하다. 만약 수십만 건이 넘어가는 정도의 대량이라면 connection 등에서 문제가 발생할 수 있기 때문에 PL/SQL의 bulk sql 문법을 사용해 분산 처리를 구현하거나 직접 DBMS에서 파일을 읽어오도록 구현하는 것이 안전하다.

Auto Increment 구현

Oracle에는 autoincrement syntax가 없기 때문에 MAX 함수를 사용하거나 sequence object를 생성해서 NEXTVAL 값을 사용하는 방식으로 auto increment를 구현한다. 문제는 위와 같이 다중 insertion을 구현하는 상황에서는 어떤 방식을 사용하더라도 문제가 발생한다는 것이다. MAX 함수를 사용한다면 insert하는 모든 record의 key value에 MAX(key)+1 값만이 삽입될 것이고, 해당 key를 primary key로 사용하고 있다면 당연하게도 무결성 constratint를 위배하기 때문에 오류가 발생하게 된다. sequence의 NEXTVAL을 사용하게 된다면 ORA-00287 (시퀀스 번호는 이 위치에 사용할 수 없습니다.) 오류가 발생하게 된다. 그렇다면 다중 insertion 시에 어떻게 auto increment된 value를 사용할 수 있을까?

Mybatis 문법 사용

<insert id="insertItems">
	<selectKey keyProperty="maxSeq" resultType="int" order="BEFORE">
		SELECT NVL(MAX(key), 0) FROM tbl
	</selectKey> 
	INSERT INTO tbl (key, col2, ..., coln)
	<foreach collection="items" item="item" index="idx" separator="UNION ALL">
		SELECT #{maxSeq} + #{idx}, #{item.val2}, ..., #{item.valn} FROM DUAL
	</foreach>
</insert>

먼저 Mybatis 문법을 사용해서 auto increment key를 구현할 수 있다. selectkey 문법을 사용하면 insert 구문을 수행하기 전에 특정 key 값을 가져온 뒤 해당 값을 이용해 처리할 수 있는데, 이 문법으로 key column의 max value를 가져와 사용할 수 있다. 도한 foreach tag의 index 인자로 호출 시에 0부터 반환되는 index value를 사용할 수 있다. 이 두 값을 조합한 constant로 원하는 형태의 auto increment key를 사용하는 다중 insertion을 구현 가능하다. 이 방식의 문제는 key value를 한 번만 가져오는 형태이기 때문에 일반적으로 사용하는 sequence 방식의 logic과는 차이가 있고, sequence 호출을 위해서는 추가적인 작업이 필요하다는 것이다.

PL/SQL FUNCTION 사용

CREATE OR REPLACE FUNCTION get_tbl_seq
RETURN NUMBER AS num NUMBER;
BEGIN
	SELECT tbl_seq.NEXTAL
	  INTO num
	  FROM DUAL;
	
	RETURN num;
END;

<insert id="insertItems">
	INSERT INTO tbl (col1, col2, ..., coln)
	<foreach collection="items" item="item" separator="UNION ALL">
		SELECT get_tbl_seq(), #{item.val2}, ..., #{item.valn} FROM DUAL
	</foreach>
</insert>

sequence의 NEXTVAL을 호출하는 방식을 사용하고자 한다면 PL/SQL의 FUNCTION을 정의해 사용해야 한다. 바로 sequence의 NEXTVAL을 호출해 그 값을 반환하는 함수를 정의하고, 이 함수를 매 insertion하려는 item마다 호출하도록 하는 것이다. 이 방식을 사용하면 제약 없이 순차적인 index를 가진 record들이 잘 삽입되는 모습을 확인할 수 있다. 이처럼 PL/SQL의 기능을 사용해 프로시저나 함수를 미리 정의해두면 간단한 호출로 사용 가능하다는 장점이 있지만, 코드 내부에서 바로 프로시저나 함수의 정보를 수정할 수 없어 직관성이 떨어진다는 단점도 있다.