[Oracle/Mybatis] IN 절에 사용 가능한 값의 개수 제한 해결하기 (ORA-01795)

이전 글에서 다루었던 다중 insertion을 수행하는 raw query를 작성해 적용한지 얼마 되지 않아서, 비슷해 보이는 문제에 관한 질문을 받게 되었다. 바로 list로 주어진 여러 개의 조건 값을 WHERE절에 IN 형태로 포함시킨 raw query를 build하고 싶다는 것이었는데, 이전 글에서 작성했던 것과 비슷하게 다음과 같은 query를 답변해 주었다.

<select id="selectItems">
	SELECT *
      FROM tbl
     WHERE cdn IN
	<foreach collection="items" item="item" open="(" close=")" separator=", ">
		#{item.val}
	</foreach>
</select>

SELECT *
  FROM tbl
 WHERE cdn IN (item1.val, item2.val, ..., itemn.val)

그런데, 조건의 개수가 꽤나 많았던 것인지 다음과 같은 오류가 발생했다는 사실을 확인하게 되었다.

ORA-01795: 목록에 지정 가능한 식의 최대 수는 1000입니다 (maximum number of expressions in a list is 1000)

사실 IN 절에 들어갈 수 있는 값의 개수에 1,000건이라는 제한이 있다는 것은 웬만해서는 마주하기 어려운 상황이다. 1,000건이 넘어가는 record들을 직접 raw query에 넣어서 사용하는 경우가 드물기도 할 뿐더러, 이 정도로 다량의 데이터라면 service layer에서 가공하는 것이 아니라 설계 시에 해당하는 테이블을 만들어서 처리하는 것이 더 적절한 설계이고, 이미 해당 데이터에 해당하는 테이블이 있는 경우라면 데이터베이스에 접근해 가져온 데이터를 service layer를 거쳐 다시 조건으로 사용해 데이터베이스에 접근하는 것이 아니라 (특히 ORM이 아닌 mybatis를 사용한다면) join 관계를 통해 한 번에 가져오는 것이 일반적이기 때문이다.

하지만 항상 실제 업무에서 이론적인 부분이 적용되는 것은 아니고, 물어본 결과 이미 설계를 변경하긴 어려워 그대로 service layer에서 가공한 정보를 조건으로 삼아 데이터베이스에서 selection을 진행해야 하는 상황이었다. (Oracle에서 지원하는 Global Temporary Table을 포함한) 임시 테이블을 사용하는 것이 가장 깔끔한 해결 방식이긴 하지만, 귀찮은 전후처리 과정이 필요하기 때문에 간단하게 해결할 수 있는 방법을 찾아보게 되었다.

1,000건씩 쪼개서 OR 연산자로 묶기

SELECT *
  FROM tbl
 WHERE cdn IN (item1.val, item2.val, ..., item1000.val)
    OR cdn IN (item1001.val, item1002.val, ..., item2000.val)
    OR ...

가장 간단한 방법은 IN 절의 조건을 1,000건으로 맞추고, OR 연산자를 사용해서 조건들을 묶어내는 방식이다. 비슷하게 1,000건씩 쪼개서 WHERE절로 SELECT한 결과를 UNION ALL해서 결과를 얻어내는 방법도 존재한다. 따로 1,000건 단위로 나누는 전처리 과정을 구현해야 하며, 성능 문제가 생길 수도 있어서 잘 사용하지는 않는 것으로 보인다. service layer에서 Collectors.groupingBy method를 사용해서 중첩된 list 형태로 변환한 데이터를 mapper에서 foreach 태그를 중첩해 풀어냄으로써 mybatis 문법으로 이 방식을 구현할 수 있다.

List<List<T>> partitionedItems = new ArrayList<>(
    items.stream()
    .collect(Collectors.groupingBy(item -> item / 1000))
    .values()
);

<select id="selectItems">
	SELECT *
      FROM tbl
     WHERE
	<foreach collection="partitionedItems" item="partition" separator="OR">
		cdn IN
        <foreach collection="partition" item="item" open="(" close=")" separator=", ">
            #{item.val}
        </foreach>
	</foreach>
