Database Reference
In-Depth Information
One of the most useful things about decomposing a large join into several smaller joins is the possibility of
parallelizing the execution. In fact, the database engine can start a separate slave process for each join. For example,
in Figure
14-14
the server process coordinates four slave processes to execute the full partition-wise join. (Chapter 15
provides detailed information about parallel processing.)
Figure
14-15
shows the results of a performance test based on the
pwj_performance.sql
script. The purpose of
the test was to reproduce an execution like the one illustrated in Figure
14-14
or, specifically, a join of two tables with
four partitions. In this particular case, the tables contained 10,000,000 and 100,000,000 rows, respectively. Note that
the degree of the parallel executions was equal to the number of partitions—that is, four.
Figure 14-15.
Response time of a two-table join with and without full partition-wise join
To recognize whether a full partition-wise join is used, it's necessary to look at the execution plan. If the partition
operation appears
before
the join operation, it means that a full partition-wise join is being used. In the following
execution plan, the
PARTITION HASH ALL
operation appears before the
HASH JOIN
operation:
SELECT *
FROM t1p, t2p
WHERE t1p.id = t2p.id
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION HASH ALL | |
|* 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL| T1P |
| 4 | TABLE ACCESS FULL| T2P |
------------------------------------
2 - access("T1P"."ID"="T2P"."ID")
