포스트

UNION ALL 을 PARTITION BY 로 변환 (Partition Outer Join)

게시
By 반지현
읽는 시간 10분
UNION ALL 을 PARTITION BY 로 변환 (Partition Outer Join)

같은 데이터에 대해 여러 키 (예: 부서별) 의 outer join 결과를 UNION ALL 로 합치던 패턴은 PARTITION BY 절을 OUTER JOIN 에 결합 하여 한 번의 쿼리로 풀 수 있습니다. plan 에는 MERGE JOIN PARTITION OUTER 가 등장하며 dimension 테이블 access 가 1 회로 줄어듭니다.

핵심 정리

  1. PARTITION OUTER JOIN 의 정의: ANSI SQL 표준 (Oracle 10gR2+) 으로, OUTER JOIN 에 PARTITION BY 를 결합하여 각 파티션 키마다 outer 측 row 를 보장 합니다. 시계열 빈 기간 채우기 (dense reporting) 의 표준 패턴입니다.
  2. 변환 효과: UNION ALL 로 N 번 outer join 하던 패턴이 1 번의 PARTITION OUTER JOIN 으로 풀리면서 dimension 테이블 access 가 N → 1 회로 줄어듭니다.
  3. plan signature: MERGE JOIN PARTITION OUTER + SORT PARTITION JOIN 이 등장하면 partition outer join 이 적용된 것입니다.

시나리오

부서별 (deptno = 10, deptno = 20) 월별 매출을 조회. year_month 는 모든 월을 가지고 있고 dept_sale_history 는 매출이 발생한 월만 가집니다. 매출이 없는 월도 0 으로 채워서 출력해야 하는 dense reporting 시나리오.

테이블건수인덱스
year_month4 (2002 년 1~4 월)pk_year_month (yymm)
dept_sale_history6pk_dept_sale_history (deptno, yymm), idx_dept_sale_history_yymm (yymm, deptno)

테스트 데이터 생성 SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- year_month 테이블 (모든 월)
CREATE TABLE year_month (
    yymm VARCHAR2(6),
    CONSTRAINT pk_year_month PRIMARY KEY (yymm)
);

INSERT INTO year_month VALUES ('200201');
INSERT INTO year_month VALUES ('200202');
INSERT INTO year_month VALUES ('200203');
INSERT INTO year_month VALUES ('200204');

-- dept_sale_history 테이블 (매출이 발생한 월만)
CREATE TABLE dept_sale_history (
    deptno   NUMBER,
    yymm     VARCHAR2(6),
    sale_amt NUMBER,
    CONSTRAINT pk_dept_sale_history PRIMARY KEY (deptno, yymm)
);
CREATE INDEX idx_dept_sale_history_yymm ON dept_sale_history (yymm, deptno);

-- deptno 10: 200201, 200203
INSERT INTO dept_sale_history VALUES (10, '200201', 1000);
INSERT INTO dept_sale_history VALUES (10, '200203', 1500);

-- deptno 20: 200202, 200204
INSERT INTO dept_sale_history VALUES (20, '200202', 2000);
INSERT INTO dept_sale_history VALUES (20, '200204', 2500);

-- deptno 30 (본 쿼리에서는 제외)
INSERT INTO dept_sale_history VALUES (30, '200201', 3000);
INSERT INTO dept_sale_history VALUES (30, '200203', 3500);

COMMIT;

기대 결과: deptno 10, 20 각각 4 개월 × 2 부서 = 8 행 (매출 없는 월은 sale_amt = 0).

1) 원본 — UNION ALL 로 deptno 별 OUTER JOIN

1
2
3
4
5
6
7
8
9
10
11

SELECT e.deptno, m.yymm, NVL(e.sale_amt, 0) AS sale_amt
  FROM year_month m
  LEFT OUTER JOIN dept_sale_history e
    ON (m.yymm = e.yymm AND e.deptno = 10)
 WHERE m.yymm LIKE '2002%'
UNION ALL
SELECT e.deptno, m.yymm, NVL(e.sale_amt, 0) AS sale_amt
  FROM year_month m
  LEFT OUTER JOIN dept_sale_history e
    ON (m.yymm = e.yymm AND e.deptno = 20)
 WHERE m.yymm LIKE '2002%';

