As with full partition-wise joins, the prime partitioning method for the sales table is to use the range method on column time_id. This is because sales is a typical example of a table that stores historical data. To enable a partial partition-wise join while preserving this range partitioning, subpartition sales by hash on column cust_id using 16 subpartitions for each partition. Both pruning and partial partition-wise joins can be used if a query joins customers and sales and if the query has a selection predicate on time_id.
When the sales table is composite partitioned, the granule of parallelism for a partial partition-wise join is a hash partition and not a subpartition. Refer to Figure 3-2 for an illustration of a hash partition in a composite table. Again, the number of hash partitions should be a multiple of the degree of parallelism. Also, on an MPP system, ensure that each hash partition has affinity to a single node. In the previous example, the eight subpartitions composing a hash partition should have affinity to the same node.
This section is based on range-hash, but it also applies for all other combinations of composite partial partition-wise joins.
The following example shows the execution plan for the query between sales and customers with sales range partitioned by time_id and subpartitioned by hash on cust_id.
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | | | P->S | QC (RAND) |
|* 3 | FILTER | | | | PCWC | |
| 4 | HASH GROUP BY | | | | PCWP | |
| 5 | PX RECEIVE | | | | PCWP | |
| 6 | PX SEND HASH | :TQ10001 | | | P->P | HASH |
| 7 | HASH GROUP BY | | | | PCWP | |
|* 8 | HASH JOIN | | | | PCWP | |
| 9 | PART JOIN FILTER CREATE | :BF0000 | | | PCWP | |
| 10 | PX RECEIVE | | | | PCWP | |
| 11 | PX SEND PARTITION (KEY) | :TQ10000 | | | P->P | PART (KEY) |
| 12 | PX BLOCK ITERATOR | | | | PCWC | |
| 13 | TABLE ACCESS FULL | CUSTOMERS | | | PCWP | |
| 14 | PX PARTITION RANGE ITERATOR| | 8 | 9 | PCWC | |
| 15 | PX PARTITION HASH ALL | | 1 | 16 | PCWC | |
|* 16 | TABLE ACCESS FULL | SALES | 113 | 144 | PCWP | |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(COUNT(SYS_OP_CSR(SYS_OP_MSR(COUNT(*)),0))>100)
8 - access("S"."CUST_ID"="C"."CUST_ID")
16 - filter("S"."TIME_ID"<=TO_DATE(' 1999-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"S"."TIME_ID">=TO_DATE(' 1999-07-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))