Oracle 조건 in 쿼리 1000개 제한 피하기.

 

1. 개요

얼마 전 주소록을 일괄삭제해주는 기능이 동작하지 않는다는 사용자 문의를 받았다. 이를 확인해보니 특정 쿼리가 수행되지 않고 예외를 발생시키고 있었는데 오늘은 이에 대해 이야기를 해보려고 한다.

해당 이슈는 ORA-01795: maximum number of expressions in a list is 1000으로 in절의 파라미터가 1000개를 초과할 때 발생하는 오류이다.

2. 왜 발생할까???

해당 예외는 Oracle에서 의도적으로 발생시키는 예외로, 이유가 분명해보였다. 그렇다면 왜 이런 예외를 발생시킬까??

당연히 성능때문이다. 이러한 제한은 Oracle이 메모리를 효율적으로 사용하기 위한 것인데 IN절의 파라미터를 처리하기 위해 Oracle은 메모리 내에 일시적인 테이블을 생성한다. 하지만 이러한 일시적인 테이블이 너무 크면 서버의 성능에 부정적인 영향을 미치기 때문에, Oracle은 IN절의 파라미터 개수를 제한하는 것이다.

때문에 내부적으로 해당 임계치(1000)을 수정할 수 있는 방법은 없으며, 해당 이슈를 해결하기 위해서는 교묘하게 우회하거나 어플리케이션 레벨에서의 구현이 필요하다.

그렇다면 어떤 방법으로 구현이 가능할지 알아보자.

3. 파라미터 제한을 피하는 방법

1. in절을 or로 연결

in절을 1000개 이하의 파라미터로 구성하여 or로 연결

select * from my_table where ID in (1,2,3,4,...,1000) or 
ID in (1001,1002,...,2000)

2. union all로 in절을 연결

in절을 1000개 이하의 파라미터로 구성하여 union all로 연결

select * from my_table where ID in (1,2,3,4,...,1000)
union all
select * from my_table where ID in (1001,1002,...)

3. 어플리케이션에서 1000건 단위로 쿼리를 요청

데이터의 전체건수가 1000건을 넘으면, 다수의 쿼리로 분할하는 유틸을 개발.

4. 임시 테이블을 생성하여 WHERE절에 사용

CREATE TEMPORARY TABLE키워드를 사용하여, 임시테이블을 생성하고 이를 IN절에 사용 할 수 있음. 해당 임시테이블은 sqlSession별로 유효하며, 다른 session에서는 DML을 수행할 수 없음.

5. 서브쿼리를 사용하여 IN절에 사용

서브쿼리를 IN절의 파라미터로 사용하는 경우, 개수 제한없이 원하는 구문을 수행할 수 있음

delete my_table where in (select member_no from member);

 

 

6. in절의 파라미터를 multi-value로 구성

single-value의 제한 파라미터는 1000건이나, multi-value의 제한 파라미터는 10만건으로 지정이 되어있음. 이를 이용해 multi-value로 값을 조작하면, 최대 10만건까지 in절에 삽입할 수 있음.

select column_X, ... from my_table
where ('magic', column_X ) in (
        ('magic', 1),
        ('magic', 2),
        ('magic', 3),
        ('magic', 4),
             ...
        ('magic', 99999)
    )

 

4. 성능테스트

위의 3. 파라미터 제한을 피하는 방법에서 적절한 방법을 찾기위해, 어플리케이션단에 구현하여 테스트를 진행해보았다.

위의 방법에서 String을 이어붙여서 쿼리를 수행해야하는 1. in절을 or로 연결, 2. union all로 in절을 연결은 유지보수와 개발에 불편함이 있을 것 같아 제외했다.

테스트는 특정 테이블의 index필드를 in절에 넣는 방식으로 진행했으며, 더미데이터로 해당 테이블에 550만건을 추가했다.

삭제처리 시, 4개의 쓰레드가 동시에 기능을 수행하였으며 하나의 요청을 대상으로 성능을 측정했다. 때문에 여러개의 쓰레드를 사용하지 않는 임시테이블의 성능이 비교적 낮게 보여졌다.

기획상 최대 삭제 개수가 50,000건이므로 50,000건을 최대로 테스트하였다. 테스트 데이터는 아래와 같다.

4.1 5,000건 삭제 테스트

4.2 50,000건 삭제 테스트

Mybatis의 excutor-type을 Batch로 설정해둔 상태에서 테스트한 결과로, 적은 파라미터로 여러 PreparedStatement를 생성하는 것이 더 높은 성능을 보였다.

또한 실험을 하면서 처리되는 쿼리의 수행시간을 보면서 특이한 점을 확인할 수 있었는데, 4개의 쓰레드가 한번의 DELETE쿼리를 수행하는 사이클을 넘길수록 쿼리의 수행시간이 짧아지는 것을 확인 할 수 있었다.

ex) 첫번째 소요시간: 220ms, 두번째 소요시간: 80ms, 세번째 소요시간: 33ms

 

 

출처 : Oracle in절 파라미터 개수 제한 이슈(ORA-01795) (velog.io)