year_month 를 두 번 access 하면서 각 분기마다 dept_sale_history 와 outer join 합니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

--------------------------------------------------------------------------------------------------------------------
| Id | Operation                              | Name                       | Rows | Bytes | Cost (%CPU) | Time     |
--------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                       |                            |    8 |   288 |      4  (0) | 00:00:01 |
|  1 |  UNION-ALL                             |                            |      |       |             |          |
|  2 |   NESTED LOOPS OUTER                   |                            |    4 |   144 |      2  (0) | 00:00:01 |
|* 3 |    INDEX RANGE SCAN                    | PK_YEAR_MONTH              |    4 |    20 |      2  (0) | 00:00:01 |
|  4 |    TABLE ACCESS BY INDEX ROWID BATCHED | DEPT_SALE_HISTORY          |    1 |    31 |      0  (0) | 00:00:01 |
|* 5 |     INDEX RANGE SCAN                   | IDX_DEPT_SALE_HISTORY_YYMM |    1 |       |      0  (0) | 00:00:01 |
|  6 |   NESTED LOOPS OUTER                   |                            |    4 |   144 |      2  (0) | 00:00:01 |
|* 7 |    INDEX RANGE SCAN                    | PK_YEAR_MONTH              |    4 |    20 |      2  (0) | 00:00:01 |
|  8 |    TABLE ACCESS BY INDEX ROWID BATCHED | DEPT_SALE_HISTORY          |    1 |    31 |      0  (0) | 00:00:01 |
|* 9 |     INDEX RANGE SCAN                   | IDX_DEPT_SALE_HISTORY_YYMM |    1 |       |      0  (0) | 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Id 3 과 Id 7 가 같은 PK_YEAR_MONTH 인덱스를 두 번 RANGE SCAN 합니다. 부서가 더 늘어날수록 access 가 비례 증가.

2) 수정 — PARTITION BY 로 한 번의 PARTITION OUTER JOIN

1
2
3
4
5
6

SELECT e.deptno, m.yymm, NVL(e.sale_amt, 0) AS sale_amt
  FROM year_month m
  LEFT OUTER JOIN dept_sale_history e
       PARTITION BY (e.deptno) ON (m.yymm = e.yymm)
 WHERE m.yymm LIKE '2002%'
   AND e.deptno IN (10, 20);

ANSI SQL 표준의 partitioned outer join 구문. PARTITION BY (e.deptno) 가 핵심입니다.

1
2
3
4
5
6
7
8
9
10
11
12
13

----------------------------------------------------------------------------------------------------------------
| Id | Operation                                | Name                 | Rows | Bytes | Cost (%CPU) | Time     |
----------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                         |                      |    4 |   124 |      7 (29) | 00:00:01 |
|  1 |  VIEW                                    |                      |    4 |   124 |      7 (29) | 00:00:01 |
|  2 |   MERGE JOIN PARTITION OUTER             |                      |    4 |   144 |      7 (29) | 00:00:01 |
|  3 |    SORT JOIN                             |                      |    4 |    20 |      3 (34) | 00:00:01 |
|* 4 |     INDEX RANGE SCAN                     | PK_YEAR_MONTH        |    4 |    20 |      2  (0) | 00:00:01 |
|* 5 |    SORT PARTITION JOIN                   |                      |    4 |   124 |      3 (34) | 00:00:01 |
|  6 |     INLIST ITERATOR                      |                      |      |       |             |          |
|  7 |      TABLE ACCESS BY INDEX ROWID BATCHED | DEPT_SALE_HISTORY    |    4 |   124 |      2  (0) | 00:00:01 |
|* 8 |       INDEX RANGE SCAN                   | PK_DEPT_SALE_HISTORY |    1 |       |      2  (0) | 00:00:01 |
----------------------------------------------------------------------------------------------------------------
  • Id 2 의 MERGE JOIN PARTITION OUTER 가 partition outer join 의 signature 입니다.
  • Id 5 의 SORT PARTITION JOIN 은 deptno 파티션 단위로 dim 측을 정렬한 단계.
  • Id 4 의 PK_YEAR_MONTH access 가 1 회만 발생 (원본은 2 회).
  • Id 6 ~ Id 8 의 INLIST ITERATOR 가 deptno IN (10, 20) 처리로 두 deptno 의 PK 인덱스를 RANGE SCAN 합니다.

