Bong's Log

만능 열쇠로 오해받는 스칼라 서브쿼리의 양면성

SQL을 작성하는 개발자들이 LEFT OUTER JOIN을 대신해 가장 즐겨 사용하는 문법이 바로 SELECT 절에 위치하는 스칼라 서브쿼리(Scalar Subquery)입니다. 메인 쿼리의 결과 집합 건수를 왜곡시키지 않으면서 코드와 명칭(Code Lookup)을 손쉽게 매핑할 수 있고, 오라클이 내부적으로 제공하는 스칼라 서브쿼리 캐싱(Scalar Subquery Caching) 기능 덕분에 일반적인 NL 아우터 조인보다 빠른 응답 속도를 보여주는 경우가 많기 때문입니다.

-- 전형적인 스칼라 서브쿼리 활용 패턴
SELECT E.EMP_ID,
       E.EMP_NAME,
       (SELECT D.DEPT_NAME 
        FROM   DEPARTMENTS D 
        WHERE  D.DEPT_ID = E.DEPT_ID) AS DEPT_NAME
FROM   EMPLOYEES E;

그러나 이 강력한 캐싱 메커니즘은 입력 값의 종류(NDV, Number of Distinct Values)와 데이터 정렬 상태를 고려하지 않고

무분별하게 사용될 경우, 오히려 일반 조인보다 훨씬 심각한 Buffer Cache 경합(cache buffers chains latch)과 CPU 점유율 급증을 초래하는 시한폭탄으로 돌변합니다.

오라클 엔진이 스칼라 서브쿼리를 어떻게 메모리에 캐싱하는지, 그리고 왜 특정 조건에서 Hash Collision(해시 충돌)로 인해 성능이 급락하는지 내부 아키텍처를 파헤쳐 봅니다.

내부 메커니즘: Query Execution Cache와 해시 테이블 동작 원리

스칼라 서브쿼리는 원칙적으로 메인 쿼리의 결과 로우 1건마다 서브쿼리를 1회씩 반복 수행하는 Iterative Execution(반복 실행) 모델로 동작합니다. 오라클은 이 반복적인 블록 I/O 오버헤드를 줄이기 위해 PGA(엄밀히는 UGA 내의 Query Execution Cache) 영역에 소규모 인메모리 해시 테이블(In-Memory Hash Table)을 생성합니다.

1. 스칼라 서브쿼리 캐싱의 3단계 처리 흐름

메인 쿼리에서 서브쿼리로 입력 값(예: E.DEPT_ID)이 전달될 때 오라클 내부에서는 다음 과정을 거칩니다.

1. Hash Bucket 탐색 (Cache Lookup): 입력된 E.DEPT_ID 값에 내부 해시 함수를 적용하여 UGA 내 해시 테이블의 버킷(Bucket)을 탐색합니다.
2. Cache Hit 시: 해시 버킷에 동일한 입력 값과 매핑된 출력 값(D.DEPT_NAME)이 이미 존재한다면, 서브쿼리의 테이블이나 인덱스를 전혀 읽지 않고 즉시 메모리의 출력 값을 반환합니다. (논리적 I/O Buffers = 0)
3. Cache Miss 시: 해시 테이블에 해당 입력 값이 없다면, 비로소 서브쿼리 블록을 실행하여 Buffer Cache에서 인덱스 및 테이블 블록을 읽고 결과 값을 가져옵니다. 이후 다음 번 호출을 위해 [입력 값 - 출력 값] 쌍을 해시 버킷에 저장합니다.

2. 내부 메모리 제한과 히든 파라미터 (_query_execution_cache_max_size)

이 캐시 공간은 무한정 늘어날 수 없으며, 단일 SQL의 실행(Execution) 수명 주기 동안만 유지되고 커서가 닫히거나 패치(Fetch)가 완료되면 즉시 해제됩니다.

