When a query contains a subquery, Adaptive Server uses different access methods to reduce the cost of processing the subquery. Parallel optimization depends on the type of subquery and the access methods:
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 join. It is not considered for existence joins or other flattening strategies.
Nested subqueries – parallel access methods are considered for the outermost query block in a query containing a subquery; the inner, nested queries always execute serially. Although the optimizer considers parallel access methods for only the outermost query block in a subquery, all worker processes that access the outer query block also access the inner tables of the nested subqueries.
Each worker process accesses the inner, nested query block in serial. Although the subquery is run once for each row in the outer table, each worker process performs only one-fifth of the executions. showplan output for the subquery indicates that the nested query is “Executed by 5 worker processes,” since each worker process used in the outer query block scans the table specified in the inner query block.
Each worker process maintains a separate cache of subquery results, so the subquery may be executed slightly more often than in serial processing.
