Some subqueries that contain vector (grouped) aggregates can be materialized. These are:
Noncorrelated quantified predicate subqueries
Correlated quantified predicate subqueries correlated only in the having clause
The materialization of the subquery results in these two steps:
Adaptive Server executes the subquery first and stores the results in a worktable.
Adaptive Server joins the outer table to the worktable as an existence join. In most cases, this join cannot be optimized because statistics for the worktable are not available.
Materialization saves the cost of evaluating the aggregates once for each row in the table. For example, this query:
select title_id
from titles
where total_sales in (select max(total_sales)
from titles
group by type)
Executes in these steps:
select maxsales = max(total_sales)
into #work
from titles
group by type
select title_id
from titles, #work
where total_sales = maxsales
The total cost of executing quantified predicate subqueries is the sum of the query costs for the two steps.
When there are where clauses in addition to a subquery, Adaptive Server executes the subquery or subqueries last to avoid unnecessary executions of the subqueries. Depending on the clauses in the query, it is often possible to avoid executing the subquery because less expensive clauses can determine whether the row is to be returned:
If any and clauses evaluate to FALSE, the row will not be returned.
If any or clauses evaluate to TRUE, the row will be returned.
In both cases, as soon as the status of the row is determined by the evaluation of one clause, no other clauses need to be applied to that row. This provides a performance improvement, because expensive subqueries need to be executed less often.
