Join Predicate Push Down(JPPD) 는 UNION ALL & UNION VIEW 가 있을 때 가능

게시 2026/04/30

By 반지현

읽는 시간 10분

선행 테이블과 후행 집합이 NESTED LOOPS JOIN 으로 수행될 때, 후행 집합이 Inline View / View 면 옵티마이저는 join predicate 를 그 안으로 push down 하여(JPPD) 인덱스 액세스 경로를 만듭니다. UNION ALL VIEW 는 그 6가지 발생 조건 중 하나입니다.

핵심 정리

JPPD 발생 조건: 선행 ↔ 후행이 NESTED LOOPS JOIN 으로 수행될 때만. HASH JOIN / SORT-MERGE JOIN 으로 수행되면 JPPD 는 동작하지 않습니다.
JPPD 가 가능한 후행 집합 6가지 (Inline View 또는 View): UNION ALL/UNION, OUTER JOIN, 랭킹 분석 함수, GROUP BY/DISTINCT, NL SEMI/ANTI, MULTI LEVEL.
효과: 후행 VIEW 안의 각 분기 SELECT 가 인덱스 RANGE SCAN 으로 변환되어, 선행 N건 × 후행 인덱스 액세스 N회 의 NL 패턴 가능. 선행 건수가 적을 때 압도적으로 빠릅니다.

6가지 JPPD 가능 패턴

패턴	한 줄 함의
UNION ALL VIEW & UNION VIEW	분기별 독립 SELECT — predicate push down 이 수학적 동치. 본 글의 시나리오
OUTER JOIN VIEW	NULL 보존 의미 유지하며 inner 쪽 predicate 를 안으로 push down
랭킹 분석 함수 사용 VIEW	partition-by 컬럼이 push down predicate 와 같으면 가능
GROUP BY, DISTINCT 사용 VIEW	grouping key 가 push down predicate 컬럼을 포함해야 동치 — 가장 까다로움
NL SEMI/ANTI JOIN VIEW	EXISTS / NOT EXISTS unnesting 후 outer 쪽 predicate push down
MULTI LEVEL VIEW	중첩 inline view — 한 단계씩 재귀적으로 push down

이 글은 첫 번째 패턴 (UNION ALL VIEW) 을 실측 비교로 다룹니다.

시나리오

같은 테이블 · 같은 인덱스 · 같은 쿼리. 단 하나만 다릅니다 — NO_PUSH_PRED 힌트로 JPPD 차단 vs PUSH_PRED 힌트로 JPPD 유도.

테이블 / 인덱스 현황

테이블	건수	인덱스	블록 수
`ORDERS`	3,000,000 (2008 ~ 2012년)	`IX_ORDERS_N2 (EMPLOYEE_ID, ORDER_DATE)`	19,791
`ORDER_ITEMS`	≈ 18,000,000	`IX_ORDER_ITEMS_PK (ORDER_ID, PRODUCT_ID)`	85,277
`ORDER_ITEMS_RETURN`	≈ 3,000,000	`IX_ORDER_ITEMS_RETURN_PK (ORDER_ID, PRODUCT_ID)`	14,370

공통 쿼리 (힌트만 다름)

SELECT /*+ LEADING(A B) <hint> */
       TO_CHAR(A.ORDER_DATE, 'YYYYMMDD') ORDER_DATE
     , B.PRODUCT_ID
     , SUM(UNIT_PRICE * QUANTITY) SALES_AMT
  FROM ORDERS A,
      (SELECT ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY
         FROM ORDER_ITEMS
       UNION ALL
       SELECT ORDER_ID, PRODUCT_ID, UNIT_PRICE, QUANTITY
         FROM ORDER_ITEMS_RETURN) B
 WHERE A.ORDER_ID    = B.ORDER_ID
   AND A.ORDER_DATE >= TO_DATE('20120701', 'YYYYMMDD')
   AND A.ORDER_DATE <  TO_DATE('20120801', 'YYYYMMDD')
   AND A.EMPLOYEE_ID = 'E070'
 GROUP BY TO_CHAR(A.ORDER_DATE, 'YYYYMMDD'), B.PRODUCT_ID;

힌트 차이만:

원본: NO_PUSH_PRED(B) — JPPD 차단 → HASH JOIN
수정: USE_NL(B) PUSH_PRED(B) — NL JOIN + JPPD 유도

1) 원본 — `NO_PUSH_PRED(B)` 로 JPPD 차단

JPPD 가 차단되면 옵티마이저는 후행 VIEW 를 통째로 빌드 한 뒤 HASH JOIN 합니다. UNION ALL 양쪽 분기 (ORDER_ITEMS 17M + ORDER_ITEMS_RETURN 3M) 가 모두 인덱스로 풀 스캔되어 약 20M 건이 메모리에 쌓입니다.

---------------------------------------------------------------------------------------------------
| Id | Operation                         | Name                     | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                  |                          |      1 |    121 |   99084 |
|  1 |  HASH GROUP BY                    |                          |      1 |    121 |   99084 |
|* 2 |   HASH JOIN                       |                          |      1 |    142 |   99084 |
|  3 |    TABLE ACCESS BY INDEX ROWID    | ORDERS                   |      1 |     20 |      22 |
|* 4 |     INDEX RANGE SCAN              | IX_ORDERS_N2             |      1 |     20 |       3 |
|  5 |    VIEW                           |                          |     20 |    20M |   99062 |
|  6 |     UNION ALL                     |                          |     20 |    20M |   99062 |
|  7 |      INDEX RANGE SCAN             | IX_ORDER_ITEMS_PK        |     20 |    17M |   84844 |
|  8 |      INDEX RANGE SCAN             | IX_ORDER_ITEMS_RETURN_PK |     20 |  2997K |   14218 |
---------------------------------------------------------------------------------------------------