• 캐시 크기를 결정하는 핵심 히든 파라미터는 _query_execution_cache_max_size이며, 기본값은 통상 65,536 Bytes (64KB) 수준입니다. (오라클 버전별로 해시 테이블 슬롯 개수는 약 256개 ~ 1,024개 내외로 제한됩니다.)
• 입출력 데이터의 컬럼 길이에 따라 다르지만, 대략 수백 개에서 천여 개 남짓의 고유한 입력 값만 해시 테이블에 온전히 담을 수 있습니다.

3. Hash Collision (해시 충돌) 발생 메커니즘

입력 값의 고유 개수(NDV)가 해시 테이블의 버킷 수를 초과하는 순간 Hash Collision(해시 충돌)이 발생합니다. 이때 오라클의 스칼라 서브쿼리 해시 테이블은 일반적인 Hash Join처럼 버킷 아래에 연결 리스트(Linked List Chain)를 길게 달아매지 않고, 단일 슬롯 덮어쓰기 또는 제한적 교체 구조로 동작합니다.

• 시나리오: 입력 값 A(DEPT_ID = 10)와 입력 값 B(DEPT_ID = 276)가 해시 함수를 거쳤을 때 동일한 해시 버킷 번호(예: Bucket #5)를 배정받았다고 가정합니다.
• 충돌 현상:
  1. 첫 번째 로우에서 10이 들어오면 Cache Miss가 발생하여 블록 I/O를 수행한 뒤 Bucket #5에 [10 : 인사팀]을 기록합니다.
  2. 두 번째 로우에서 276이 들어오면 해시 값은 Bucket #5를 가리키지만, 실제 입력 값(10 vs 276)이 다르므로 Hash Collision(Cache Miss)으로 판정됩니다.
  3. 오라클은 서브쿼리를 다시 실행해 블록 I/O를 일으키고, Bucket #5의 내용을 [276 : 재무팀]으로 덮어씁니다(Overwrite).
  4. 세 번째 로우에서 다시 10이 들어오면, 이미 276에 의해 캐시가 밀려났으므로 또다시 Cache Miss가 발생하여 서브쿼리를 재수행합니다.

핵심 요약: 입력 데이터가 정렬되지 않은 상태에서 고유 값의 종류(NDV)가 많으면, 캐시에 넣고 덮어쓰는 CPU 연산만 소모할 뿐 실제 캐시 히트율은 0%에 수렴하여 순수 NL 조인보다도 느려집니다.

주요 원인 분석: 실무에서 스칼라 서브쿼리가 독이 되는 3가지 순간

프로덕션 환경에서 스칼라 서브쿼리가 슬로우 쿼리의 주범으로 지목되는 원인은 매우 명확합니다.

1. 입력 값의 높은 카디널리티 (High NDV)

공통 코드나 부서 코드처럼 종류가 수십 개 이하인 컬럼이 아니라, 고객번호(CUST_ID), 주문번호(ORDER_ID), 상품번호(ITEM_ID)처럼 고유 값이 수만~수백만 개인 컬럼을 스칼라 서브쿼리의 입력 값으로 넣는 경우입니다. 해시 테이블 크기를 아득히 초과하여 매 로우마다 Cache Miss와 해시 덮어쓰기 오버헤드가 발생합니다.

2. 대량 배치/DW 쿼리에서 UNNEST 차단으로 인한 NL 강제 고착화

메인 쿼리 결과가 100만 건인 대량 집계 쿼리에서는 참조 테이블과 Hash Outer Join으로 한 번에 풀어야 마땅합니다. 그러나 개발자가 이를 스칼라 서브쿼리로 작성해 두면(특히 Oracle 11g 이하 환경이나 복잡한 스칼라 구조), 옵티마이저가 이를 일반 조인으로 풀어내지 못하고 100만 번의 인덱스 랜덤 액세스(Single Block I/O)를 수행하게 만듭니다.

3. 다중 컬럼 반환을 위한 동일 서브쿼리 중복 호출

스칼라 서브쿼리는 문법상 단일 행, 단일 컬럼(1 Row, 1 Column)만 반환할 수 있습니다. 만약 부서 테이블에서 부서명, 부서위치, 매니저ID 3개 컬럼을 가져와야 할 때, 개발자들이 동일한 스칼라 서브쿼리를 SELECT 절에 3번 복사해서 붙여넣으면 똑같은 테이블/인덱스 탐색과 해시 캐시 구성을 3배로 중복 수행하게 됩니다.

트러블슈팅 및 해결 방안 (튜닝 가이드)

1. 실행 계획(ALLSTATS LAST)을 통한 캐시 효율 진단

스칼라 서브쿼리의 캐싱이 제대로 작동하고 있는지 확인하려면, 반드시 메인 쿼리의 출력 로우 수(A-Rows)와 스칼라 서브쿼리 오퍼레이션의 실제 실행 횟수(Starts)를 비교해야 합니다.

-- 실행 통계 수집 후 커서 플랜 확인
SELECT /*+ GATHER_PLAN_STATISTICS */
       O.ORDER_ID,
       (SELECT C.CUST_NAME FROM CUSTOMERS C WHERE C.CUST_ID = O.CUST_ID) AS CUST_NAME
FROM   ORDERS O
WHERE  O.ORDER_DATE >= TRUNC(SYSDATE - 7);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |      1 |        |   100K |     215K|
|   1 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS   |  85420 |      1 |  85420 |     170K|
|*  2 |   INDEX UNIQUE SCAN         | PK_CUST     |  85420 |      1 |  85420 |   43200 |
|*  3 |  TABLE ACCESS FULL          | ORDERS      |      1 |   100K |   100K |    1800 |
---------------------------------------------------------------------------------------------

진단 기준:

• 정상적인 캐싱: 메인 쿼리(Id 3)의 A-Rows가 100K(10만 건)일 때, 서브쿼리(Id 1, 2)의 Starts가 30 ~ 500 수준이라면 캐시 히트율이 99% 이상인 최상의 상태입니다.
• 캐싱 실패 (튜닝 대상): 위 플랜처럼 메인 쿼리가 100K인데 서브쿼리의 Starts가 85,420번 찍히고 Buffers가 21만 블록에 달한다면, 높은 NDV와 Hash Collision으로 인해 캐시가 무력화된 상태입니다.

2. 해결책 ①: Oracle 12c 이상의 UNNEST 힌트 활용 (Scalar Subquery Unnesting)

Oracle 12c부터는 옵티마이저가 SELECT 절의 스칼라 서브쿼리도 자동으로 LEFT OUTER JOIN으로 변환하는 기능이 도입되었습니다(_optimizer_unnest_scalar_sq = TRUE). 만약 옵티마이저가 비용 계산 문제로 이를 변환하지 않았다면, 스칼라 서브쿼리 내부에 /*+ UNNEST */ 힌트를 명시하여 강제로 Hash Outer Join을 유도할 수 있습니다.

-- 스칼라 서브쿼리를 강제로 풀어내어 Hash Outer Join으로 유도
SELECT /*+ LEADING(O) USE_HASH(C@SUBQ) */
       O.ORDER_ID,
       (SELECT /*+ QB_NAME(SUBQ) UNNEST */ C.CUST_NAME 
        FROM   CUSTOMERS C 
        WHERE  C.CUST_ID = O.CUST_ID) AS CUST_NAME
FROM   ORDERS O
WHERE  O.ORDER_DATE >= TRUNC(SYSDATE - 7);

단, 애플리케이션 SQL을 직접 수정할 수 있는 상황이라면 힌트에 의존하기보다 명시적인 LEFT OUTER JOIN 구문으로 리팩토링하는 것이 가장 직관적이고 안전합니다.

3. 해결책 ②: 입력 데이터 정렬(ORDER BY)을 통한 로컬 캐싱 극대화

스칼라 서브쿼리 내부에 복잡한 로직이 있어 아우터 조인으로 풀기 어렵고, 고유 값(NDV)이 해시 버킷 크기를 약간 웃도는 수준(예: 2,000 ~ 5,000개)이라면, 인라인 뷰에서 입력 키 기준으로 먼저 정렬(ORDER BY 또는 인덱스 스캔 유도)한 뒤 스칼라 서브쿼리를 태우는 기법이 유효합니다.

SELECT ORDER_ID,
       (SELECT C.CUST_NAME FROM CUSTOMERS C WHERE C.CUST_ID = X.CUST_ID) AS CUST_NAME
FROM (
       -- 인라인 뷰에서 CUST_ID 순으로 미리 정렬하여 전달 (NO_MERGE 필수)
       SELECT /*+ NO_MERGE */ O.ORDER_ID, O.CUST_ID
       FROM   ORDERS O
       WHERE  O.ORDER_DATE >= TRUNC(SYSDATE - 7)
       ORDER BY O.CUST_ID
) X;

• 원리: 입력 값(CUST_ID)이 1, 1, 1, ..., 2, 2, 2, ..., 5000, 5000처럼 끼리끼리 뭉쳐서 들어오면, 나중에 다른 값에 의해 해시 버킷이 덮어쓰여지더라도 동일한 값은 이미 앞 단계에서 100% Cache Hit 처리된 이후이므로, 서브쿼리 실행 횟수(Starts)는 정확히 고유 값 개수(NDV)만큼만 수행되고 끝납니다.

4. 해결책 ③: 다중 컬럼 추출 시 문자열 결합(Concatenation) 또는 객체 반환

스칼라 서브쿼리 캐싱 효율이 매우 좋은 상황(낮은 NDV)에서 2~3개의 컬럼을 동시에 가져와야 한다면, 서브쿼리를 여러 개 쪼개지 말고 고정 길이 문자열(RPAD 또는 구분자)로 결합하여 1회만 호출한 뒤 바깥에서 잘라 쓰는(SUBSTR / REGEXP_SUBSTR) 고전 튜닝 기법을 적용합니다.

SELECT EMP_ID,
       EMP_NAME,
       TRIM(SUBSTR(DEPT_INFO, 1, 20))  AS DEPT_NAME,
       TRIM(SUBSTR(DEPT_INFO, 21, 10)) AS LOC_CODE
FROM (
       SELECT E.EMP_ID,
              E.EMP_NAME,
              -- 단 1회의 스칼라 서브쿼리 호출로 2개 컬럼 정보를 동시 캐싱
              (SELECT RPAD(D.DEPT_NAME, 20) || RPAD(D.LOC_CODE, 10)
               FROM   DEPARTMENTS D
               WHERE  D.DEPT_ID = E.DEPT_ID) AS DEPT_INFO
       FROM   EMPLOYEES E
);

요약

• 스칼라 서브쿼리 캐싱의 본질: UGA 메모리 내 소규모 해시 테이블(_query_execution_cache_max_size, 기본 64KB)에 [입력값-출력값]을 저장하여 반복적인 블록 I/O를 생략하는 기법입니다.
• Hash Collision 리스크: 입력 값의 종류(NDV)가 많고 정렬되어 있지 않으면 해시 버킷 충돌과 덮어쓰기(Overwrite)가 반복되어 캐시 효율이 완전히 깨지며, 대량 배치 시 심각한 랜덤 I/O 병목을 일으킵니다.
• 실무 튜닝 공식:
  • 공통 코드/부서 코드 등 소수 고유 값(Low NDV) + 소량 OLTP 조회 $\rightarrow$ 스칼라 서브쿼리 유지
  • 고객번호/주문번호 등 다수 고유 값(High NDV) + 대량 조회 $\rightarrow$ LEFT OUTER JOIN 변환 (또는 /*+ UNNEST */ 힌트 적용)
  • 아우터 조인 변환이 불가능한 중간 규모 NDV $\rightarrow$ 인라인 뷰 ORDER BY 정렬 후 스칼라 호출

개요: 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 |
--------------------------------------------------------------------------------------

1. 중복 제거 및 조기 종료(Early Exit): DEPARTMENTS의 특정 부서(DEPT_ID = 10)에 연봉 5000 이상인 사원이 1,000명 있더라도, NESTED LOOPS SEMI는 EMP_DEPT_IX 인덱스를 스캔하다가 조건에 맞는 첫 번째 로우를 찾는 순간 나머지 999건에 대한 스캔을 즉시 중단하고 다음 부서(DEPT_ID = 20)로 넘어갑니다. Buffer Cache의 논리적 블록 읽기(Buffers)를 획기적으로 줄여줍니다.
2. 조인 순서 역전 (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 오버헤드 제거
);

핵심 요약 및 워크로드별 선택 기준

• 결론: 오라클 CBO 환경에서 IN과 EXISTS는 Subquery Unnesting을 통해 동일한 Semi Join으로 수렴합니다. 문법 자체의 우열을 따지기보다 실행 계획에서 FILTER로 풀렸는지 SEMI/ANTI JOIN으로 풀렸는지 확인하고, 데이터 분포도에 맞춰 NL_SJ/HASH_SJ 방향을 통제하는 것이 베테랑 DBA의 핵심 튜닝 포인트입니다.

개요: 인라인 뷰(Inline View)가 풀리지 않을 때 벌어지는 문제

실무 SQL을 작성하다 보면 UNION ALL, GROUP BY, DISTINCT가 포함된 복잡한 뷰(View)나 인라인 뷰(Inline View)를 메인 테이블과 조인해야 하는 상황이 빈번하게 발생합니다.

일반적인 뷰라면 오라클 옵티마이저(CBO)의 쿼리 변환기(Query Transformer)가 뷰 머징(View Merging)을 수행하여 메인 쿼리와 하나로 합친 뒤 최적의 실행 계획을 수립합니다. 그러나 뷰 내부에 집계 연산이나 집합 연산자가 포함되어 뷰 머징이 불가능하거나 비용상 불리할 때, 뷰 내부 테이블을 처음부터 끝까지 다 읽고(Full Scan) 중간 집합을 만든 뒤 조인한다면 응답 속도는 기하급수적으로 느려집니다.

이때 오라클 옵티마이저가 선택하는 핵심 돌파구가 바로 JPPD (Join Predicate Pushdown, 조인 조건 푸시다운)입니다. 메인 쿼리에 있는 조인 조건(Join Predicate)을 뷰의 울타리 안쪽으로 강제로 밀어 넣어, 뷰 내부 테이블의 인덱스를 활용하고 부분 범위 처리를 가능하게 만드는 JPPD의 내부 메커니즘과 실무 튜닝 기법을 상세히 파헤쳐 봅니다.

내부 메커니즘: 옵티마이저는 어떻게 조인 조건을 뷰 안으로 밀어 넣는가?

오라클의 쿼리 변환 엔진(CBQT, Cost-Based Query Transformation)이 JPPD를 수행하는 내부 아키텍처와 실행 계획상의 변화를 단계별로 살펴봅니다.

1. View Merging 실패와 비효율적인 조인 (JPPD 미적용 시)

아래와 같이 부서별 급여 합계를 구하는 인라인 뷰와 부서 테이블을 조인하는 쿼리가 있다고 가정합니다.

SELECT D.DEPT_ID, D.DEPT_NAME, V.SUM_SAL
FROM   DEPARTMENTS D
JOIN   (SELECT DEPT_ID, SUM(SALARY) AS SUM_SAL
        FROM   EMPLOYEES
        GROUP BY DEPT_ID) V
  ON   D.DEPT_ID = V.DEPT_ID
WHERE  D.LOC_CODE = 'SEOUL';

만약 DEPARTMENTS에서 'SEOUL'에 위치한 부서가 단 1건뿐이더라도, 뷰 머징과 JPPD가 모두 실패하면 오라클은 다음과 같이 동작합니다.

1. EMPLOYEES 테이블 전체(예: 1,000만 건)를 Full Scan하여 모든 부서의 급여 합계를 PGA Sort/Hash Area에서 집계(HASH GROUP BY)합니다.
2. 생성된 거대한 뷰 중간 결과 집합(VIEW)을 DEPARTMENTS의 1건과 조인한 뒤 나머지 부서 데이터를 모두 버립니다.

2. JPPD 작동 원리와 VIEW PUSHED PREDICATE

옵티마이저가 JPPD 변환을 결정하면, 메인 쿼리의 조인 조건인 D.DEPT_ID = V.DEPT_ID를 인라인 뷰 안쪽의 WHERE 절로 침투시켜 상관 서브쿼리(Correlated Subquery)와 유사한 형태로 쿼리를 재작성합니다.

-- 옵티마이저가 내부적으로 재작성(Query Transformation)한 논리적 형태
SELECT D.DEPT_ID, D.DEPT_NAME, V.SUM_SAL
FROM   DEPARTMENTS D,
       LATERAL (SELECT DEPT_ID, SUM(SALARY) AS SUM_SAL
                FROM   EMPLOYEES E
                WHERE  E.DEPT_ID = D.DEPT_ID -- 외부의 조인 조건이 뷰 내부로 침투!
                GROUP BY DEPT_ID) V
WHERE  D.LOC_CODE = 'SEOUL';

이 변환이 완료되면 실행 계획에는 다음과 같은 고유 오퍼레이션이 등장합니다.

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | A-Rows | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |      1 |      12 |
|   1 |  NESTED LOOPS                  |             |      1 |      1 |      1 |      12 |
|*  2 |   TABLE ACCESS BY INDEX ROWID  | DEPARTMENTS |      1 |      1 |      1 |       2 |
|*  3 |    INDEX RANGE SCAN            | DEPT_LOC_IX |      1 |      1 |      1 |       1 |
|   4 |   VIEW PUSHED PREDICATE        |             |      1 |      1 |      1 |      10 |
|*  5 |    FILTER                      |             |      1 |        |      1 |      10 |
|   6 |     SORT GROUP BY              |             |      1 |      1 |      1 |      10 |
|   7 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |      1 |     15 |     12 |      10 |
|*  8 |       INDEX RANGE SCAN         | EMP_DEPT_IX |      1 |     15 |     12 |       2 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("D"."LOC_CODE"='SEOUL')
   8 - access("E"."DEPT_ID"="D"."DEPT_ID")  <-- 메인 테이블의 컬럼이 인덱스 액세스 조건으로 작동!

• 핵심 동작 순서:
  1. 선행(Driving) 테이블인 DEPARTMENTS에서 'SEOUL' 조건을 만족하는 부서 1건을 먼저 추출합니다.
  2. 추출된 D.DEPT_ID 값을 바인드 변수처럼 인라인 뷰 내부로 밀어 넣습니다 (VIEW PUSHED PREDICATE).
  3. 뷰 내부의 EMPLOYEES 테이블은 전체 스캔 대신 EMP_DEPT_IX 인덱스를 Range Scan하여 해당 부서의 사원 12건만 Buffer Cache에서 읽고 SORT GROUP BY를 마칩니다.
  4. 항상 Nested Loops 기반으로 동작: JPPD는 선행 테이블의 로우마다 뷰 내부로 값을 건네주어야 하므로, 물리적 조인 방식은 필연적으로 Nested Loops Join (USE_NL)과 결합됩니다.

3. JPPD가 작동 가능한 대표적 뷰 유형과 관련 히든 파라미터

오라클 내부에서 JPPD는 비용 기반(Cost-Based) 및 규칙 기반으로 제어되며, 다음 세 가지 히든 파라미터에 의해 지배받습니다.

• _push_join_predicate (기본값 TRUE): JPPD 기능 전체를 활성화합니다.
• _push_join_union_view (기본값 TRUE): UNION ALL 또는 UNION으로 구성된 뷰 내부의 각 브랜치(Branch)로 조인 조건을 밀어 넣습니다.
• _optimizer_push_pred_cost_based (기본값 TRUE): JPPD 적용 전후의 Cost를 계산하여 유리할 때만 선택합니다.

주요 원인 분석: 실무에서 JPPD 관련 성능 장애가 터지는 3가지 이유

JPPD는 양날의 검입니다. 작동해야 할 때 작동하지 않아서 문제 되기도 하지만, 작동하지 말아야 할 상황에서 작동하여 DB를 멈추게 하는 경우도 많습니다.

1. 문법적 제약 조건으로 인한 JPPD 불가 (Unpushable Views)

개발자가 인라인 뷰 내부에 아래와 같은 오퍼레이션을 포함시키면, 데이터 정합성이 깨지거나 구조적 한계로 인해 옵티마이저는 JPPD를 원천 포기합니다.

• 뷰 내부에 ROWNUM 슈도 컬럼이 사용된 경우 (실체화 강제)
• 뷰 내부에 분석 함수(Analytic Function: ROW_NUMBER() OVER(), RANK(), SUM() OVER())가 포함된 경우 (파티션 기준과 조인 키가 일치하지 않을 때)
• 뷰 내부에 CONNECT BY 계층형 쿼리나 CUBE / ROLLUP이 포함된 경우

2. 뷰 내부에 조인 키 인덱스가 없는 경우

JPPD는 조인 조건을 뷰 안으로 밀어 넣어 인덱스 스캔(Index Scan)을 타게 만드는 것이 유일한 목적입니다. 만약 뷰 내부 테이블에 조인 컬럼 인덱스가 없거나 인덱스 컬럼이 가공(TO_CHAR(DEPT_ID))되어 있다면, 조건을 밀어 넣어 봐야 어차피 내부에서 Full Table Scan을 반복해야 하므로 옵티마이저는 JPPD를 기각합니다.

3. 잘못된 JPPD 작동 (High Cardinality Driving)

가장 치명적인 장애 시나리오입니다. 통계정보 왜곡으로 인해 옵티마이저가 선행 테이블(Driving)의 결과 건수를 1건으로 예측하고 JPPD(VIEW PUSHED PREDICATE)를 선택했으나, 실제 런타임에 선행 테이블에서 50만 건이 추출되는 경우입니다.

• 뷰 내부의 UNION ALL이나 GROUP BY 쿼리가 50만 번 반복 실행(Starts = 500K)되면서 Buffer Cache의 논리적 I/O(Buffers)가 수천만 블록으로 치솟고 CPU가 100% 고갈됩니다.

트러블슈팅 및 해결 방안 (튜닝 가이드)

실무에서 JPPD가 풀리지 않아 느린 쿼리와, 반대로 JPPD가 잘못 작동해 느린 쿼리를 교정하는 단계별 튜닝 전략입니다.

1. PUSH_PRED 힌트를 통한 강제 JPPD 유도 (소량 Driving 시)

선행 테이블의 결과 건수가 소량이고 뷰 내부 테이블에 조인 키 인덱스가 존재함에도, 옵티마이저가 뷰 전체를 Full Scan + Hash Join으로 처리할 때는 NO_MERGE와 PUSH_PRED 힌트를 결합하여 강제로 조건을 밀어 넣습니다.

-- [튜닝 전/후 비교] UNION ALL 뷰 내부로 조인 조건을 강제 침투시키는 쿼리
SELECT /*+ LEADING(C V) USE_NL(V) NO_MERGE(V) PUSH_PRED(V) */
       C.CUST_ID, C.CUST_NAME, V.ORDER_DT, V.ORD_AMT
FROM   CUSTOMERS C
LEFT JOIN (
       SELECT /*+ QB_NAME(V_SUB) */ CUST_ID, ORDER_DT, ORD_AMT
       FROM   ORDERS_2025
       UNION ALL
       SELECT CUST_ID, ORDER_DT, ORD_AMT
       FROM   ORDERS_2026
) V ON C.CUST_ID = V.CUST_ID
WHERE  C.VIP_GRADE = 'DIAMOND'; -- 극소수(예: 10명) 고객만 조회

힌트 작성 필수 공식:

1. LEADING(선행 뷰): 반드시 조건을 제공할 테이블이 먼저 읽히도록 순서를 고정합니다.
2. USE_NL(뷰): JPPD는 Nested Loops 조인에서만 작동하므로 NL 조인을 지시합니다.
3. NO_MERGE(뷰) & PUSH_PRED(뷰): 뷰 머징을 막은 상태에서 조인 조건 푸시다운을 명령합니다.

2. NO_PUSH_PRED 힌트를 통한 JPPD 해제 (대량 Driving 시)

선행 테이블의 데이터 건수가 수만 건 이상으로 많을 때 잘못된 JPPD(VIEW PUSHED PREDICATE)가 발생해 Starts 횟수가 폭발한다면, 즉시 NO_PUSH_PRED 힌트로 푸시다운을 막고 Hash Join (USE_HASH)으로 유도해야 합니다.

-- 대량 데이터 조회 시 JPPD를 차단하고 단 1회 Full Scan + Hash Join으로 유도
SELECT /*+ LEADING(C V) USE_HASH(V) NO_MERGE(V) NO_PUSH_PRED(V) */
       C.CUST_ID, C.CUST_NAME, V.SUM_AMT
FROM   CUSTOMERS C
JOIN   (
       SELECT CUST_ID, SUM(ORD_AMT) AS SUM_AMT
       FROM   ORDERS_2026
       GROUP BY CUST_ID
) V ON C.CUST_ID = V.CUST_ID
WHERE  C.REG_DATE >= TO_DATE('20200101', 'YYYYMMDD'); -- 대량(예: 30만 명) 조회

3. 문법적 제약(ROWNUM, 분석함수) 우회 리팩토링

인라인 뷰 안에 ROW_NUMBER() OVER(PARTITION BY CUST_ID ORDER BY ORDER_DT DESC)처럼 최신 1건을 뽑는 분석 함수가 들어 있을 때, 만약 PARTITION BY 절에 조인 키(CUST_ID)가 정확히 명시되어 있다면 오라클은 JPPD를 정상적으로 수행할 수 있습니다.

그러나 PARTITION BY가 누락되어 있거나 ROWNUM으로 작성되어 JPPD가 막혀 있다면, 아래와 같이 쿼리를 리팩토링하여 해결합니다.

• 해결 방안 A: 분석 함수의 PARTITION BY 절에 메인 쿼리와의 조인 키 컬럼을 명시적으로 추가하여 옵티마이저에게 푸시다운 안전성을 보장해 줍니다.
• 해결 방안 B (Oracle 12c 이상): 인라인 뷰를 CROSS APPLY 또는 OUTER APPLY (Lateral View) 문법으로 변환하고 내부에서 FETCH FIRST 1 ROW ONLY를 사용하면, JPPD 제약 없이 깔끔하게 인덱스 부분 범위 처리가 가능합니다.

-- Oracle 12c 이상: OUTER APPLY를 활용해 JPPD 제약을 완벽히 우회하는 기법
SELECT C.CUST_ID, C.CUST_NAME, V.ORDER_ID, V.ORD_AMT
FROM   CUSTOMERS C
OUTER APPLY (
       SELECT O.ORDER_ID, O.ORD_AMT
       FROM   ORDERS O
       WHERE  O.CUST_ID = C.CUST_ID -- 조인 조건을 뷰 내부에 직접 기술
       ORDER BY O.ORDER_DT DESC
       FETCH FIRST 1 ROW ONLY       -- ROWNUM 제약 없이 최신 1건만 인덱스 스캔!
) V
WHERE  C.VIP_GRADE = 'DIAMOND';

요약 

• JPPD(Join Predicate Pushdown)의 정의: 뷰 머징(View Merging)이 불가능한 GROUP BY, UNION ALL, Outer Join 뷰 내부로 메인 쿼리의 조인 조건을 밀어 넣어 인덱스 액세스를 가능케 하는 쿼리 변환 기법입니다.
• 실행 계획과 동작 특징: 항상 NESTED LOOPS 조인과 짝을 이루며, 실행 계획의 VIEW 또는 VIEW PUSHED PREDICATE 하단 오퍼레이션에서 메인 테이블의 조인 키가 인덱스 access 조건으로 사용되는지 확인해야 합니다.
• 실무 튜닝 핵심 기준:
  • 선행(Driving) 집합이 소량이고 뷰 내부 테이블에 조인 키 인덱스가 있을 때 $\rightarrow$ /*+ NO_MERGE(V) USE_NL(V) PUSH_PRED(V) */
  • 선행(Driving) 집합이 대량이어서 반복 실행(Starts) 부하가 극심할 때 $\rightarrow$ /*+ NO_MERGE(V) USE_HASH(V) NO_PUSH_PRED(V) */