Adaptive Server uses different methods to reduce the cost of processing subqueries. Parallel optimization depends on the type of subquery:
Materialized subqueries – parallel query methods are not considered for the materialization step.
Flattened subqueries – parallel query optimization is considered only when the subquery is flattened to a regular inner join or a semijoin.
Nested subqueries – parallel operations are considered for the outermost query block in a query containing a subquery; the inner, nested queries always execute serially. This means that all tables in nested subqueries are accessed serially. In the following example, the table RA2 is accessed in parallel, but the result is that the table is serialized using a two-to-one exchange operator before accessing the subquery. The table RB2 inside the subquery is accessed in parallel.
select count(*) from RA2 where not exists (select * from RB2 where RA2.a1 = b1) QUERY PLAN FOR STATEMENT 1 (at line 1). Executed in parallel by coordinating process and 2 worker processes. 8 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |SCALAR AGGREGATE Operator | Evaluate Ungrouped COUNT AGGREGATE. | | |SQFILTER Operator has 2 children. | | | | |EXCHANGE Operator (Merged) | | |Executed in parallel by 2 Producer and 1 Consumer processes. | | | | | | |EXCHANGE:EMIT Operator | | | | | | | | |RESTRICT Operator | | | | | | | | | | |SCAN Operator | | | | | | FROM TABLE | | | | | | RA2 | | | | | | Index : RA2_NC2L | | | | | | Forward Scan. | | | | | | Executed in parallel with a 2-way partition scan. | | | | Run subquery 1 (at nesting level 1). | |
| | QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 2). | | | | Correlated Subquery. | | Subquery under an EXISTS predicate. | | | | |SCALAR AGGREGATE Operator | | | Evaluate Ungrouped ANY AGGREGATE. | | | Scanning only up to the first qualifying row. | | | | | | |SCAN Operator | | | | FROM TABLE | | | | RB2 | | | | Table Scan. | | | | Forward Scan. | | | | END OF QUERY PLAN FOR SUBQUERY 1.
The following example shows an in subquery flattened into a semijoin. Adaptive Server converts this into an inner join to provide greater flexibility in shuffling the tables in the join order. As seen below, the table RB2, which was originally in the subquery, is now being accessed in parallel.
select * from RA2 where a1 in (select b1 from RB2) QUERY PLAN FOR STATEMENT 1 (at line 1). Executed in parallel by coordinating process and 5 worker processes. 10 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |EXCHANGE Operator (Merged) |Executed in parallel by 3 Producer and 1 Consumer processes. | | |EXCHANGE:EMIT Operator | | | | |MERGE JOIN Operator (Join Type: Inner Join) | | | Using Worktable3 for internal storage. | | | Key Count: 1 | | | Key Ordering: ASC | | | | | | |SORT Operator | | | | Using Worktable1 for internal storage. | | | | | | | | |SCAN Operator | | | | | FROM TABLE | | | | | RB2 | | | | | Table Scan. | | | | | Executed in parallel with a 3-way partition scan. | | | | | | |SORT Operator | | | | Using Worktable2 for internal storage. | | | | | | | | |EXCHANGE Operator (Merged) | | | | |Executed in parallel by 2 Producer and 3 Consumer processes. | | | | | | | | | | |EXCHANGE:EMIT Operator | | | | | | | | | | | | |RESTRICT Operator | | | | | | | | | | | | | | |SCAN Operator | | | | | | | | FROM TABLE | | | | | | | | RA2 | | | | | | | | Index : RA2_NC2L | | | | | | | | Forward Scan. | | | | | | | | Positioning at index start. | | | | | | | | Executed in parallel with a 2-way partition scan.
