GroupSortedOp (Aggregation) operator

The GroupSortedOp (Aggregation) operator is a simple variant of the GroupSorted (Distinct) operator described in “GroupSorted (Distinct) operator”. The GroupSortedOp (Aggregation) operator requires that the input set is sorted on the group by columns. The algorithm is very similar. A row is read from the child operator. If the row is the start of a new vector, then its grouping columns are cached and the aggregation results are initialized. If the row belongs to the current group being processed, the aggregate functions are applied to the aggregate results. When the child operator returns a row that starts a new group or End Of Scan, the current vector and its aggregated values are returned to the parent operator.

This is a nonblocking operator similar to the GroupSorted (Distinct) operator with one difference. The first row in theGroupSortedOp (Aggregation) operator is returned after an entire group is processed, where the first row in the GroupSorted (Distinct) operator is returned at the start of a new group. This example collects a list of all cities with the number of authors that live in each city.

1> select city, total_authors = count(*)
2>     from authors
3>     group by city
4> plan
5> "(group_sorted
6>      (sort (scan authors))
7> )"
8> go

QUERY PLAN FOR STATEMENT 1 (at line 3).
Optimized using the Abstract Plan in the PLAN clause.

3 operator(s) under root


The type of query is SELECT.

ROOT:EMIT Operator

	|GROUP SORTED Operator
	|  Evaluate Grouped COUNT AGGREGATE.
	|
	|   |SORT Operator
	|   | Using Worktable1 for internal storage.
	|   |
	|   |   |SCAN Operator
	|   |   |  FROM TABLE
	|   |   |  authors
	|   |   |  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.

In this query plan, the scan of authors does not return rows in grouping order. A sort operator is applied to order the stream based on the grouping column city. At this point, a GroupSortedOp (Aggregation) operator can be applied to evaluate the count() aggregate.

The GroupSortedOp (Aggregation) operator showplan output reports the aggregate functions being applied as:

 |  Evaluate Grouped COUNT AGGREGATE.