파티션 키 및 partition-wise join 사용 확인

명령어

-- 1) Do both tables have the same partitioning type, key and count?
SELECT p.owner, p.table_name, p.partitioning_type, k.column_name,
       p.partition_count, p.subpartitioning_type
FROM   dba_part_tables p
  JOIN dba_part_key_columns k
    ON k.owner = p.owner AND k.name = p.table_name AND k.object_type = 'TABLE'
WHERE  p.owner = 'CUSTOMER_APP'
  AND  p.table_name IN ('CUSTOMER_H', 'ORDERS_H');

-- 2) Explain the join and inspect the plan shape:
EXPLAIN PLAN FOR
SELECT /*+ PARALLEL(4) */ m.city, SUM(s.amount)
FROM   customer_app.customer_h m
  JOIN customer_app.orders_h s ON s.customer_id = m.id
GROUP  BY m.city;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC +PARALLEL +PARTITION'));
-- Full PWJ: HASH JOIN sits BELOW the PARTITION HASH ALL line (PX PARTITION HASH ALL in parallel)
-- Partial PWJ: PX SEND PARTITION (KEY) on one side only
-- No PWJ: PX SEND HASH / BROADCAST on both sides

하는 일

조인 키로 equi-partition된 두 테이블은 파티션 쌍 단위로 조인할 수 있으며(full partition-wise join), 이는 병렬 쿼리의 재분배 트래픽과 메모리를 크게 줄인다. 계획 형태로 PWJ 사용 여부를 확인한다.

사용 시점

DWH에서 대형 fact-fact 또는 fact-dimension 조인을 설계할 때, 병렬 쿼리에서 PX 재분배가 많을 때(PX Deq Credit: send blkd), 파티셔닝 전략을 검증할 때.

주의

라이선스: Partitioning 옵션(유료 EE). 병렬 쿼리는 Enterprise Edition이 필요하다. EXPLAIN PLAN은 추정치이므로 실제 계획은 DISPLAY_CURSOR/SQL Monitor를 사용한다. full PWJ에는 같은 파티션 수(hash는 2의 거듭제곱 권장)와 일치하는 키 데이터 타입이 필요하다. PWJ는 직렬 계획에도 나타날 수 있지만 실제 이득은 병렬 실행에서 나온다.