UNION ALL 사용시 공통으로 사용하는 테이블을 분리

게시 2026/04/30

By 반지현

읽는 시간 8분

Join Factorization (JF) 은 UNION ALL 의 위 / 아래 분기에서 공통으로 쓰이는 테이블 을 view 밖으로 끌어내어 한 번만 access 하도록 변환하는 옵티마이저 변환입니다. 본 글의 시나리오에서 SALES 풀 스캔이 2 회에서 1 회로 줄어들어 비용 또한 절반으로 떨어집니다.

핵심 정리

UNION ALL 양쪽 분기에 등장하는 같은 테이블을 view 밖으로 분리하여 한 번만 access 하도록 옵티마이저가 자동 변환합니다. 분기에 남는 것은 분기마다 다른 부분 (예: 다른 필터 조건의 dimension) 입니다.
효과: 공통 테이블이 클수록 (Fact 테이블 등) 효과가 큽니다. 같은 풀 스캔을 N 회 → 1 회로 줄여 I/O 가 1/N 로 떨어집니다.
적용 신호: plan 에 자동 생성된 view 이름 VW_JF_SET$xxx 가 등장하면 JF 가 적용된 것입니다. 이 view 는 옵티마이저가 만든 inline view 로, 분기 차이만 안에 남고 공통 테이블은 밖으로 빠집니다.

시나리오

SALES (대용량 fact, 약 90 만 건) 와 CHANNELS (소용량 데이터) 를 join 한 결과를 두 채널 (channel_id = 3, channel_id = 9) 에 대해 UNION ALL 로 합치는 쿼리. 양쪽 분기 모두에서 SALES 가 똑같이 등장하며, 분기 차이는 CHANNELS 의 필터 값뿐입니다.

테이블	건수	인덱스
`SALES`	약 897K	(파티셔닝, full scan)
`CHANNELS`	작음	`CHANNELS_PK (channel_id)`

SALES 가 양쪽에서 풀 스캔되면 같은 데이터를 두 번 읽는 명백한 중복이 발생합니다. JF 는 이를 자동으로 해결합니다.

1) 원본 — UNION ALL 양쪽에 SALES 가 그대로 등장

SELECT /*+ USE_HASH(c s) */
       s.prod_id, s.cust_id, s.quantity_sold,
       s.amount_sold, c.channel_desc
  FROM sales s, channels c
 WHERE c.channel_id = s.channel_id
   AND c.channel_id = 3
UNION ALL
SELECT /*+ USE_HASH(c s) */
       s.prod_id, s.cust_id, s.quantity_sold,
       s.amount_sold, c.channel_desc
  FROM sales s, channels c
 WHERE c.channel_id = s.channel_id
   AND c.channel_id = 9;

사람이 보면 양쪽이 거의 같지만 c.channel_id 만 다릅니다. 옵티마이저가 JF 를 적용하지 않으면 SALES 가 두 번 풀 스캔되는 비효율이 발생합니다.

옵티마이저는 cost 비교 후 본 케이스에서는 JF 를 자동 적용하여 다음 plan 을 만듭니다.

-----------------------------------------------------------------------------------
| Id | Operation                       | Name               | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |                    |      |       |  495 |
|* 1 |  HASH JOIN                      |                    | 449K |   20M |  495 |
|  2 |   VIEW                          | VW_JF_SET$0A277F6D |    2 |    50 |    2 |
|  3 |    UNION-ALL                    |                    |      |       |      |
|  4 |     TABLE ACCESS BY INDEX ROWID | CHANNELS           |    1 |    13 |    1 |
|* 5 |      INDEX UNIQUE SCAN          | CHANNELS_PK        |    1 |       |    0 |
|  6 |     TABLE ACCESS BY INDEX ROWID | CHANNELS           |    1 |    13 |    1 |
|* 7 |      INDEX UNIQUE SCAN          | CHANNELS_PK        |    1 |       |    0 |
|  8 |   PARTITION RANGE ALL           |                    | 897K |   18M |  489 |  ← SALES 가 view 밖으로 빠져 1 회만 풀 스캔
|  9 |    TABLE ACCESS FULL            | SALES              | 897K |   18M |  489 |
-----------------------------------------------------------------------------------

