The GROUP BY operator is primarily used for grouping and aggregate computation 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. During execution plan generation, the SQL optimizer selects which GROUP BY operator to use based on its cost assessment of the two algorithms.
For regular 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 preceding example, the outputs & filters section of the execution plan for query Q1 shows the output information of the SCALAR GROUP BY operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter condition of this operator. In query Q1, no filter is set for the SCALAR GROUP BY operator, so the value is nil. |
| group | The columns used for grouping. For example, query Q1 uses the SCALAR GROUP BY operator, so the value is nil. |
| agg_func | The aggregate functions involved. For example, query Q1 calculates the sum of data in column c1 of table t1, so the value is T_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 preceding example, the outputs & filters section of the execution plan for query Q2 shows the output information of the HASH GROUP BY operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter condition of this operator. Because the requirement is that the sum of column c2 after grouping is greater than 2, the value is T_FUN_SUM(t1.c2) > 2. |
| group | The columns used for grouping. For example, query Q2 groups by column c1 of table t1, so the value is T1.C1. |
| agg_func | The aggregate functions involved. For example, query Q2 calculates the sum of column c2 of table t1, so the value is T_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 preceding example, the outputs & filters section of the execution plan for query Q3 shows the information of the MERGE GROUP BY operator. It can be seen that the same SQL statement generates an execution plan that chooses the MERGE GROUP BY operator. Their basic operator information is the same; the main difference lies in the grouping algorithm selected 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.