개요：

문제 상황：

• 예제 쿼리:

select * from member where id in (1, 2, 3, ...)

• **오류 메시지:**Cause: java.sql.SQLException: ORA-01795: 목록의 최대 표현식 수는 1000입니다
• **설명:**Oracle 데이터베이스에서 IN (...) 절을 사용할 때 괄호 안의 값이 1000개를 초과하면 오류가 발생합니다.

해결 방안：

이 문제를 해결하기 위한 세 가지 방법을 소개합니다.

방법 1

• 'or' 키워드를 사용한 조건문으로 분할

select * from member where member_id=1 or member_id=2 or member_id=3 ...

• Mybatis 구현

<select id="selectMemberList" resultType="com.Member">
    select * from member
    <where>
        <if test="memberIds != null and memberIds.size gt 0">
            <foreach collection="memberIds" item="memberId" index="idx" open="" close="" separator="or">
                member_id = #{memberId, jdbcType=INTEGER}
            </foreach>
        </if>
    </where>
</select>

• Mybatis 최적화 버전

<select id="selectMemberList" resultType="com.Member">
    select * from member
    <where>
        <choose>
            <when test="memberIds != null and memberIds.size gt 0 and memberIds.size lt 1000">
                <foreach collection="memberIds" item="memberId" index="idx" open="member_id in(" close=")" separator=",">
                    #{memberId, jdbcType=VARCHAR}
                </foreach>
            </when>
            <when test="memberIds != null and memberIds.size ge 1000">
                <foreach collection="memberIds" item="memberId" index="idx" open="" close="" separator="or">
                    member_id = #{memberId, jdbcType=VARCHAR}
                </foreach>
            </when>
        </choose>
    </where>
</select>

방법 2

• 여러 IN 조건의 결합으로 분할, 각 IN당 1000개 이하

select * from member where member_id in (1,2,3...) and member_id in (1000,1001,1002...) and ...

• Mybatis 구현

<select id="selectMemberList" resultType="com.Member">
    select * from member
    <where>
        <if test="memberIds != null and memberIds.size gt 0">
            <foreach collection="memberIds" item="memberId" index="idx" open="member_id in(" close=")" separator=",">
                <if test="idx != 0 and (idx % 999) == 0">
                    #{memberId, jdbcType=INTEGER} ) and member_id in( #{memberName, jdbcType=INTEGER}
                </if>
                <if test="idx == 0 or (idx % 999) != 0">
                    #{memberId, jdbcType=INTEGER}
                </if>
            </foreach>
        </if>
    </where>
</select>

방법 3

• IN 절에 select '고정값' from dual + union all 서브쿼리 사용

select m.* from member m
where m.member_id in (
    select 1 from dual
    union all
    select 2 from dual
    union all
    select 3 from dual
    ...
)

• Mybatis 구현

<select id="selectMemberList" resultType="com.Member">
    select * from member
    <where>
        <if test="@Ognl@isNotEmpty(memberIds)">
            member_id in
            <foreach collection="memberIds" item="memberId" index="idx" open="(" separator=" union all " close=")">
                select #{memberId} from dual
            </foreach>
        </if>
    </where>
</select>

• 최적화 버전 (CTE 사용)

with temp as (
    select 1 as id from dual
    union all
    select 2 as id from dual
    ...
)
select m.* from member m
where m.member_id in (
    select temp.id from temp
)

• Mybatis 구현

<select id="selectMemberList" resultType="com.Member">
    with temp as
    <foreach collection="memberIds" item="memberId" index="idx" open="(" separator=" union all " close=")">
        select #{memberId} as id from dual
    </foreach>
    select m.* from member m
    where m.member_id in (
        select temp.id from temp
    )
</select>