SUBQUERY: nested iteration

This SHOWPLAN step indicates a nested iteration on a correlated subquery that involves an aggregate. The plan for the subquery will immediately follow the SUBQUERY 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 worktable. The GROUP BY clause for the vector aggregate consists of the outer references in the subquery and the elements of the GROUP BY clause for the subquery, if such a clause exists.

The following example demonstrates the SHOWPLAN output for subqueries that involve 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
            FROM TABLE
            Worktable 1
            SUBQUERY : nested iteration
            GROUP BY
            Vector Aggregate
              FROM TABLE
              sales s2
              Nested iteration
              Table Scan
              TO TABLE
              Worktable 1