Evaluate Ungrouped ASSIGNMENT OPERATOR
When a query includes compute to compile a scalar aggregate, showplan prints the “Evaluate Ungrouped ASSIGNMENT OPERATOR” message. This query computes an average for the entire result set:
select type, advance from titles where title like "Compu%" order by type compute avg(advance)
The showplan output shows that the computation of the aggregate values takes place in the step 2:
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable1 created for ORDER BY.
FROM TABLE
titles
Nested iteration.
Index : title_ix
Forward scan.
Positioning by key.
Keys are:
title ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
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.
Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.
Evaluate Ungrouped COUNT AGGREGATE.
Evaluate Ungrouped ASSIGNMENT OPERATOR.
This step involves sorting.
FROM TABLE
Worktable1.
Using GETSORTED
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.
