SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Parallelism
HP NonStop SQL/MX Query Guide—523728-003
8-9
Join With Matching Partitions
•
Join with logical subpartition alignment
Logical subpartitioning is typically used when the only difference between the join
tables is the partition boundaries or the number of partitions. Hash partitioned
tables cannot use logical subpartitioning.
The optimizer can choose this feature if both tables are partitioned on the join
columns but the first key values or the number of partitions of the second table do
not match the first table and the second table is clustered on the join columns. The
partitioning key and the clustering key columns of the second table must be the
same.
For logical subpartitioning, the optimizer behaves as if the other table is partitioned
the same way as the first table (“logically” partitions). Consequently, each instance
of the join operator (ESP) could be accessing more than one partition or some
fraction of a partition (subpartition) of the second table.
The next figure shows an example of logical subpartitioning. The first table has
three partitions. The second table has four partitions. However, because the
partitioning key and clustering key columns match the first table, the optimizer can
adjust the partitioning key boundary values of the second table into three logical
partitions that match the first table.
Tbl A
Prt 1
0 - 100
Tbl A
Prt 2
101 -
Left Child
Tbl B
Prt 1
Tbl B
Prt 2
Tbl B
Prt 3
Tbl B
Prt 4
0 - 50 51 - 100 101 - 150
Right Child
0 - 100 101 -
VST083.vsd
TB L1
TB L2
151 -
Tbl A
Prt 1
0 - 100
Tbl A
Prt 2
101 - 200
Tbl A
Prt 3
Left Child
Tbl B
Prt 1
Tbl B
Prt 2
Tbl B
Prt 3
Tbl B
Prt 4
0 - 75 76 - 150 151 - 225 226 -
TB L1
TB L2
TB L3
Right Child
0 - 100
101 - 200 201 -
VST084.vsd
201 -