union all operators are implemented using a physical operator by the same name. union all is a fairly simple operation and should be used in parallel only when the query is moving a lot of data.
The only condition to generating a parallel union all is that each of its operands must be of the same degree, irrespective of the type of partitioning they have. The following example (using table HA2) shows a union all operator being processed in parallel. The position of the exchange operator above the union all operator signifies that it is being processed by multiple threads:
create table HA2(a1 int, a2 int, a3 int) partition by hash(a1, a2) (p1, p2) select * from RA2 union all select * from HA2 QUERY PLAN FOR STATEMENT 1 (at line 1). Executed in parallel by coordinating process and 2 worker processes. The type of query is SELECT. ROOT:EMIT Operator |EXCHANGE Operator (Merged) |Executed in parallel by 2 Producer and 1 Consumer processes. | | |EXCHANGE:EMIT Operator | | | | |UNION ALL Operator has 2 children. | | | | | | |SCAN Operator | | | | FROM TABLE | | | | RA2 | | | | Table Scan. . . . . . . . . . . . . . . . . . . . | | | | Executed in parallel with a 2-way partition scan. . . . . . . . . . . . . . . . . . . . | | | | | | |SCAN Operator | | | | FROM TABLE | | | | HA2 | | | | Table Scan. . . . . . . . . . . . . . . . . . . . | | | | Executed in parallel with a 2-way partition scan.
In the next example, the data from each side of the union operator is restricted by selective predicates on either side. The amount of data being sent through the union all operator is small enough that Adaptive Server decides not to run the unions in parallel. Instead, each scan of the tables RA2 and HA2 are organized by putting 2-to-1 exchange operators on each side of the union. The resultant operands are then processed in parallel by the union all operator:
select * from RA2 where a2 > 2400 union all select * from HA2 where a3 in (10,20)
Executed in parallel by coordinating process and 4 worker processes. 7 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |UNION ALL Operator has 2 children. | | |EXCHANGE Operator (Merged) | |Executed in parallel by 2 Producer and 1 Consumer processes. | | | | |EXCHANGE:EMIT Operator | | | | | | |SCAN Operator | | | | FROM TABLE | | | | RA2 | | | | Table Scan. | | | | Executed in parallel with a 2-way partition scan. | | |EXCHANGE Operator (Merged) | |Executed in parallel by 2 Producer and 1 Consumer processes. | | | | |EXCHANGE:EMIT Operator | | | | | | |SCAN Operator | | | | FROM TABLE | | | | HA2 | | | | Table Scan. | | | | Executed in parallel with a 2-way partition scan.
