Join Predicate Push Down (JPPD) 는 Semi Join 상황에도 SubQuery 에 침투 가능

EXISTS 서브쿼리가 UNNEST 로 평탄화되어 SEMI JOIN 으로 풀릴 때도 JPPD 가 동작합니다. Unnest → Semi 변환 → JPPD 의 3단 변환 순서, 그리고 Predicate Information 위치로 push 여부를 판별하는 법을 정리합니다.

게시 2026/04/30

By 반지현

읽는 시간 5분

JPPD 는 Semi Join 상황에서도 동작합니다. UNNEST 가 EXISTS 서브쿼리를 view 로 평탄화한 직후, 그 view 안으로 push 되어 인덱스 탐색 키로 사용됩니다.

핵심 정리

WHERE 조건절의 서브쿼리가 Semi Join 으로 풀리는 상황 에서도 Join Predicate Push Down (JPPD) 가 발생할 수 있습니다.

원본 쿼리

SELECT /*+ QB_NAME(MAIN) */
       e.*
  FROM employee e
 WHERE e.job_id = 'AD_ASST'
   AND EXISTS (SELECT /*+ QB_NAME(SUB) UNNEST */ 1   -- ← Semi Join 유도를 위한 UNNEST 힌트
                 FROM department d, location l
                WHERE d.department_id = e.department_id
                  AND d.location_id   = l.location_id);

실행 계획

---------------------------------------------------------------------------
| Id | Operation                       | Name       | Rows | Bytes | Cost |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |            |    1 |    72 |    3 |
|  1 |  NESTED LOOPS SEMI              |            |    1 |    72 |    3 |
|  2 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEE   |    1 |    68 |    2 |
|* 3 |    INDEX RANGE SCAN             | EMP_JOB_IX |    1 |       |    1 |
|  4 |   VIEW PUSHED PREDICATE         | VW_SQ_1    |    1 |     4 |    1 |  ← JPPD 발생 plan
|  5 |    NESTED LOOPS                 |            |    1 |    10 |    1 |
|  6 |     TABLE ACCESS BY INDEX ROWID | DEPARTMENT |    1 |     7 |    1 |
|* 7 |      INDEX UNIQUE SCAN          | DEPT_ID_PK |    1 |       |    0 |
|* 8 |     INDEX UNIQUE SCAN           | LOC_ID_PK  |   23 |    69 |    0 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."JOB_ID"='AD_ASST')
7 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")  ← VIEW 내부에 외부 술어가 침투해 인덱스 탐색 키로 사용됨
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

Id 4 의 VIEW PUSHED PREDICATE 가 핵심 — VW_SQ_1 은 UNNEST 가 EXISTS 서브쿼리를 평탄화하면서 옵티마이저가 자동 생성한 view 입니다. 그 위로 e.department_id 가 push 되어 view 안의 NL 로 들어갔습니다.

옵티마이저는 어떻게 이 plan 에 도달했는가

1) 변환 순서 — Unnest → Semi → JPPD

본 plan 의 변환 체인은 세 단계입니다.

Subquery Unnesting — UNNEST 힌트가 EXISTS 서브쿼리를 인라인 뷰 (VW_SQ_1) 로 평탄화합니다. 이 단계가 풀리지 않으면 EXISTS 는 FILTER 형 (correlated subquery) 으로 남아 outer 의 각 row 마다 서브쿼리를 재실행합니다.
Semi Join 변환 — 평탄화된 뷰와 outer 의 조인을 SEMI JOIN 으로 결정합니다.
Join Predicate Pushdown — outer 의 join 술어가 unnested view 안의 join 키로 존재하면 push 후보가 됩니다. cost 비교로 push 와 no-push 중 저렴한 쪽이 선택됩니다.

1 번이 풀리지 않으면 2 / 3 단계는 평가조차 되지 않습니다. 그래서 UNNEST 가 모든 후속 변환의 첫 번째 트리거입니다.

2) JPPD 가 발동 가능했던 조건

다음 조건이 모두 만족된 결과입니다.

Semi Join 의 inner 측이 view 형태: VW_SQ_1 이 unnesting 결과 view 로 만들어져 push 의 대상이 됩니다.
외부 join 키가 inner view 의 base 컬럼으로 직접 매핑: e.department_id 가 view 안의 d.department_id 와 1:1 매칭됩니다.
view 내부에 결과집합 의존 함수 부재: RANK, ROWNUM 같이 입력 행 집합에 의존하는 함수가 있으면 push 시 결과가 달라지므로 옵티마이저가 차단합니다. 본 view 는 단순 조인뿐이라 push 가 안전합니다.
cost 가 유리: outer 가 작고 (e.job_id = 'AD_ASST' 로 1 row 추정), inner 가 PK 인덱스 access 로 1 회 lookup 이 가능합니다.

3) JPPD 와 access path 의 관계

JPPD 가 push 후보가 되면 옵티마이저는 push plan 과 no-push plan 의 cost 를 비교합니다. 본 케이스는 outer 1 row + inner PK 인덱스 (DEPT_ID_PK, LOC_ID_PK UNIQUE SCAN) 조합이라 push 후 NL 의 비용이 거의 0 이므로 push 가 압도적으로 유리합니다.

outer 가 컸다면 HASH JOIN SEMI 가 유리해지고, 그 경우에는 push 안 하는 쪽이 cost 가 낮을 가능성이 큽니다. JPPD 의 적용 여부는 inner access path 가 인덱스 driven 한가에 강하게 의존 합니다.

JPPD 는 SEMI JOIN 의 inner view 에도 push 되지만, view 내부에 결과집합 의존 연산이 없고 inner access path 가 인덱스 driven 한 cost 조건이 맞아야 발동합니다.

SQL Tuning, 케이스 스터디