Database Reference
In-Depth Information
Parallel Joins
All join methods can be executed in parallel. However, as shown in Figure
14-12
, they scale differently. Depending on
the degree of parallelism, one method may be faster than the other. So, to pick out the best join method, it's essential
to know both whether parallel processing is used and what the degree of parallelism is. (Chapter 15 covers parallel
processing.)
Figure 14-12.
Comparison of the performance with different degrees of parallelism. This figure shows a two-table join
(50K and 94M rows) executed on a system with 8 cores
Partition-wise Joins
A
partition-wise join
(which shouldn't be confused with a partitioned outer join) is an optimization technique that
the query optimizer applies to merge and hash joins only. Partition-wise joins are used to reduce the amount of CPU,
memory, and, in case of RAC, network resources used to process joins. The basic idea is to divide a large join into
several smaller joins. Partition-wise joins can be full or partial. The following sections describe these two alternatives.
All queries used as examples are provided in the
pwj.sql
script.
■
partition-wise joins require partitioned tables. Consequently, they're available only when the partitioning option
in enterprise edition is used.
Note
Full Partition-wise Joins
To illustrate the operation of a full partition-wise join, let's begin by describing how a join without this optimization
is performed. Figure
14-13
shows a join between two partitioned tables. A single join of all rows of the two tables is
executed by a single server process.