Id 5 의 VIEW 가 20M 건을 통째로 빌드 한 것이 핵심 비효율입니다 — Buffers 99,062 의 99% 가 이 한 작업에 소모됩니다. ORDERS 의 결과가 단 20건이라는 사실을 옵티마이저가 후행 VIEW 안쪽으로 전달하지 못한 결과입니다.

2) 수정 — `PUSH_PRED(B)` + `USE_NL(B)` 로 JPPD 유도

옵티마이저는 A.ORDER_ID = B.ORDER_ID predicate 를 UNION ALL VIEW 안쪽 두 분기에 각각 push down 합니다. 이제 후행 VIEW 는 WHERE ORDER_ID = :선행값 으로 변환되어 PK 인덱스 RANGE SCAN 이 가능해집니다.

---------------------------------------------------------------------------------------------------
| Id | Operation                         | Name                     | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                  |                          |      1 |    121 |     281 |
|  1 |  HASH GROUP BY                    |                          |      1 |    121 |     281 |
|  2 |   NESTED LOOPS                    |                          |      1 |    142 |     281 |
|  3 |    TABLE ACCESS BY INDEX ROWID    | ORDERS                   |      1 |     20 |      22 |
|* 4 |     INDEX RANGE SCAN              | IX_ORDERS_N2             |      1 |     20 |       3 |
|  5 |    VIEW                           |                          |     20 |    142 |     259 |
|  6 |     UNION ALL PUSHED PREDICATE    |                          |     20 |    142 |     259 |
|  7 |      TABLE ACCESS BY INDEX ROWID  | ORDER_ITEMS              |     20 |    142 |     259 |
|* 8 |       INDEX RANGE SCAN            | IX_ORDER_ITEMS_PK        |     20 |    124 |     181 |
|  9 |      TABLE ACCESS BY INDEX ROWID  | ORDER_ITEMS_RETURN       |     20 |     18 |      78 |
|*10 |       INDEX RANGE SCAN            | IX_ORDER_ITEMS_RETURN_PK |     20 |     18 |      60 |
---------------------------------------------------------------------------------------------------

핵심 신호:

Id 6 의 UNION ALL PUSHED PREDICATE — JPPD 가 적용되었다는 옵티마이저의 명시적 키워드
Id 8 / Id 10 의 Starts = 20 — 선행 ORDERS 의 20건만큼 정확히 후행 인덱스를 20번 두드립니다
Buffers 99,084 → 281 (≈352배 절감), A-Rows 20M → 142

분석

왜 `NO_PUSH_PRED` 는 비효율적인가

옵티마이저는 후행이 VIEW 면 두 갈래 중 하나를 택합니다.

VIEW 결과 전체를 빌드 후 HASH/SORT-MERGE JOIN — 후행이 큰 집합이면 폭망
join predicate 를 VIEW 안으로 push down 후 NL JOIN (= JPPD) — 후행 인덱스가 있을 때 유리

NO_PUSH_PRED 는 1번을 강제합니다. 이 시나리오는 후행이 20M 건짜리 거대 집합 이라 1번이 폭망하는 전형 케이스입니다.

왜 UNION ALL VIEW 에서 JPPD 가 동작하는가

UNION ALL 은 분기별로 독립된 SELECT 를 단순 결합합니다. 즉, 외부 predicate 를 각 분기 SELECT 의 WHERE 로 옮김.

SELECT * FROM (S1 UNION ALL S2) WHERE p
  ≡ (SELECT * FROM S1 WHERE p) UNION ALL (SELECT * FROM S2 WHERE p)

UNION (중복 제거) 도 동치성이 성립하므로 JPPD 가능합니다. 반면 GROUP BY 가 끼면 grouping key 가 push down predicate 컬럼을 포함하지 않을 때 결과값이 다를수 있어, 옵티마이저가 더 보수적으로 판단합니다.

NL 조인 회수 = 선행 건수

수정본 Id 8 / Id 10 의 Starts = 20 이 본질입니다. 선행 ORDERS 의 20건이 후행 인덱스를 20번 두드립니다.

선행 건수가 적을 때 압도적으로 빠름 (이 케이스: 20건 → 281 buffers)
선행 건수가 많아지면 NL 의 N×M 폭발로 다시 HASH JOIN 이 유리해짐

JPPD 는 단순한 “최적화” 가 아니라, 옵티마이저에게 “이 후행 VIEW 는 작은 집합으로 좁힐 수 있다” 는 정보를 추가로 주는 행위입니다. 선행 건수가 작다는 보장이 있으면 PUSH_PRED 가 정답, 그렇지 않으면 옵티마이저의 자동 선택을 신뢰하세요.

Buffers 비교

단계	원본 (`NO_PUSH_PRED`)	수정 (`PUSH_PRED`)	차이
ORDERS 액세스	22	22	동일
후행 VIEW	99,062	259	383배 절감
합계	99,084	281	352배 절감

정리

세 줄로 압축하면:

후행이 VIEW 인 NL 조인 에서 인덱스 액세스 경로를 만들고 싶으면 PUSH_PRED(view_alias) + USE_NL(view_alias) 한 쌍으로 유도할 것.
JPPD 는 6가지 VIEW 패턴 (UNION ALL/UNION, OUTER JOIN, 분석함수, GROUP BY/DISTINCT, NL SEMI/ANTI, MULTI LEVEL) 일 때만 가능. 그 외는 NO_MERGE + 다른 전략 필요.
실행계획에 UNION ALL PUSHED PREDICATE / VIEW PUSHED PREDICATE 키워드가 등장하면 JPPD 적용. VIEW 행의 A-Rows 가 후행 테이블 전체 건수에 가까우면 미적용 의심.

SQL Tuning, 케이스 스터디