Predicate Information:
----------------------
   1 - access("ITEM_1"="S"."CHANNEL_ID")
   5 - access("C"."CHANNEL_ID"=3)
   7 - access("C"."CHANNEL_ID"=9)

핵심 신호는 두 가지입니다.

Id 2 의 VW_JF_SET$0A277F6D 가 옵티마이저가 JF 로 자동 생성한 inline view 의 이름입니다.
Id 9 의 SALES 가 view 밖에 위치 (UNION-ALL 자식이 아닌 HASH JOIN 의 다른 자식) 하므로 풀 스캔 1 회만 발생합니다.

2) 변환 후 형태 — 옵티마이저 내부 변환을 SQL 로 표현

옵티마이저가 자동으로 풀어낸 결과를 사람 손으로 명시적으로 작성하면 다음과 같습니다.

SELECT s.prod_id    AS prod_id,
       s.cust_id    AS cust_id,
       s.quantity_sold,
       s.amount_sold,
       vw_jf_set$0a277f6d.item_2 AS channel_desc
  FROM (SELECT c.channel_id   AS item_1,
               c.channel_desc AS item_2
          FROM channels c
         WHERE c.channel_id = 3
        UNION ALL
        SELECT c.channel_id   AS item_1,
               c.channel_desc AS item_2
          FROM channels c
         WHERE c.channel_id = 9) vw_jf_set$0a277f6d,
       sales s
 WHERE vw_jf_set$0a277f6d.item_1 = s.channel_id;

핵심 변화:

인라인 view (vw_jf_set$0a277f6d) 안에 CHANNELS 의 두 분기 UNION ALL 만 남음.
SALES 가 view 밖으로 빠져 view 와 hash join.
item_1, item_2 는 옵티마이저가 자동 부여하는 컬럼 alias.

원본과 변환 후는 결과가 완전히 동일 합니다. 본 케이스의 두 분기는 CHANNELS 의 필터만 다르고 SALES 와의 join 패턴이 같으므로, view 밖으로 분리해도 의미가 보존됩니다.

분석

JF 가 동작하는 조건

옵티마이저가 JF 를 자동 적용하는 조건은 다음과 같습니다.

UNION ALL 또는 UNION 으로 결합된 분기 사이에 공통 테이블 이 있음 (같은 테이블 + 같은 join 조건).
분기별 차이가 view 안에 표현 가능 함. 본 케이스에서는 CHANNELS 의 필터 값만 다름.
Cost 비교 후 유리하다고 판단 됨. 공통 테이블이 작거나 분기가 적으면 JF 가 안 일어날 수 있습니다.

_optimizer_join_factorization 히든 파라미터로 동작을 제어할 수 있습니다 (Oracle 11g+ 기본 TRUE).

-- 환경 점검
SHOW PARAMETER optimizer_join_factorization;

-- 세션에서만 끄기 (변환 차단 테스트)
ALTER SESSION SET "_optimizer_join_factorization" = FALSE;

JF 는 OLAP / DW 환경의 UNION ALL 패턴에서 큰 효과를 냅니다. Fact 테이블이 공통이면 사실상 1/N 비용 으로 떨어집니다. 의도와 다르게 자동 적용 / 미적용 되는 경우 NO_FACTORIZE_JOIN 또는 FACTORIZE_JOIN 힌트로 명시 제어가 가능합니다.

정리

Join Factorization 은 UNION ALL 양쪽 분기의 공통 테이블을 view 밖으로 분리 하여 같은 테이블의 중복 access 를 피하는 옵티마이저 변환입니다.
plan 에 VW_JF_SET$xxx view 이름이 등장 하면 JF 가 적용된 것입니다. 공통 테이블은 변환 view 밖에서 한 번만 access.
당연한 결과지만, 공통 테이블이 클수록 효과도 큽니다.

SQL Tuning, 케이스 스터디

핵심 정리

시나리오

1) 원본 — UNION ALL 양쪽에 SALES 가 그대로 등장

2) 변환 후 형태 — 옵티마이저 내부 변환을 SQL 로 표현

분석

JF 가 동작하는 조건

정리

태그