When eager aggregation is enabled, the optimizer determines cost, depending on whether the estimated cheapest plan uses eager aggregation or not.
Output from the showplan aggregation:
1> select r1, sum(s1) 2> from r, s 3> where r2=s2 4> group by r1 5> go
QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. 6 operator(s) under root |ROOT:EMIT Operator | | |HASH VECTOR AGGREGATE Operator | | GROUP BY | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Using Worktable2 for internal storage. | | Key Count: 1 | | | | |MERGE JOIN Operator (Join Type: Inner Join) | | | Using Worktable1 for internal storage. | | | Key Count: 1 | | | Key Ordering: ASC | | | | | | |GROUP SORTED Operator | | | | Evaluate Grouped COUNT AGGREGATE. | | | | | | | | |SCAN Operator | | | | | FROM TABLE | | | | | r | | | | | Index : ir21 | | | | | Forward Scan. | | | | | Positioning at index start. | | | | | Index contains all needed columns. Base table will not be read. | | | | | Using I/O Size 2 Kbytes for index leaf pages. | | | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | | | | |GROUP SORTED Operator | | | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | | | | | | | |SCAN Operator | | | | | FROM TABLE | | | | | s | | | | | Index : is21 | | | | | Forward Scan. | | | | | Positioning at index start. | | | | | Index contains all needed columns. Base table will not be read. | | | | | Using I/O Size 2 Kbytes for index leaf pages. | | | | | With LRU Buffer Replacement Strategy for index leaf pages. r1 ----------- ----------- 1 2 2 4 (2 rows affected)
As the query performs vector aggregation over the join of r and s, the hash vector aggregate operator at the top of the query tree is expected in all cases. However, the group sorted operators over the scans of r and of s are not part of the query; they perform the eager aggregation.
When advanced_aggregation is off, the plan does not contain the eager aggregation operators group sorted:
1> set advanced_aggregation off 2> go 1> select r1, sum(s1) 2> from r, s 3> where r2=s2 4> group by r1 5> go
QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is SELECT. 4 operator(s) under root |ROOT:EMIT Operator | | |HASH VECTOR AGGREGATE Operator | | GROUP BY | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Using Worktable2 for internal storage. | | Key Count: 1 | | | | |MERGE JOIN Operator (Join Type: Inner Join) | | | Using Worktable1 for internal storage. | | | Key Count: 1 | | | Key Ordering: ASC | | | | | | |SCAN Operator | | | | FROM TABLE | | | | r | | | | Index : ir21 | | | | Forward Scan. | | | | Positioning at index start. | | | | Index contains all needed columns. Base table will not be read. | | | | Using I/O Size 2 Kbytes for index leaf pages. | | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | | | | |SCAN Operator | | | | FROM TABLE | | | | s | | | | Index : is21 | | | | Forward Scan. | | | | Positioning at index start. | | | | Index contains all needed columns. Base table will not be read. | | | | Using I/O Size 2 Kbytes for index leaf pages. | | | | With LRU Buffer Replacement Strategy for index leaf pages. r1 ----------- ----------- 1 2 2 4 (2 rows affected)
