Partition-Wise Join: Full·Partial·병렬 재분배

핵심 요약

Partition-Wise Join은 큰 조인 하나를 조인 가능한 Partition 또는 Subpartition Pair별 작은 조인으로 나누는 최적화입니다.

큰 조인 1개
→ 서로 같은 Join Key 값 범위를 가진 데이터 조각을 대응
→ Pair별 작은 조인 수행
→ 병렬 조인의 재분배·Interconnect·Peak Memory 부담을 줄일 수 있음

두 유형을 먼저 구분합니다.

Full Partition-Wise Join
→ 양쪽이 Join Key로 Equipartition되었거나 Reference Partitioning 관계
→ 대응 조각끼리 직접 조인
→ Serial·Parallel 모두 가능

Partial Partition-Wise Join
→ 한쪽 Reference Table만 Join Key로 Partition
→ 다른 입력을 Reference Table의 Partition 구조에 맞춰 Runtime 재분배
→ Parallel에서만 가능

Partition-Wise Join은 어떤 데이터 조각끼리 조인할지를 정합니다. Pair 내부에서 실제 Row를 연결하는 알고리즘은 Hash Join·Nested Loops Join·Sort Merge Join 중 별도로 선택됩니다.

학습 목표

이 이론을 학습한 뒤에는 다음을 설명할 수 있어야 합니다.

1. 일반 Parallel Join의 HASH·HASH 재분배와 Broadcast 분배를 구분한다.
2. Full Partition-Wise Join의 Equipartition 조건을 판단한다.
3. Reference Partitioning이 Full 방식의 예외적 설계 수단이 되는 이유를 설명한다.
4. Partial 방식의 Reference Table과 동적 재분할 입력을 구분한다.
5. Full 방식의 Serial·Parallel 가능성과 Partial 방식의 Parallel 전용 조건을 구분한다.
6. PX SEND PARTITION (KEY)와 PQ Distrib PART (KEY)를 해석한다.
7. Partition-Wise Join과 Partition Pruning·Join Method를 구분한다.
8. Partition Pair 수와 DOP의 관계를 판단한다.
9. Join Key 편중과 Partition 편중이 PX 병목을 만드는 이유를 설명한다.
10. DBMS_XPLAN과 V$PQ_TQSTAT으로 재분배·Skew·TEMP를 검증한다.

1. 일반 Parallel Join의 데이터 분배

두 개의 큰 입력을 customer_id로 Parallel Hash Join한다고 가정합니다.

SELECT /*+ GATHER_PLAN_STATISTICS PARALLEL(8) */
       c.grade,
       SUM(s.amount)
FROM   sales s
JOIN   customers c
  ON   c.customer_id = s.customer_id
GROUP BY c.grade;

같은 customer_id의 양쪽 Row가 동일한 Consumer PX Server에서 만나야 조인할 수 있습니다. 두 입력의 크기가 비슷한 대량 조인에서는 대표적으로 양쪽을 Join Key Hash로 재분배합니다.

SALES       → PX SEND HASH ┐
                            ├→ 같은 customer_id가 같은 Consumer PX에서 Hash Join
CUSTOMERS   → PX SEND HASH ┘

이 방식은 PQ_DISTRIBUTE 관점에서 HASH, HASH 분배에 해당합니다. 다만 모든 Parallel Join이 양쪽을 재분배하는 것은 아닙니다. 한쪽 입력이 충분히 작으면 작은 입력을 모든 Consumer PX에 복제하는 Broadcast가 선택될 수 있습니다.

대형 입력 + 대형 입력
→ HASH·HASH 후보

매우 작은 입력 + 대형 입력
→ BROADCAST·NONE 또는 NONE·BROADCAST 후보

따라서 Partition-Wise Join의 이점은 단순히 PX SEND HASH가 보인다는 사실이 아니라, 현재 Workload에서 필요한 Row·Bytes 이동과 Memory·RAC Interconnect 비용을 실제로 얼마나 줄였는지로 판단합니다.

