Database Reference
In-Depth Information
The previous execution plan shows a serial full partition-wise join. The following shows the execution plan used
with parallel processing for the very same SQL statement. Also in this case, the
PX PARTITION HASH ALL
operation
appears before the
HASH JOIN
operation. Notice how you can use the
pq_distribute
hint to instruct the query
optimizer to use a full partition-wise join (Chapter 15 provides more information about the operations used for
parallel processing):
SELECT /*+ ordered use_hash(t2p) pq_distribute(t2p none none) */ *
FROM t1p, t2p
WHERE t1p.id = t2p.id
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 |
| 3 | PX PARTITION HASH ALL| |
|* 4 | HASH JOIN | |
| 5 | TABLE ACCESS FULL | T1P |
| 6 | TABLE ACCESS FULL | T2P |
--------------------------------------------
4 - access("T1P"."ID"="T2P"."ID")
Because full partition-wise joins require two equi-partitioned tables, special care is necessary to use this
optimization technique during physical database design. In other words, it's common to equi-partition tables that are
expected to be frequently subject to massive joins. If you don't equi-partition them, you won't be able to benefit from
full partition-wise joins.
■
Two list-partitioned tables are considered equi-partitioned not only when both tables have the same number of
partitions defined on the same list of values, but also when the partitions are defined in the same order. The
pwj_list.sql
script illustrates this limitation.
Caution
It's also important to note that all partitioning methods are supported and that a full partition-wise join can join
partitions to subpartitions. To illustrate, let's say you have two tables:
sales
and
customers
. The join key is
customer_id
.
If both tables are hash partitioned, have the same number of partitions, and both use the join key as the partition key,
it's possible to take advantage of full partition-wise joins. Keep in mind that it's often a requirement to partition a table
like
sales
(in other words, a table containing historical data) with range partitioning. In such a situation, it's possible
to use composite partitioning for the
sales
table. This composite partitioning is done at the partition level with a
range, and at the subpartition level with a hash in order to meet both requirements. Thus, the full partition-wise join is
performed between the hash partitions of the
customers
table with the subpartitions of the
sales
table.
Partial Partition-wise Joins
In contrast to full partition-wise joins, partial partition-wise joins don't require two equi-partitioned tables. In addition,
only one table must be partitioned according to the join key; the other table (which can be partitioned or not) is
