Materialized queries

When Adaptive Server materializes subqueries, the query is executed in two steps:

The first step stores the results of the subquery in an internal variable or worktable.
The second step uses the internal variable or worktable results in the outer query.

This query materializes the subquery into a worktable:

select type, title_id
from titles
where total_sales in (select max(total_sales)
            from sales_summary
            group by type)

QUERY PLAN FOR STATEMENT 1 (at line 1).

    STEP 1
        The type of query is SELECT (into Worktable1).
        GROUP BY
        Evaluate Grouped MAXIMUM AGGREGATE.

        FROM TABLE
            sales_summary
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            Worktable1.

    STEP 2
        The type of query is SELECT.

        FROM TABLE
            titles
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Worktable1.
        EXISTS TABLE : nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 16 Kbytes for data pages.
        With MRU Buffer Replacement Strategy for data pages.

The showplan message “EXISTS TABLE: nested iteration,” near the end of the output, shows that Adaptive Server performs an existence join.