Database Reference
In-Depth Information
at all. During execution, the database engine starts two sets of parallel slaves. The first reads the nonpartitioned table
(Table 2) and distributes the data according to the join key. The second receives the data from the first set, and each slave
reads one partition of the partitioned table and then performs its part of the join.
Figure 14-16. Joining two tables with a partial partition-wise join
To recognize whether a partial partition-wise join is used, it's necessary to look at the execution plan. If the PX
SEND operation is of type PARTITION (KEY) , it means that a partial partition-wise join is being used. In the following
example, operation 7 provides that information:
SELECT /*+ ordered use_hash(t2p) pq_distribute(t2p none partition) */ *
FROM t1p, t2p
WHERE t1p.id = t2p.id
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 |
|* 3 | HASH JOIN BUFFERED | |
| 4 | PX PARTITION HASH ALL | |
| 5 | TABLE ACCESS FULL | T1P |
| 6 | PX RECEIVE | |
| 7 | PX SEND PARTITION (KEY)| :TQ10000 |
| 8 | PX BLOCK ITERATOR | |
| 9 | TABLE ACCESS FULL | T2P |
------------------------------------------------
Search WWH ::




Custom Search