2. Full Partition-Wise Join의 원리

Full Partition-Wise Join은 두 입력이 Join Key 기준으로 Equipartition되어 있거나, 부모·자식이 Reference Partitioning 관계일 때 가능합니다.

SALES 조각 1      ↔ CUSTOMERS 조각 1
SALES 조각 2      ↔ CUSTOMERS 조각 2
SALES 조각 3      ↔ CUSTOMERS 조각 3
...

어떤 Join Key 값도 대응하지 않는 다른 조각과 조인할 필요가 없어야 합니다. 즉, 각 Pair가 동일한 Join Key 값 집합을 담당해야 합니다.

Full 방식의 핵심 조건

• Partition Key 사이의 Equijoin Predicate가 존재
• 양쪽 조각이 동일한 Join Key 값 범위를 담당
• Range·List는 대응 경계와 값 집합이 호환
• Hash는 대응 Partition 번호를 만들 수 있도록 같은 Hash Partition 구성이 필요
• Join Key의 Data Type과 비교 의미가 호환
• 또는 Reference Partitioning으로 부모·자식 Partition 배치가 보장됨

단순히 두 Table이 모두 Partitioned Table이거나 Partition 수만 같다는 사실만으로는 충분하지 않습니다.

SALES      : sale_date Range Partition
CUSTOMERS  : customer_id Hash Partition
Join       : customer_id

결론
→ customer_id 기준 대응 Pair가 보장되지 않음
→ 이 구조만으로 Full Partition-Wise Join 조건 불충족

지원 가능한 대응 수준

Full 방식은 반드시 Partition 대 Partition만 의미하지 않습니다. 설계가 호환되면 다음 대응도 가능합니다.

Partition    ↔ Partition
Partition    ↔ Subpartition
Subpartition ↔ Partition
Subpartition ↔ Subpartition

Range-Range·List-List·Hash-Hash뿐 아니라 Interval-Range·Interval-Interval과 Composite 구조도 조건을 만족하면 사용할 수 있습니다.

3. Full 방식의 실행과 DOP

Full Partition-Wise Join은 Serial과 Parallel 모두 가능합니다.

Serial
→ 대응 Pair를 한 Pair씩 순서대로 처리

Parallel
→ 여러 PX Server가 서로 다른 Pair를 동시에 처리

Parallel Full 방식에서 Join Pair가 병렬 작업의 Granule이 됩니다. 따라서 유효한 Pair 수보다 큰 DOP를 지정해도 그 단계에서 모든 PX Server가 동시에 유용한 일을 할 수는 없습니다.

유효한 Join Pair 8개, DOP 16
→ Join 단계의 동시 작업 단위는 최대 8개
→ 일부 PX가 유휴 상태가 될 수 있음

작업량이 균등하지 않다면 Pair 수가 충분해도 병목이 발생합니다. Oracle은 Hash Partition 수를 DOP보다 충분히 많게 두거나, Pair 수가 DOP와 비슷하다면 각 Pair의 크기를 균등하게 만드는 방향을 권장합니다.

실행계획 단서

Full 방식의 실행계획에서는 Join 바로 아래의 Partition Operation이 대응 조각별 조인을 나타내는 중요한 단서가 됩니다.

PX PARTITION HASH ALL
  HASH JOIN

실제 Plan 형태는 Partition 방법과 Join 순서에 따라 달라질 수 있습니다. 다음 항목을 함께 확인합니다.

• Join 바로 아래의 Partition·Subpartition Iterator
• 양쪽 Join 입력 직전의 불필요한 PX SEND HASH가 줄었는지
• Partition Start·Stop 또는 Join Filter
• 이후 Aggregate·Sort 단계의 별도 PX 분배와 혼동하지 않았는지

