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:
select
s.product_code,
sum(amount),
sum(ad_cost)
from
sales s,
advertising a
where
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.