[TroubleShooting/DB] ORA-01795 maximum number of expressions in a list is 1000 in bi publisher (feat: QueryDSL)

문제

프로젝트를 진행하는 중에 특정 조건에 맞는 유저들을 검색하고, 해당 아이디들을 기준으로 특정 데이터를 업데이트 하려는 중에 에러를 마주했다.

'ORA-01795 maximum number of expressions in a list is 1000 in bi publisher'

 

확인해보니  Oracle은 9버전 이후부터 in 절에 1000개가 넘는 데이터를 허용하지 않고 있기 때문에 발생하는 에러였다.

// in절에 다량의 데이터로 인한 에러 발생
jpaQueryFactory.
    select(member).
    from(member).
    where(member.id.in(1,2,3,4 ... 1001)).
    fetch();

 

해당 에러에 대해서 서비스에서 QueryDSL 사용 예시로 해결하는 방법을 포스팅 하려고 한다.

 

해결

in 절에 직접적인 데이터의 갯수가 1000개가 넘으면 발생하는 에러이기 때문에 그 문제를 해결하면 된다.

1. subQuery 사용

in 절 안에 데이터가 아닌 subQuery를 통해 조회하는 방식으로 해결이 가능하다.

// in절에 subQuery 사용으로 문제 해결
jpaQueryFactory.
    select(member).
    from(member).
    where(member.id.in(JPAExpressions.
                            select(order.member.id).
                            from(order).
                            where(order.status.eq("success")))).
    fetch();

 

2. or 사용

in 절 안에 데이터가 1000개 넘지 않도록 ListUtils.partition 를 사용해 조회한다. 결과적으로 같은 in 절을 or 로 나누어 조회한다.

// 기존 동적 검색 로직 활용 가능
BooleanBuilder mainBuilder = new BooleanBuilder();
// 해당 조건의 검색이 있을 경우 subBuilder 생성 후 partition을 통해 1000개로 나누어 or 절로 추가
if(condition.getIds()!=null&&!condition.getIds().isEmpty){
    BooleanBuilder subBuilder = new BooleanBuilder()
    for(List<Integer> partitionIds : ListUtils.partition(condition.getIds(), 1000)){
        subBuilder.or(member.id.in(partitionIds));
    }
    // subBuilder 를 메인 Builder 에 추가
    mainBuilder.and(subBuilder);
}
jpaQueryFactory.
        select(member).
        from(member).
        where(mainBuilder).
        fetch();

 

만약 동적 업데이트 (https://mokggang.tistory.com/70) 할 때 in 절로 인해 동일한 에러가 날 경우에도 같은 방법으로 해결 가능하다.