Full 방식은 Join 단계의 재분배를 피할 수 있는 구조이지, SQL 전체에 PX SEND가 하나도 없어야 한다는 뜻은 아닙니다.

4. Reference Partitioning과 Full 방식

Reference Partitioning은 부모 Table의 Partition 배치를 Referential Constraint를 통해 자식 Table에 상속시키는 구조입니다.

ORDERS
→ order_date Range Partition

ORDER_ITEMS
→ ORDERS와의 FK를 이용한 Reference Partition

다음 Join은 부모와 자식이 order_id로 연결되고, 대응 부모·자식 Row가 같은 Partition Pair에 배치되므로 Full Partition-Wise Join 후보가 될 수 있습니다.

SELECT o.order_date,
       SUM(i.quantity * i.unit_price)
FROM   orders o
JOIN   order_items i
  ON   i.order_id = o.order_id
GROUP BY o.order_date;

이 경우 부모의 물리 Partition Key가 order_date이고 Join Key가 order_id여도, Reference Partitioning이 부모·자식의 대응 배치를 보장합니다. 이것은 “양쪽이 직접 Join Key로 Partition되어야 한다”는 일반 조건의 중요한 예외입니다.

5. Partial Partition-Wise Join

Partial 방식은 한쪽 Table만 Join Key로 Partition되어 있어도 사용할 수 있습니다. Join Key로 이미 Partition된 입력을 Reference Table이라고 합니다.

SALES
→ customer_id Hash Partition
→ Reference Table

CUSTOMERS
→ 비파티션 또는 다른 Key로 Partition

Database는 다른 입력을 Runtime에 Reference Table의 Partition 구조와 같은 값 집합으로 재분할합니다.

CUSTOMERS Scan
→ customer_id로 대상 SALES Partition 계산
→ PX SEND PARTITION (KEY)
→ 해당 Partition을 처리하는 Consumer PX로 전송
→ SALES Partition과 Pair별 Join

Partial 방식의 장점은 Reference Table을 Join Key로 다시 이동하지 않는다는 점입니다. 일반적인 대형 HASH, HASH 조인과 비교하면 한쪽 입력만 재분배합니다.

일반 대형 Parallel Join
→ 양쪽 HASH 재분배 가능

Partial Partition-Wise Join
→ Reference Table은 기존 Partition 배치 사용
→ 다른 입력만 PARTITION 방식으로 재분배

Partial Partition-Wise Join은 Parallel에서만 수행됩니다.

실행계획 단서

대표적인 Plan 표기는 다음과 같습니다.

PX SEND PARTITION (KEY)
PQ Distrib: PART (KEY)

이는 Row의 Join Key를 이용해 Reference Table의 대상 Partition을 계산하고, 그 Partition을 담당하는 Consumer PX로 전송한다는 뜻입니다. Plan에 다음과 같은 Join Filter 구조가 함께 나타날 수도 있습니다.

PART JOIN FILTER CREATE
PX PARTITION ... JOIN-FILTER

Join Filter는 실행 중 필요한 Reference Partition을 줄이는 보조 구조일 수 있으므로, PX SEND PARTITION (KEY)와 Partition Iterator 전체를 함께 해석합니다.

6. PQ_DISTRIBUTE로 보는 Full·Partial 분배

PQ_DISTRIBUTE Hint는 Parallel Join의 Producer·Consumer 간 Row 분배 방식을 지정합니다. 대표 조합은 다음과 같습니다.

PARTITION 조합은 Partition된 쪽이 Join Key로 Partition되어 있고 Equijoin이어야 합니다. NONE, NONE은 양쪽이 Join Key로 Equipartition되어야 합니다. 조건이 맞지 않으면 Hint가 무시될 수 있습니다.

Hint는 학습·진단 시 가설을 검증하는 도구로 사용할 수 있지만, 실제 운영에서는 분배 Row 수, Skew, DOP, 통계와 다른 Join 후보까지 비교한 뒤 적용합니다.