분석

일반 LEFT OUTER JOIN 은 outer 측 row 와 매칭되는 inner row 가 없을 때 단일 NULL row 를 반환합니다. PARTITION OUTER JOIN 은 여기서 한 단계 더 나아가 각 파티션 키마다 outer 측 row 를 보장 합니다.

  • year_month 4 행 (모든 월)
  • dept_sale_history 의 deptno 10 은 200201, 200203 만 매출 있음
  • 일반 LEFT JOIN year_month m / dept_sale_history e ON yymm AND deptno=10 → 4 행 (월별로 deptno 10 의 매칭 또는 NULL)
  • PARTITION BY (deptno) 추가 → 4 행 × 2 부서 = 8 행 (각 부서별로 모든 월이 보장됨)

이것이 dense reporting 의 핵심: dimension (year_month) 의 모든 값이 각 partition (deptno) 별로 빠짐없이 출력.

Time Series 빈 기간 채우기 패턴

dense reporting 의 가장 흔한 사용 사례는 시계열 데이터의 빈 기간 채우기 입니다.

시계열 형태일반 LEFT JOIN 결과PARTITION OUTER JOIN 결과
부서별 월별 매출매출 있는 월만모든 월 (매출 없으면 0)
직원별 일별 출근출근일만모든 일 (결근일 표시)
상품별 시간대 판매판매 시간대만모든 시간대 (판매 0 표시)

PARTITION BY 위치의 의미

ANSI SQL 표준 구문은 다음과 같습니다.

1
2
3

-- 형태 1: outer 가 파티션 안 (이번 글의 형태)
year_month m LEFT OUTER JOIN dept_sale_history e
  PARTITION BY (e.deptno) ON (m.yymm = e.yymm)

year_month 의 모든 row 가 dept_sale_history.deptno 의 각 파티션마다 매칭됩니다. 즉 year_month × distinct(deptno) cartesian-like 구조 + 매칭.

일반 OUTER JOIN 과 PARTITION OUTER JOIN 비교

[일반 LEFT OUTER JOIN]
m × e (deptno=10) on yymm


deptno 가 매번 달라야 하면
UNION ALL N 회 필요
[PARTITION OUTER JOIN]
m × distinct(e.deptno) × e on yymm
  ^ deptno 파티션마다 m 의 모든 row

deptno 별 outer 보장이 자동
한 번의 join 으로 끝

Partition Outer Join 은 dense reporting (빈 기간 / 빈 카테고리 채우기) 의 표준 ANSI SQL 패턴입니다. UNION ALL 로 partition 별 outer join 을 N 번 작성하던 패턴이 보이면 즉시 변환을 검토할 수 있는 강력한 방법.

변환 효과 비교

항목원본 (UNION ALL)수정 (PARTITION OUTER JOIN)
year_month access2 회1 회
dept_sale_history access2 회 (각 deptno)1 회 (INLIST IN 10,20)
Plan 형태UNION-ALL + NL OUTER × 2MERGE JOIN PARTITION OUTER
부서 N 개로 확장 시UNION ALL N 회 (linear)한 번의 PARTITION OUTER JOIN

부서 수가 늘어날수록 효과가 비례 증가합니다.

정리

  1. UNION ALL + 같은 outer 측 + 다른 partition 키 패턴은 LEFT OUTER JOIN ... PARTITION BY (key) ON ... 의 ANSI SQL 표준 구문으로 변환할 수 있습니다.
  2. plan signature 는 MERGE JOIN PARTITION OUTER + SORT PARTITION JOIN 입니다. 이 두 operation 이 보이면 partition outer join 이 적용된 것입니다.
  3. 활용 영역: 시계열 데이터의 빈 기간 채우기, 카테고리별 dense reporting 등 dimension 의 모든 값을 partition 별로 보장 해야 하는 모든 케이스에 적합합니다.
SQL Tuning, 케이스 스터디
oracle partition-outer-join query-rewriting union-all dense-reporting
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.