The GROUP BY operator is primarily used for grouping and aggregate calculation operations in SQL.
There are two algorithms for grouping data: the HASH algorithm and the MERGE algorithm. Therefore, based on the algorithm, the GROUP BY operator can be divided into two types: HASH GROUP BY and MERGE GROUP BY. When generating an execution plan, the SQL optimizer selects which GROUP BY operator to use based on its cost assessment of the two algorithms.
For ordinary aggregate functions (SUM, MAX, MIN, AVG, COUNT, STDDEV), the GROUP BY operator is also assigned to complete the grouping. For SQL statements that contain only aggregate functions and no GROUP BY, the SCALAR GROUP BY operator is assigned. Thus, the GROUP BY operator can be further divided into three types: SCALAR GROUP BY, HASH GROUP BY, and MERGE GROUP BY.
SCALAR GROUP BY
Example 1: Execution plan containing a SCALAR GROUP BY operator
obclient> CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES(1, 1);
Query OK, 1 rows affected
obclient> INSERT INTO t1 VALUES(2, 2);
Query OK, 1 rows affected
obclient> INSERT INTO t1 VALUES(3, 3);
Query OK, 1 rows affected
Q1:
obclient> EXPLAIN SELECT SUM(c1) FROM t1;
Query Plan:
| ========================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
----------------------------------------
|0 |SCALAR GROUP BY| |1 |37 |
|1 | TABLE SCAN |T1 |3 |37 |
========================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_SUM(T1.C1)]), filter(nil),
group(nil), agg_func([T_FUN_SUM(T1.C1)])
1 - output([T1.C1]), filter(nil),
access([T1.C1]), partitions(p0)
In the example above, the output information of the SCALAR GROUP BY operator is listed in detail under outputs & filters in the execution plan display for query Q1:
Information Name |
Meaning |
|---|---|
| output | The expression output by this operator. |
| filter | The filter condition for this operator. In the example,SCALAR GROUP BYThe operator is not set.filter, so it isnil. |
| group | The column used for grouping. For example, in the Q1 query, it isSCALAR GROUP BYoperator, so it isnil. |
| agg_func | The aggregate functions involved. For example, the Q1 query calculates the table'st1of thec1The sum of column data is , so it isT_FUN_SUM(t1.c1). |
HASH GROUP BY
Example 2: Execution plan containing a HASH GROUP BY operator
Q2:
obclient> EXPLAIN SELECT SUM(c2) FROM t1 GROUP BY c1 HAVING SUM(c2) > 2;
Query Plan:
| ======================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
--------------------------------------
|0 |HASH GROUP BY| |1 |40 |
|1 | TABLE SCAN |T1 |3 |37 |
======================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_SUM(T1.C2)]), filter([T_FUN_SUM(T1.C2) > 2]),
group([T1.C1]), agg_func([T_FUN_SUM(T1.C2)])
1 - output([T1.C1], [T1.C2]), filter(nil),
access([T1.C1], [T1.C2]), partitions(p0)
In the example above, the output information of the HASH GROUP BY operator is listed in detail under outputs & filters in the execution plan display for query Q2:
Information Name |
Meaning |
|---|---|
| output | The expression output by this operator. |
| filter | The filter condition for this operator. Due to the requirement that after grouping,c2The column sum is greater than 2, so it isT_FUN_SUM(t1.c2) > 2. |
| group | The column used for grouping. For example, the Q2 query is performed on thet1Table'sc1The columns are grouped by , so it is .T1.C1. |
| agg_func | The aggregate functions involved. For example, in the Q2 query, the table is calculated.t1of thec2The sum of the columns is thusT_FUN_SUM(t1.c2). |
Note
The HASH GROUP BY operator ensures that the HASH algorithm is used for grouping during execution.
MERGE GROUP BY
Example 3: Execution plan containing a MERGE GROUP BY operator
Q3:
obclient> EXPLAIN SELECT /*+NO_USE_HASH_AGGREGATION*/SUM(c2) FROM t1 GROUP BY c1 HAVING SUM(c2) > 2;
Query Plan:
| =======================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
---------------------------------------
|0 |MERGE GROUP BY| |1 |45 |
|1 | SORT | |3 |44 |
|2 | TABLE SCAN |T1 |3 |37 |
=======================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_SUM(T1.C2)]), filter([T_FUN_SUM(T1.C2) > 2]),
group([T1.C1]), agg_func([T_FUN_SUM(T1.C2)])
1 - output([T1.C1], [T1.C2]), filter(nil), sort_keys([T1.C1, ASC])
2 - output([T1.C1], [T1.C2]), filter(nil),
access([T1.C1], [T1.C2]), partitions(p0)
In the example above, the information about the MERGE GROUP BY operator is listed in detail under outputs & filters in the execution plan display for query Q3. It can be seen that the same SQL statement generates an execution plan that uses the MERGE GROUP BY operator. The basic information of the operator is the same; the main difference lies in the grouping algorithm chosen during execution. Additionally, the result returned by operator 2 here, TABLE SCAN, is unordered. Since the GROUP BY algorithm uses MERGE GROUP BY, a SORT operator must be assigned.
Notice
The NO_USE_HASH_AGGREGATION and USE_HASH_AGGREGATION hints can be used to control which algorithm the GROUP BY operator uses for grouping.