7. Partition-Wise Join과 Join Method

Partition-Wise Join과 Hash Join은 같은 분류가 아닙니다.

Partition-Wise Join
→ 어떤 Partition·Subpartition Pair끼리 조인할지 결정

Join Method
→ 각 Pair 내부에서 Row를 어떤 알고리즘으로 연결할지 결정

따라서 다음 조합이 모두 가능합니다.

• Partition-Wise Hash Join
• Partition-Wise Nested Loops Join
• Partition-Wise Sort Merge Join

실행계획에 HASH JOIN이 보인다는 사실만으로 Partition-Wise Join이라고 판단하지 않습니다. Partition Operation과 PX SEND·PQ Distrib을 함께 확인해야 합니다.

8. Partition Pruning과의 차이

Partition Pruning은 한 입력에서 읽지 않아도 되는 Partition을 제거합니다. Partition-Wise Join은 두 입력의 대응 조각을 이용해 Pair별로 조인합니다.

Partition Pruning
→ 어느 Partition을 읽지 않을 것인가?

Partition-Wise Join
→ 읽는 데이터 조각을 어느 상대 조각과 조인할 것인가?

다음 조합이 모두 가능합니다.

모든 Partition을 읽어 Pruning 이득이 없어도, Full 방식으로 Join 재분배와 Pair별 Peak Memory를 줄일 수 있습니다.

9. Composite Partition 설계

데이터 보관·삭제는 날짜 기준이지만 지배적인 대량 Join은 고객 기준일 수 있습니다. 이때 Range-Hash Composite를 검토할 수 있습니다.

SALES
→ sale_date Range Partition
→ customer_id Hash Subpartition

CUSTOMER_MONTHLY
→ month Range Partition
→ customer_id Hash Subpartition

조건이 호환되면 다음 효과를 결합할 수 있습니다.

날짜 Predicate
→ Range Partition Pruning

customer_id Join
→ Hash 조각 Pair별 Full 또는 Partial Partition-Wise Join

단순히 Subpartition 수만 같게 만드는 것으로 충분하지 않습니다. Join Key, Hash 조각 수와 대응 번호, Range·List 경계, Data Type과 Join Predicate를 함께 맞춰야 합니다.

10. 데이터 편중과 병렬 병목

Partition-Wise 구조가 성립해도 Join Key 또는 Partition Pair별 Row 수가 크게 다르면 일부 PX Server에 일이 집중됩니다.

Pair 1: 10억 Row
Pair 2: 1천만 Row
Pair 3: 1천만 Row
...

전체 완료시간
→ 가장 큰 Pair를 처리하는 느린 PX에 의해 결정

대표 원인은 다음과 같습니다.

• 특정 고객·계정·상품 값에 Row가 집중
• NULL·Unknown·Default 값의 집중
• Range Partition별 기간과 업무량 불균형
• 낮은 NDV의 Join Key
• Pair 수와 DOP의 부조화
• 특정 Pair의 Hash Workarea Spill과 TEMP 사용
• RAC에서 Partition Affinity가 맞지 않아 Remote I/O 증가

Partition 수를 무조건 늘리는 것도 정답은 아닙니다. Segment·통계·Local Index·Partition Maintenance 비용이 함께 증가합니다.

11. 실제 실행 통계 검증

Cursor Plan

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    NULL,
    NULL,
    'ALLSTATS LAST +PARALLEL +PARTITION +PREDICATE +MEMSTATS +NOTE'
  )
);

확인 항목은 다음과 같습니다.

1. Join 바로 아래 Partition·Subpartition Operation
2. Join 입력별 PX SEND 방식과 PQ Distrib
3. PSTART·PSTOP와 Join Filter
4. Starts·A-Rows·Buffers·A-Time
5. Hash Join의 OMem·1Mem·Used-Mem·Used-Tmp
6. 최종 Aggregate·Sort에서 발생한 별도 재분배

