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

Parallelism
HP NonStop SQL/MX Query Guide—523728-003
8-7
Join With Matching Partitions
In the simple case shown in the next figure, the first partition of Table A is joined with
the first partition of Table B, the second partition of Table A is joined with the second
partition of Table B, and so on. This method of performing joins works only if, for a
given row in Table A, partition 1, all its matching rows are stored in Table B, partition 1.
In the case where both Table A and Table B are partitioned on the join attributes with
matching key ranges or compatible hashing functions, the optimizer might choose to
use this algorithm.
As mentioned previously, the join with matching partitions is called a Type1 join; the
case shown in the figure is a generalized Type1 join. Variations of the Type1 join
include those discussed next.
•
Join With Range Repartitioning
Range repartitioning allows matching partition joins between two tables where only
one table is partitioned on the join column. This type of plan can also be generated
if the partition first key boundary values do not match or if the number of partitions
of the two tables do not match or if one table is not clustered on the join columns.
In this type of plan, only one table is repartitioned.
SERIAL PLAN
VST082.vsd
Join
Tbl A
Prt 1
Tbl A
Prt 1
Tbl B
Prt 1
Tbl B
Prt 1
Tbl A
Prt 2
Tbl A
Prt 2
Tbl B
Prt 2
Tbl B
Prt 2
Join Join
Tbl A
Prt 3
Tbl A
Prt 3
Tbl B
Prt 3
Tbl B
Prt 3
Join
Combine
PARTITIONED PARALLEL PLAN