</select>

Multiple Column의 조건절 사용

SELECT *
  FROM tbl
 WHERE (0, cdn) IN (0, item1.val), (0, item2.val), ..., (0, itemn.val)

다음으로 사용할 수 있는 방법은 multiple column을 비교하는 방식을 사용하는 것이다. 의미 없이 동일한 값으로 채운 column을 하나 추가해서 비교에 사용하면 단일 column을 IN 절을 사용해 비교하는 것과 달리 1,000건 제한에 걸리지 않기 때문에 이 방식을 사용해 제한을 우회할 수 있다. 상대적으로 간단하게 제약을 해결할 수는 있지만 예기치 않은 오류가 발생할 가능성이 있고, 깔끔하다고 보기는 어렵다.

<select id="selectItems">
	SELECT *
      FROM tbl
     WHERE (0, cdn) IN
	<foreach collection="items" item="item" open="(" close=")" separator=", ">
		(0, #{item.val})
	</foreach>
</select>

IN절 내부에서 Subquery 사용

SELECT *
  FROM tbl
 WHERE cdn IN
    SELECT item1.val FROM DUAL
    UNION ALL
    SELECT item2.val FROM DUAL
    UNION ALL
    ...
    UNION ALL
    SELECT itemn.val FROM DUAL

테이블을 만들어서 이에 대해 selection한 결과를 IN절에 사용한다면 마찬가지로 건수에 대한 제약 없이 결과를 얻을 수 있다. 그런데 이 방식은 사실 IN절에 대해 내부에 subquery를 사용해 inline view를 만든 것과 동일한 것이기 때문에, 조건들을 subquery 형태로 만들어 준다면 제약을 해결 가능할 것임을 이해할 수 있다. 여기에서는 UNION ALL을 사용해 조건들을 subquery로 만들어 주었다.

<select id="selectItems">
	SELECT *
      FROM tbl
     WHERE cdn IN
	<foreach collection="items" item="item" open="(" close=")" separator="UNION ALL">
		SELECT #{item.val} FROM DUAL
	</foreach>
</select>

중첩 테이블 사용 (시도)

CREATE OR REPLACE TYPE cdn_type IS TABLE OF NUMBER; -- 조건 type에 따라 다르게 설정

SELECT *
  FROM tbl
 WHERE cdn IN (
    SELECT *
      FROM TABLE(cdn_type(item1.val, item2.val, ..., itemn.val))
)

Oracle의 collection type 중에는 중첩 테이블(Nested Table)이 존재한다. TYPE ... IS TABLE OF 구문으로 선언한 중첩 테이블 타입에 생성자를 사용해 값을 할당하거나 PL/SQL 변수로 할당한 뒤 method들을 사용해 요소의 값에 접근 또는 변경할 수 있다. 중첩 테이블은 일반 테이블의 column 타입으로 사용할 수도 있고, TABLE() 함수의 인자로 사용되면 실제 테이블처럼 collection 내부의 값을 사용할 수 있다. 생성자로 선언한 중첩 테이블을 TABLE() 함수의 인자로 주어 그 결과를 FROM 절에서 테이블 형태로 사용한다면 우리가 원하는 결과를 얻어낼 수 있다.

<select id="selectItems">
	SELECT *
      FROM tbl
     WHERE cdn IN (
        SELECT *
          FROM TABLE(cdn_type(
            <foreach collection="items" item="item" open="(" close=")" separator=", ">
                #{item.val}
            </foreach>
        ))
    )

</select>

문제는 중첩 테이블의 생성자를 호출할 때도 1,000건의 제한이 있다는 점이었는데, 적은 건수에 대해서는 문제가 없었지만 1,000건이 넘는 데이터를 인자로 주면 ORA-00939 (함수의 인수가 너무 많습니다) 오류가 발생했다. 함수나 프로시저를 만들어서 내부적으로 collection type의 extend method를 호출하는 등의 방식으로 구현해 이 방식을 사용할 수는 있겠지만, 쿼리를 깔끔하게 만든다는 측면에서의 중첩 테이블 사용 시도는 실패하게 되었다.