Benefits of Partition-Wise Joins

Partition-wise joins offer benefits described in this section:
■ Reduction of Communications Overhead
■ Reduction of Memory Requirements


Reduction of Communications Overhead
When executed in parallel, partition-wise joins reduce communications overhead. This
is because, in the default case, parallel execution of a join operation by a set of parallel execution servers requires the redistribution of each table on the join column into disjoint subsets of rows. These disjoint subsets of rows are then joined pair-wise by a single parallel execution server.

Oracle can avoid redistributing the partitions because the two tables are already
partitioned on the join column. This enables each parallel execution server to join a
pair of matching partitions.

This improved performance from using parallel execution is even more noticeable in
Oracle Real Application Clusters configurations with internode parallel execution.
Partition-wise joins dramatically reduce interconnect traffic. Using this feature is for large DSS configurations that use Oracle Real Application Clusters.
Currently, most Oracle Real Application Clusters platforms, such as MPP and SMP
clusters, provide limited interconnect bandwidths compared with their processing
powers. Ideally, interconnect bandwidth should be comparable to disk bandwidth, but
this is seldom the case. As a result, most join operations in Oracle Real Application
Clusters experience high interconnect latencies without parallel execution of
partition-wise joins.

Reduction of Memory Requirements
Partition-wise joins require less memory than the equivalent join operation of the
complete data set of the tables being joined.
In the case of serial joins, the join is performed at the same time on a pair of matching partitions. If data is evenly distributed across partitions, the memory requirement is divided by the number of partitions. There is no skew.
In the parallel case, memory requirements depend on the number of partition pairs
that are joined in parallel. For example, if the degree of parallelism is 20 and the number of partitions is 100, 5 times less memory is required because only 20 joins of
two partitions are performed at the same time. The fact that partition-wise joins
require less memory has a direct effect on performance. For example, the join probably does not need to write blocks to disk during the build phase of a hash join.

Posted in: Partitioning