Oracle Partition - Partition-Wise Join (PWJ)

Card Puncher Data Processing


The most fundamental parallel execution optimization is a partition-wise join.

If two rather large tables are often joined together in SQL statements, consider the potential benefits of partition-wise joins.

A partition wise join is a join between two tables that are partitioned on the same column with the same partitioning scheme.

In shared nothing architecture, the partitioned data are located on a specific node.


Here is a query to find out the revenue numbers and advertising expenditures for a specific period for each product:

    sales s, 
    advertising a
    s.sales_dt = a.ad_dt
and t.product_code = a.product_code
and sales_dt between '1-aug-05' and '1-sep-05'
group by t.product_code;

Here the tables are joined on the columns SALES_DT and AD_DT, both date columns. To enhance performance, you might decide to partition both tables in the same way. In this case, range partitioning on the columns being joined and partitioning on the same ranges is the most effective.

-- script for creating the ADVERTISING table with this range partition.
create table advertising (
	ad_id	number,
	ad_dt	date,
	product_code	number,
	ad_cost	number,
	channel	varchar2(10)
partition by range (ad_dt)
  partition y05q1 values less than (to_date('04/01/2005','mm/dd/yyyy')) tablespace y05q1,
  partition y05q2 values less than (to_date('07/01/2005','mm/dd/yyyy')) tablespace y05q2,
  partition y05q3 values less than (to_date('10/01/2005','mm/dd/yyyy')) tablespace y05q3,
  partition y05q4 values less than (to_date('01/01/2006','mm/dd/yyyy')) tablespace y05q4,
  partition pmax values less than (maxvalue) tablespace y05q4

Similarly, if you often join these tables on the PRODUCT_CODE column, then that column should be a strong candidate for the partition key. And because product code values are in a specific set, the partitioning scheme should be list partitioning.

Documentation / Reference

Recommended Pages
Card Puncher Data Processing
Oracle - Partitions (Table and Indexes)

partition in Oracle. Partitioning enables you to store one logical object – a table or index – transparently in several independent segments. Partitioning can provide great performance improvements...
Data System Architecture
Relational Data Modeling - Normal Forms

A relational database is : in First Normal Form (1NF) if each attribute is single-valued with atomic values. in Second Normal Form (2NF) if it is in 1NF and each attribute that is not a primary key...

Share this page:
Follow us:
Task Runner