パーティションキーとパーティション単位結合の使用を確認

コマンド

-- 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

概要

結合キーで等価パーティション化された2つの表は、パーティションのペアごとに結合でき(フルパーティション単位結合)、パラレルクエリでの再分散トラフィックとメモリが大幅に削減される。計画の形からPWJが使用されているかがわかる。

使用する場面

DWHで大規模なファクト対ファクトまたはファクト対ディメンション結合を設計するとき、パラレルクエリで大量のPX再分散(PX Deq Credit: send blkd)が見られるとき、パーティション化戦略の検証に。

注意

ライセンス: Partitioningオプション(EEの有償オプション)。パラレルクエリにはEnterprise Editionが必要。EXPLAIN PLANは推定であり、実際の計画にはDISPLAY_CURSOR/SQL Monitorを使用する。フルPWJには同じパーティション数(ハッシュでは2の累乗を推奨)と一致するキーデータ型が必要。PWJはシリアル計画にも現れるが、実際の効果はパラレル実行で得られる。