The GroupInsertingOp is a blocking operator. All rows from the child operator must be processed before the first row can be returned from the GroupInsertingOp.
The GroupInsertingOp used in earlier versions of Adaptive Server for generating grouped tables. It is limited to 31 or fewer columns in the group by clause. The operator starts by creating a work table with a clustered index of the grouping columns. As each row is fetched from the child, a lookup into the work table is done based on the grouping columns. If no row is found, then the row is inserted. This effectively creates a new group and initializes its aggregate values. If a row is found, then the new aggregate values are updated based on evaluating the new values. The GroupInsertingOp has the side effect of returning rows ordered by the grouping columns.
1> select city, total_authors = count(*)
2> from authors
3> group by city
4> plan
5> “(group_inserting (i_scan auidind authors ))”
6> go
QUERY PLAN FOR STATEMENT 1 (at line 1). Optimized using the Abstract Plan in the PLAN clause. STEP 1 The type of query is SELECT. 2 operator(s) under root
|ROOT:EMIT Operator | | |GROUP INSERTING Operator | | GROUP BY | | Evaluate Grouped COUNT AGGREGATE | | 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 example, the group inserting operator starts by building a worktable with a clustered index keyed on the city column. The group inserting operator proceeds to drain the authors table. For each row, a lookup is done on the city value. If there is no row in the aggregation worktable with the current city value, then the row is inserted. This creates a new group for the current city value with an initialized count value. If the row for the current city value is found, then an evaluation is done to increment the count aggregate value.
