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

Parallelism
HP NonStop SQL/MX Query Guide—523728-003
8-11
Unions
Unions
The optimizer can choose to execute the union in parallel when another operator, such
as a group by, exists above the MERGE_UNION, which can benefit from receiving
parallel streams of data. For parallelism of the MERGE_UNION operator, the optimizer
supports a matching partitions union (based on the matching partitions join algorithm).
Inputs of the union must be partitioned the same; otherwise, the optimizer must
repartition the tables.
A union can be executed in parallel in three ways:
•
If you do not need to order the data, the optimizer reads both tables in parallel.
•
If you require ordering, the optimizer might use sort or index operations on the
individual tables and then merge the streams in parallel.
•
In certain cases, the union is run in parallel by using ESPs that read different
partitions of each of the tables.
Group Bys
SQL/MX supports hash and sort group by operations in addition to scalar aggregate
operations (group by operation without a group by operator). The grouping can be
performed in DAM processes, ESPs, or the master executor.
A full group by can be executed in parallel if the data is partitioned on the group by
columns. A full scalar aggregate cannot be executed in parallel.
The partial group by operator can be executed in parallel without regard to the
partitioning scheme and might result in duplicate groups. Therefore, the results of a
VST890.vsd
Join
Join
Join
Combine
Outer Inner
Tbl A
Prt 2
Tbl B
Tbl A
Prt 3
SELECT * FROM A,B
WHERE A.COL1=B.COL2;
Tbl A
Prt 1
Join