개요: EXISTS와 IN, 무엇이 더 빠를까?
SQL 튜닝 인터뷰나 실무 코드 리뷰에서 가장 단골로 등장하는 질문 중 하나는 "WHERE 절에서 IN과 EXISTS 중 어느 것이 성능상 유리한가?"입니다. 과거 규칙 기반 옵티마이저(RBO) 시절의 낡은 가이드에서는 "메인 쿼리가 작으면 EXISTS, 서브쿼리 결과가 작으면 IN을 써라"라고 공식처럼 정의하곤 했습니다.
하지만 현대의 비용 기반 옵티마이저(CBO, Cost-Based Optimizer) 환경에서 이 명제는 절반만 맞고 절반은 틀립니다.
오라클 옵티마이저는 쿼리 변환기(Query Transformer)를 통해 사용자가 IN으로 작성하든 EXISTS로 작성하든, 내부적으로 동일한 세미 조인(Semi Join) 또는 안티 조인(Anti Join) 실행 계획으로 통합 변환하여 최적의 경로를 찾아내기 때문입니다.
결국 실무 튜닝의 핵심은 문법적 모양새가 아니라, 옵티마이저가 서브쿼리를 어떻게 풀어내는지(Subquery Unnesting)와 그 과정에서 Semi / Anti Join이 메모리(Buffer Cache / PGA) 상에서 어떻게 동작하는지 그 내부 메커니즘을 통제하는 데 있습니다.
내부 메커니즘: 쿼리 변환(Query Transformation)과 Semi/Anti Join
오라클 CBO가 IN, EXISTS, NOT IN, NOT EXISTS가 포함된 SQL을 파싱할 때 내부적으로 일어나는 핵심 아키텍처는 다음과 같습니다.
1. Subquery Unnesting (서브쿼리 풀어내기) vs FILTER
옵티마이저는 서브쿼리를 만났을 때 크게 두 가지 경로 중 하나를 선택합니다.
- Subquery Unnesting (UNNEST): 서브쿼리의 중첩 구조를 허물고 메인 쿼리와 동일한 레벨(Flat)로 끌어올려 일반 조인 형태(Semi/Anti Join)로 변환합니다. 조인 순서와 물리적 조인 기법(NL, Hash, Sort Merge)을 자유롭게 선택할 수 있어 대용량 처리에서 필수적입니다.
- Subquery Filtering (NO_UNNEST): 서브쿼리를 풀지 않고 원형 그대로 둡니다. 실행 계획상 FILTER 오퍼레이션으로 나타나며, 기본적으로 Nested Loops와 유사하게 메인 쿼리의 각 행마다 서브쿼리를 반복 실행합니다.
2. Semi Join (세미 조인: EXISTS, IN)의 동작 원리
일반적인 Inner Join은 메인 테이블(1)과 서브 테이블(M)이 조인되면 최종 결과 건수가 M건으로 증폭됩니다. 반면, Semi Join(NL_SJ, HASH_SJ, MERGE_SJ)은 메인 쿼리의 행이 서브쿼리에 '단 1건이라도 존재하는지'만 확인하고 즉시 탐색을 멈추는(Short-Circuit Evaluation) 조인 기법입니다.
SELECT D.DEPT_ID, D.DEPT_NAME
FROM DEPARTMENTS D
WHERE EXISTS (SELECT /*+ UNNEST NL_SJ */ 1
FROM EMPLOYEES E
WHERE E.DEPT_ID = D.DEPT_ID
AND E.SALARY >= 5000);
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 | 28 |
| 1 | NESTED LOOPS SEMI | | 1 | 11 | 11 | 28 |
| 2 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 27 | 27 | 7 |
|* 3 | INDEX RANGE SCAN | EMP_DEPT_IX | 27 | 45 | 11 | 21 |
--------------------------------------------------------------------------------------
- 중복 제거 및 조기 종료(Early Exit): DEPARTMENTS의 특정 부서(DEPT_ID = 10)에 연봉 5000 이상인 사원이 1,000명 있더라도, NESTED LOOPS SEMI는 EMP_DEPT_IX 인덱스를 스캔하다가 조건에 맞는 첫 번째 로우를 찾는 순간 나머지 999건에 대한 스캔을 즉시 중단하고 다음 부서(DEPT_ID = 20)로 넘어갑니다. Buffer Cache의 논리적 블록 읽기(Buffers)를 획기적으로 줄여줍니다.
- 조인 순서 역전 (Right Semi Join): IN 절이나 EXISTS 절이 UNNEST 되면, 옵티마이저는 서브쿼리 집합을 먼저 읽고(SORT UNIQUE 또는 VW_NSO_1 뷰 생성) 메인 쿼리를 나중에 조인하거나, 반대로 서브쿼리를 Hash Build Input으로 삼는 HASH JOIN RIGHT SEMI를 구사할 수 있습니다.
3. Anti Join (안티 조인: NOT EXISTS, NOT IN)과 NULL의 치명적 차이
조건을 만족하지 않는 데이터를 걸러내는 Anti Join(NL_AJ, HASH_AJ, MERGE_AJ)에서는 NOT EXISTS와 NOT IN 사이에 아키텍처적으로 엄청난 차이가 존재합니다. 바로 NULL 값을 다루는 논리 구조(Three-Valued Logic)입니다.
- NOT EXISTS: 조인 컬럼의 NULL 여부를 따지지 않고 매칭되는 로우가 없으면 무조건 참(True)을 반환합니다. 따라서 옵티마이저가 거리낌 없이 HASH JOIN ANTI나 NESTED LOOPS ANTI로 즉각 변환합니다.
- NOT IN: 서브쿼리 결과 집합 내에 단 1건이라도 NULL이 포함되어 있으면 전체 평가식이 UNKNOWN(결과 0건)이 되어버리는 SQL 표준 제약을 가집니다.
- 예: WHERE A.ID NOT IN (10, 20, NULL) $\rightarrow$ A.ID <> 10 AND A.ID <> 20 AND A.ID <> NULL(알수없음) $\rightarrow$ 항상 False (결과 0건)
이 때문에 과거 오라클 버전에서는 서브쿼리 컬럼에 NOT NULL 제약조건이 없으면 Anti Join으로 풀지 못하고 악명 높은 FILTER 플랜으로 처리했습니다. Oracle 11g 이후부터는 이를 개선하기 위해 HASH JOIN ANTI NA (Null-Aware Anti Join) 및 HASH JOIN ANTI SNA (Single Null-Aware) 알고리즘이 도입되었으나, 여전히 내부적으로 NULL 체크를 위한 추가적인 프로브(Probe) 연산이 발생하므로 순수 NOT EXISTS보다 비용이 높습니다.
주요 원인 분석: 실무에서 서브쿼리 성능이 급락하는 이유
프로덕션 환경에서 서브쿼리가 포함된 SQL이 갑작스러운 I/O 폭증이나 CPU 고갈을 일으키는 주원인은 다음과 같습니다.
1. Unnesting 실패로 인한 FILTER 오퍼레이션 고착화
서브쿼리 내부에 ROWNUM, 집계 함수, CONNECT BY, 또는 복잡한 OR 조건이 결합되어 있으면 옵티마이저가 UNNEST 변환을 포기하고 FILTER 오퍼레이션을 선택합니다.
- 왜 문제인가?: 메인 쿼리의 결과 건수가 100만 건이고 고유 값(Distinct Value) 종류가 많다면, 서브쿼리를 100만 번 반복 실행하면서 Buffer Cache의 cache buffers chains 래치 경합과 db file sequential read 대기를 폭발시킵니다. (오라클이 내부적으로 Scalar Subquery Caching과 유사한 Filter 최적화를 수행하지만, 입력 값의 종류가 해시 테이블 크기를 넘어가면 캐시 미스가 속출합니다.)
2. 대용량 서브쿼리에 대한 잘못된 NL_SJ / NL_AJ 선택
메인 쿼리 건수가 수십만 건인데 옵티마이저가 통계정보 오류로 이를 수십 건으로 오판하여 NESTED LOOPS SEMI를 선택한 경우, 수십만 번의 인덱스 랜덤 액세스가 발생하여 온라인 트랜잭션이 마비됩니다.
3. NOT IN 절에 NULL 허용 컬럼 사용
서브쿼리의 비교 대상 컬럼에 NOT NULL 제약조건이 빠진 상태에서 NOT IN을 구사하여, 옵티마이저가 ANTI NA (Null-Aware) 연산을 수행하느라 PGA Hash Area 탐색 비용이 증가하거나, 다중 컬럼 비교 시 Unnesting이 완전히 깨지는 현상입니다.
트러블슈팅 및 해결 방안 (튜닝 가이드)
1. 실행 계획을 통한 서브쿼리 변환 상태 진단
문제가 되는 SQL의 실행 계획을 추출하여 SEMI, ANTI 키워드가 정상적으로 나타나는지, 아니면 FILTER로 묶여 있는지 점검합니다.
-- 실행 계획 및 Predicate Information 점검
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('대상_SQL_ID', NULL, 'ALLSTATS LAST +PREDICATE'));
점검 포인트:
Operation 항목에 FILTER가 떠 있고, 그 하위 스텝의 Starts 항목이 메인 쿼리 건수만큼 수만~수십만 번 찍혀 있다면 즉시 Unnesting 유도 또는 Hash Semi/Anti Join으로 튜닝해야 합니다.
2. 힌트(Hint)를 활용한 Semi / Anti Join 제어 전략
옵티마이저가 잘못된 판단을 내릴 때, 서브쿼리 블록 내부에 직접 변환 힌트와 물리적 조인 힌트를 주입하여 실행 계획을 교정합니다.
-- [사례 1] 대용량 테이블 간 EXISTS 조인: Unnesting 후 Hash Semi Join 유도
SELECT /*+ LEADING(O) */ O.ORDER_ID, O.ORDER_DATE, O.CUST_ID
FROM ORDERS O
WHERE EXISTS (
SELECT /*+ UNNEST HASH_SJ */ 1
FROM ORDER_ITEMS I
WHERE I.ORDER_ID = O.ORDER_ID
AND I.ITEM_STATUS = 'SHIPPED'
);
-- [사례 2] 서브쿼리 결과 집합이 극소량일 때: 서브쿼리를 먼저 드라이빙(Right Semi Join)
SELECT /*+ LEADING(I@SUBQ O) USE_NL(O) */ O.ORDER_ID, O.ORDER_DATE
FROM ORDERS O
WHERE O.ORDER_ID IN (
SELECT /*+ QB_NAME(SUBQ) UNNEST */ I.ORDER_ID
FROM ORDER_ITEMS I
WHERE I.PROMO_CODE = 'VIP_SPECIAL_2026'
);
-- [사례 3] 메인 쿼리가 극소량이고 인덱스 효율이 좋을 때: 의도적으로 FILTER(NO_UNNEST) 유도
SELECT O.ORDER_ID, O.ORDER_DATE
FROM ORDERS O
WHERE O.ORDER_DATE = TRUNC(SYSDATE) -- 극소량(예: 5건)
AND EXISTS (
SELECT /*+ NO_UNNEST */ 1
FROM HUGE_LOG_TABLE L
WHERE L.ORDER_ID = O.ORDER_ID
);
3. NOT IN을 NOT EXISTS로 표준화 (또는 NOT NULL 필터 명시)
안티 조인 요건에서는 정합성 오류와 실행 계획 저하를 동시에 막기 위해 NOT IN 대신 NOT EXISTS 사용을 개발 표준으로 강제하는 것이 가장 안전합니다. 불가피하게 NOT IN을 써야 한다면 서브쿼리에 IS NOT NULL 조건을 수동으로 추가해야 합니다.
-- [권장] NOT EXISTS를 활용한 깔끔한 Hash Anti Join 유도
SELECT D.DEPT_ID, D.DEPT_NAME
FROM DEPARTMENTS D
WHERE NOT EXISTS (
SELECT /*+ UNNEST HASH_AJ */ 1
FROM EMPLOYEES E
WHERE E.DEPT_ID = D.DEPT_ID
);
-- [대안] NOT IN 사용 시 반드시 IS NOT NULL 조건 명시
SELECT D.DEPT_ID, D.DEPT_NAME
FROM DEPARTMENTS D
WHERE D.DEPT_ID NOT IN (
SELECT /*+ UNNEST HASH_AJ */ E.DEPT_ID
FROM EMPLOYEES E
WHERE E.DEPT_ID IS NOT NULL -- Null-Aware 오버헤드 제거
);
핵심 요약 및 워크로드별 선택 기준
| 비교 구분 | 권장 구문 및 힌트 전략 | 내부 오퍼레이션 | 최적의 상황 (When to use) |
| 소량 메인 + 인덱스 보유 서브 | EXISTS + /*+ UNNEST NL_SJ */ (또는 NO_UNNEST) | NESTED LOOPS SEMI / FILTER | OLTP 환경에서 메인 쿼리 결과 건수가 적고 서브쿼리 조인 키 인덱스가 완벽할 때 |
| 대용량 메인 + 대용량 서브 | EXISTS / IN + /*+ UNNEST HASH_SJ */ | HASH JOIN SEMI (RIGHT SEMI) | 배치/DW 환경에서 양쪽 테이블 모두 건수가 많아 Full Scan과 PGA 해시 결합이 유리할 때 |
| 대용량 메인 + 극소량 서브 | IN + /*+ UNNEST */ + 서브쿼리 선행(LEADING) | NESTED LOOPS (VW_NSO_1 드라이빙) | 서브쿼리에서 걸러지는 건수가 극소량이어서, 서브쿼리를 먼저 읽고 메인 테이블을 인덱스로 찌를 때 |
| 제외 조건 (차집합 연산) | NOT EXISTS + /*+ UNNEST HASH_AJ */ | HASH JOIN ANTI / NL ANTI | NULL 데이터로 인한 결과 증발(0건 반환) 버그를 예방하고 순수 Anti Join 성능을 확보할 때 |
- 결론: 오라클 CBO 환경에서 IN과 EXISTS는 Subquery Unnesting을 통해 동일한 Semi Join으로 수렴합니다. 문법 자체의 우열을 따지기보다 실행 계획에서 FILTER로 풀렸는지 SEMI/ANTI JOIN으로 풀렸는지 확인하고, 데이터 분포도에 맞춰 NL_SJ/HASH_SJ 방향을 통제하는 것이 베테랑 DBA의 핵심 튜닝 포인트입니다.