Oracle Database can perform partial partition-wise joins only in parallel. Unlike full partition-wise joins, partial partition-wise joins require you to partition only one table on the join key, not both tables. The partitioned table is referred to as the reference table. The other table may or may not be partitioned. Partial partition-wise joins are more common than full partition-wise joins.
To execute a partial partition-wise join, the database dynamically partitions or repartitions the other table based on the partitioning of the reference table. After the other table is repartitioned, the execution is similar to a full partition-wise join.
The following example shows a call detail records table, cdrs, in a typical data warehouse scenario. The table is interval-hash partitioned.
CREATE TABLE cdrs
( id NUMBER
, cust_id NUMBER
, from_number VARCHAR2(20)
, to_number VARCHAR2(20)
, date_of_call DATE
, distance VARCHAR2(1)
, call_duration_in_s NUMBER(4)
) PARTITION BY RANGE(date_of_call)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY HASH(cust_id)
SUBPARTITIONS 16
(PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2005','dd-MON-yyyy')))
PARALLEL;
The cdrs table is joined with the nonpartitioned callers table on the cust_id column to rank the customers who spent the most time making calls.
EXPLAIN PLAN FOR
SELECT c.cust_id
, c.cust_last_name
, c.cust_first_name
, AVG(call_duration_in_s)
, COUNT(1)
, DENSE_RANK() OVER
(ORDER BY (AVG(call_duration_in_s) * COUNT(1)) DESC) ranking
FROM callers c
, cdrs cdr
WHERE cdr.cust_id = c.cust_id
AND cdr.date_of_call BETWEEN TO_DATE('01-JAN-2006','dd-MON-yyyy')
AND TO_DATE('31-DEC-2006','dd-MON-yyyy')
GROUP BY c.cust_id
, c.cust_last_name
, c.cust_first_name
ORDER BY ranking;
The execution plans shows a partial partition-wise join. In the plan, the columns Rows, Bytes, Cost (%CPU), Time, and TQ have been removed.
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW NOSORT | | | | | |
| 2 | PX COORDINATOR | | | | | |
| 3 | PX SEND QC (ORDER) | :TQ10002 | | | P->S | QC (ORDER) |
| 4 | SORT ORDER BY | | | | PCWP | |
| 5 | PX RECEIVE | | | | PCWP | |
| 6 | PX SEND RANGE | :TQ10001 | | | P->P | RANGE |
| 7 | HASH GROUP BY | | | | PCWP | |
|* 8 | HASH JOIN | | | | PCWP | |
| 9 | PART JOIN FILTER CREATE | :BF0000 | | | PCWP | |
| 10 | BUFFER SORT | | | | PCWC | |
| 11 | PX RECEIVE | | | | PCWP | |
| 12 | PX SEND PARTITION (KEY) | :TQ10000 | | | S->P | PART (KEY) |
| 13 | TABLE ACCESS FULL | CALLERS | | | | |
| 14 | PX PARTITION RANGE ITERATOR| | 367 | 731 | PCWC | |
| 15 | PX PARTITION HASH ALL | | 1 | 16 | PCWC | |
|* 16 | TABLE ACCESS FULL | CDRS | 5857 | 11696 | PCWP | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("CDR"."CUST_ID"="C"."CUST_ID")
16 - filter("CDR"."DATE_OF_CALL">=TO_DATE(' 2006-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "CDR"."DATE_OF_CALL"<=TO_DATE('
2006-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))