Evaluate Grouped ASSIGNMENT OPERATOR
Queries using compute by display the same aggregate messages as group by, with the “Evaluate Grouped ASSIGNMENT OPERATOR” message.
The values are placed in a worktable in one step, and the computation of the aggregates is performed in a second step. This query uses type and advance, like the group by query example above:
select type, advance from titles having title like "Compu%" order by type compute avg(advance) by type
In the showplan output, the computation of the aggregates takes place in 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 Grouped SUM OR AVERAGE AGGREGATE.
Evaluate Grouped COUNT AGGREGATE.
Evaluate Grouped 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.
