개요: 인라인 뷰(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가 모두 실패하면 오라클은 다음과 같이 동작합니다.
- EMPLOYEES 테이블 전체(예: 1,000만 건)를 Full Scan하여 모든 부서의 급여 합계를 PGA Sort/Hash Area에서 집계(HASH GROUP BY)합니다.
- 생성된 거대한 뷰 중간 결과 집합(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") <-- 메인 테이블의 컬럼이 인덱스 액세스 조건으로 작동!
- 핵심 동작 순서:
- 선행(Driving) 테이블인 DEPARTMENTS에서 'SEOUL' 조건을 만족하는 부서 1건을 먼저 추출합니다.
- 추출된 D.DEPT_ID 값을 바인드 변수처럼 인라인 뷰 내부로 밀어 넣습니다 (VIEW PUSHED PREDICATE).
- 뷰 내부의 EMPLOYEES 테이블은 전체 스캔 대신 EMP_DEPT_IX 인덱스를 Range Scan하여 해당 부서의 사원 12건만 Buffer Cache에서 읽고 SORT GROUP BY를 마칩니다.
- 항상 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를 계산하여 유리할 때만 선택합니다.
| 뷰(View) 구성 형태 | JPPD 가능 여부 | 실행 계획 표시 특징 |
| UNION ALL / UNION 포함 뷰 | 가능 | VIEW 오퍼레이션 하위의 각 UNION-ALL 브랜치마다 인덱스 액세스 발생 |
| GROUP BY / DISTINCT 포함 뷰 | 가능 | VIEW PUSHED PREDICATE 오퍼레이션 명시적 출력 |
| Outer Join (+, LEFT JOIN) 대상 뷰 | 가능 | Outer Join의 후행(Inner) 집합으로 위치한 뷰 내부로 조건 침투 |
| Anti / Semi Join 대상 뷰 | 가능 | NOT EXISTS, EXISTS 내의 복잡한 뷰 내부로 조건 침투 |
주요 원인 분석: 실무에서 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명) 고객만 조회
힌트 작성 필수 공식:
- LEADING(선행 뷰): 반드시 조건을 제공할 테이블이 먼저 읽히도록 순서를 고정합니다.
- USE_NL(뷰): JPPD는 Nested Loops 조인에서만 작동하므로 NL 조인을 지시합니다.
- 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) */