ALLSTATS LAST는 Plan Line별 Runtime 통계를 제공하지만, PX Server별 세부 편중은 합산값만으로 숨겨질 수 있습니다.

V$PQ_TQSTAT로 PX 편중 확인

같은 Session에서 Parallel SQL 실행이 끝난 뒤 다음과 같이 확인합니다.

SELECT dfo_number,
       tq_id,
       server_type,
       process,
       num_rows,
       bytes
FROM   v$pq_tqstat
ORDER BY dfo_number DESC,
         tq_id,
         server_type,
         process;

V$PQ_TQSTAT의 Table Queue는 Plan의 PX SEND <분배방식>과 PX RECEIVE 구간에 대응합니다.

같은 TQ_ID의 PX별 NUM_ROWS·BYTES가 비슷함
→ 분배가 비교적 균등

일부 PX의 NUM_ROWS·BYTES가 압도적으로 큼
→ Join Key 또는 Partition Skew 의심

TQ별 Row·Bytes 합계는 실제 전송량을 보여 주고, PX별 분산과 최대·평균 차이는 병렬 불균형을 보여 줍니다. V$PQ_TQSTAT는 실행 Session에 종속되므로 다른 Session에서 뒤늦게 조회하지 않습니다.

12. 실행계획 판독 예시

Partial 방식

HASH JOIN
  PART JOIN FILTER CREATE
    PX RECEIVE
      PX SEND PARTITION (KEY)   PQ Distrib: PART (KEY)
        TABLE ACCESS FULL CUSTOMERS
  PX PARTITION HASH JOIN-FILTER
    TABLE ACCESS FULL SALES

CUSTOMERS
→ customer_id로 SALES Partition 담당 PX에 재분배

SALES
→ 기존 customer_id Partition 배치를 유지

HASH JOIN
→ 대응 조각별 Join

Full 방식

PX PARTITION HASH ALL
  HASH JOIN
    TABLE ACCESS FULL SALES
    TABLE ACCESS FULL CUSTOMERS

해석할 때는 다음 질문을 순서대로 확인합니다.

1. 두 입력이 Join Key로 Equipartition되었거나 Reference Partitioning 관계인가?
2. Join 바로 아래에서 대응 Partition Pair를 처리하는가?
3. Join 입력 직전의 PX SEND HASH가 제거되었는가?
4. 보이는 PX SEND HASH가 Join이 아니라 이후 GROUP BY 때문은 아닌가?
5. 실제 TQ별 전송 Row·Bytes가 줄었는가?

Plan Operation 하나만으로 단정하지 않고 DDL·Predicate·전체 Plan Tree·Runtime 통계를 함께 대조합니다.

13. 설계 판단 절차

1. 시스템에서 가장 비용이 큰 대량 Join을 찾는다.
2. Filter 후 양쪽 입력 Row·Bytes를 측정한다.
3. Join Key의 NDV·Top Frequency·NULL 집중을 확인한다.
4. 데이터 생명주기 Key와 지배적 Join Key가 같은지 판단한다.
5. 다르면 Composite 또는 Reference Partitioning을 검토한다.
6. 양쪽 Equipartition이 가능한지 확인한다.
7. 불가능하면 한쪽 Reference Table을 둔 Partial 이득을 계산한다.
8. Pair 수·DOP·Skew·RAC Affinity·Local Index 운영비를 함께 검토한다.
9. 일반 HASH·HASH, Broadcast, Full, Partial 후보를 실제로 비교한다.
10. DBMS_XPLAN·V$PQ_TQSTAT·TEMP·Interconnect 통계로 검증한다.

Partition-Wise Join은 물리 설계를 바꾸는 최적화이므로 특정 SQL 한 개의 응답시간만 보지 않습니다. Partition Maintenance, Local Index 수, 통계 수집, 작은 조회, DML, 데이터 증가까지 전체 Workload 기준으로 결정합니다.

핵심 비교