When you join two large partitioned tables, the database performs a full cross-partition merge by default — including joins between partitions whose date ranges never overlap. These invalid joins consume CPU and memory without producing results. Partition-wise join eliminates this waste by splitting the join into smaller, independent joins between matching partition pairs, which significantly reduces query execution time.
How it works
When you join two partitioned tables on their partition keys, the query planner matches partitions with the same key range and joins them independently. For example, the Q1 partition of one table joins only the Q1 partition of the other — never Q2, Q3, or Q4. This eliminates the invalid joins that occur in a full cross-partition merge.
Without partition-wise join, the planner performs a single Merge Join across all partitions of both tables. Invalid joins — such as joining sales_y2023q1 with measurement_y2023q3 on an equi-join condition — produce empty results but still consume CPU and memory.
Enable partition-wise join
Run the following statement to enable partition-wise join for the current session:
set enable_partitionwise_join to on;Example
The following example uses two range-partitioned tables — measurement and sales — to show the difference in query plans before and after enabling partition-wise join.
Set up the example tables
CREATE TABLE measurement(
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2023q1 PARTITION OF measurement
FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
CREATE TABLE measurement_y2023q2 PARTITION OF measurement
FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');
CREATE TABLE measurement_y2023q3 PARTITION OF measurement
FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');
CREATE TABLE measurement_y2023q4 PARTITION OF measurement
FOR VALUES FROM ('2023-10-01') TO ('2024-04-01');
CREATE TABLE sales (
dept_no number,
part_no varchar2,
country varchar2(20),
date date,
amount number
) PARTITION BY RANGE (date);
CREATE TABLE sales_y2023q1 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
CREATE TABLE sales_y2023q2 PARTITION OF sales
FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');
CREATE TABLE sales_y2023q3 PARTITION OF sales
FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');
CREATE TABLE sales_y2023q4 PARTITION OF sales
FOR VALUES FROM ('2023-10-01') TO ('2024-04-01');Both tables are partitioned by a date range into four quarterly partitions for 2023.
Query plan without partition-wise join
Run the following query and view the execution plan:
explain select a.* from sales a join measurement b on a.date = b.logdate where b.unitsales > 10;When partition-wise join is disabled, the planner uses a Merge Join across all partitions:
QUERY PLAN
--------------------------------------------------------------------------------------------------
Aggregate (cost=871.75..871.76 rows=1 width=8)
-> Merge Join (cost=448.58..812.79 rows=23587 width=32)
Merge Cond: (a.date = b.logdate)
-> Sort (cost=185.83..191.03 rows=2080 width=40)
Sort Key: a.date
-> Append (cost=0.00..71.20 rows=2080 width=40)
-> Seq Scan on sales_y2023q1 a (cost=0.00..15.20 rows=520 width=40)
-> Seq Scan on sales_y2023q2 a_1 (cost=0.00..15.20 rows=520 width=40)
-> Seq Scan on sales_y2023q3 a_2 (cost=0.00..15.20 rows=520 width=40)
-> Seq Scan on sales_y2023q4 a_3 (cost=0.00..15.20 rows=520 width=40)
-> Sort (cost=262.75..268.42 rows=2268 width=8)
Sort Key: b.logdate
-> Append (cost=0.00..136.34 rows=2268 width=8)
-> Seq Scan on measurement_y2023q1 b (cost=0.00..31.25 rows=567 width=8)
Filter: (unitsales > 10)
-> Seq Scan on measurement_y2023q2 b_1 (cost=0.00..31.25 rows=567 width=8)
Filter: (unitsales > 10)
-> Seq Scan on measurement_y2023q3 b_2 (cost=0.00..31.25 rows=567 width=8)
Filter: (unitsales > 10)
-> Seq Scan on measurement_y2023q4 b_3 (cost=0.00..31.25 rows=567 width=8)
Filter: (unitsales > 10)
(21 rows)This plan joins all four partitions of sales with all four partitions of measurement. Joins such as sales_y2023q1 with measurement_y2023q3 are invalid — their date ranges don't overlap, so the join condition always produces empty results.
Query plan with partition-wise join
Enable partition-wise join and run the same query:
set enable_partitionwise_join to on;
explain select a.* from sales a join measurement b on a.date = b.logdate where b.unitsales > 10; QUERY PLAN
----------------------------------------------------------------------------------------
Append (cost=21.70..453.33 rows=5896 width=128)
-> Hash Join (cost=21.70..105.96 rows=1474 width=128)
Hash Cond: (b.logdate = a.date)
-> Seq Scan on measurement_y2023q1 b (cost=0.00..31.25 rows=567 width=8)
Filter: (unitsales > 10)
-> Hash (cost=15.20..15.20 rows=520 width=128)
-> Seq Scan on sales_y2023q1 a (cost=0.00..15.20 rows=520 width=128)
-> Hash Join (cost=21.70..105.96 rows=1474 width=128)
Hash Cond: (b_1.logdate = a_1.date)
-> Seq Scan on measurement_y2023q2 b_1 (cost=0.00..31.25 rows=567 width=8)
Filter: (unitsales > 10)
-> Hash (cost=15.20..15.20 rows=520 width=128)
-> Seq Scan on sales_y2023q2 a_1 (cost=0.00..15.20 rows=520 width=128)
-> Hash Join (cost=21.70..105.96 rows=1474 width=128)
Hash Cond: (b_2.logdate = a_2.date)
-> Seq Scan on measurement_y2023q3 b_2 (cost=0.00..31.25 rows=567 width=8)
Filter: (unitsales > 10)
-> Hash (cost=15.20..15.20 rows=520 width=128)
-> Seq Scan on sales_y2023q3 a_2 (cost=0.00..15.20 rows=520 width=128)
-> Hash Join (cost=21.70..105.96 rows=1474 width=128)
Hash Cond: (b_3.logdate = a_3.date)
-> Seq Scan on measurement_y2023q4 b_3 (cost=0.00..31.25 rows=567 width=8)
Filter: (unitsales > 10)
-> Hash (cost=15.20..15.20 rows=520 width=128)
-> Seq Scan on sales_y2023q4 a_3 (cost=0.00..15.20 rows=520 width=128)
(25 rows)The planner now creates four independent Hash Joins — one per matching quarter: sales_y2023q1 with measurement_y2023q1, sales_y2023q2 with measurement_y2023q2, and so on. Invalid cross-quarter joins are eliminated entirely, and the total estimated cost drops from 871.76 to 453.33.