SUBQUERY : nested iteration

This SHOWPLAN step indicates a nested iteration on a correlated subquery involving an aggregate. The plan for the subquery will immediately follow this step, and is indented to isolate the steps for the subquery from the steps for the outer query. The plan for the subquery will produce a vector aggregate result in a work table. The GROUP BY clause for the vector aggregate consists of the outer references in the subquery plus the elements of the subquery's GROUP BY clause, if one exists.

The following example demonstrates the SHOWPLAN output for subqueries involving aggregates:

Query:

SELECT ord_num, title_id, qty
FROM sales s1
WHERE qty <
(SELECT avg(qty)
FROM sales s2
WHERE s2.title_id = s1.title_id)

SHOWPLAN:

STEP 1
The type of query is SELECT
FROM TABLE
sales s1
Nested iteration
Table Scan
AND EXISTS : nested iteration
FROM TABLE
Worktable 1
SUBQUERY : nested iteration
GROUP BY
Vector Aggregate
FROM TABLE
sales s2
Nested iteration
Table Scan
TO TABLE
Worktable 1