Full Partition-Wise Joins: Composite - Single-Level

This method is a variation of the single-level - single-level method. In this scenario, one table (typically the larger table) is composite partitioned on two dimensions, using the join columns as the subpartition key. In the example, the sales table is a typical example of a table storing historical data. Using range partitioning is a logical initial partitioning method for a table storing historical information.

For example, assume you want to partition the sales table into eight partitions by range on the column time_id. Also assume you have two years and that each partition represents a quarter. Instead of using range partitioning, you can use composite partitioning to enable a full partition-wise join while preserving the partitioning on time_id. For example, partition the sales table by range on time_id and then subpartition each partition by hash on cust_id using 16 subpartitions for each partition, for a total of 128 subpartitions. The customers table can use hash partitioning with 16 partitions.

When you use the method just described, a full partition-wise join works similarly to the one created by a single-level - single-level hash-hash method. The join is still divided into 16 smaller joins between hash partition pairs from both tables. The difference is that now each hash partition in the sales table is composed of a set of 8 subpartitions, one from each range partition.

Figure 3-2 illustrates how the hash partitions are formed in the sales table. Each cell represents a subpartition. Each row corresponds to one range partition, for a total of 8 range partitions. Each range partition has 16 subpartitions. Each column corresponds to one hash partition for a total of 16 hash partitions; each hash partition has 8 subpartitions. Hash partitions can be defined only if all partitions have the same number of subpartitions, in this case, 16.

Hash partitions are implicit in a composite table. However, Oracle does not record them in the data dictionary, and you cannot manipulate them with DDL commands as you can range or list partitions.

Figure 3-2 Range and Hash Partitions of a Composite Table

Description of "Figure 3-2 Range and Hash Partitions of a Composite Table"

The following example shows the execution plan for the full partition-wise join with the sales table 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)                | :TQ10001  |       |       | P->S | QC (RAND)  |
|*  3 |    FILTER                            |           |       |       | PCWC |            |
|   4 |     HASH GROUP BY                    |           |       |       | PCWP |            |
|   5 |      PX RECEIVE                      |           |       |       | PCWP |            |
|   6 |       PX SEND HASH                   | :TQ10000  |       |       | P->P | HASH       |
|   7 |        HASH GROUP BY                 |           |       |       | PCWP |            |
|   8 |         PX PARTITION HASH ALL        |           |     1 |    16 | PCWC |            |
|*  9 |          HASH JOIN                   |           |       |       | PCWP |            |
|  10 |           TABLE ACCESS FULL          | CUSTOMERS |     1 |    16 | PCWP |            |
|  11 |           PX PARTITION RANGE ITERATOR|           |     8 |     9 | PCWC |            |
|* 12 |            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)
   9 - access("S"."CUST_ID"="C"."CUST_ID")
  12 - 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'))

Note:

The Rows, Cost (%CPU), Time, and TQ columns were removed from the plan table output in this example.

Composite - single-level partitioning is effective because it enables you to combine pruning on one dimension with a full partition-wise join on another dimension. In the previous example query, pruning is achieved by scanning only the subpartitions corresponding to Q3 of 1999, in other words, row number 3 in Figure 3-2. Oracle then joins these subpartitions with the customer table, using a full partition-wise join.

All characteristics of the single-level - single-level partition-wise join apply to the composite - single-level partition-wise join. In particular, for this example, these two points are common to both methods:

The degree of parallelism for this full partition-wise join cannot exceed 16. Even though the sales table has 128 subpartitions, it has only 16 hash partitions.
A partition is now a collection of subpartitions. For example, in Figure 3-2, store hash partition 9 of the sales table shown by the eight circled subpartitions, on the same node as hash partition 9 of the customers table.