DBAtlas · Database operations atlas

Verify partition keys and partition-wise join usage

OraclePartitioning

Command

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

What it does

Two tables equi-partitioned on the join key can be joined partition pair by partition pair (full partition-wise join), which sharply reduces redistribution traffic and memory in parallel queries. The plan shape shows whether PWJ is used.

When to use it

When designing large fact-to-fact or fact-to-dimension joins in a DWH, when parallel queries show heavy PX redistribution (PX Deq Credit: send blkd), and when validating the partitioning strategy.

Caution

License: Partitioning option (extra-cost EE); parallel query requires Enterprise Edition. EXPLAIN PLAN is an estimate; use DISPLAY_CURSOR/SQL Monitor for the actual plan. Full PWJ needs the same partition counts (a power of two is recommended for hash) and matching key data types. PWJ can appear in serial plans too, but the real gain is in parallel execution.

Open in DBAtlas →

Related