All Products
Search
Document Center

GROUP BY

Last Updated: Jun 18, 2021

The GROUP BY operator enables data grouping and aggregating in SQL statements.

Algorithms for data grouping include HASH and MERGE . So, the GROUP BY operator also includes HASH GROUP BY and MERGE GROUP BY. For a newly generated execution plan, the SQL optimizer selects from these two GROUP BY operators based on cost evaluation.

General aggregate functions (SUM, MAX, MIN, AVG, COUNT, STDDEV) are also executed by assigning the GROUP BY operator. However, if an SQL contains only aggregate functions but no GROUP BY, the SCALAR GROUP BY operator is assigned. In that sense, GROUP BY operators can be divided into three types: SCALAR GROUP BY, HASH GROUP BY, and MERGE GROUP BY.

SCALAR GROUP BY

Example 1: An execution plan containing a SCALAR GROUP BY operator.

obclient>CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(1, 1);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(2, 2);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(3, 3);
Query OK, 1 rows affected (0.12 sec)

Q1:
obclient> EXPLAIN SELECT SUM(c1) FROM t1\G;
*************************** 1. row ***************************
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 in the execution plan display of query Q1 shows in detail the output information of the SCALAR GROUP BY operator.

Field

Description

output

The output expression of the operator.

filter

The filter conditions of the operator.

In this example, the condition is set to nil because no filter is configured for the SCALAR GROUP BY operator.

group

Indicates the columns to be grouped.

For example, as query Q1 has a SCALAR GROUP BY operator, the value of group is nil.

agg_func

Indicates the aggregate functions involved.

For example, query Q1 is to sum up the data in column c1 of table t1, therefore the value is T_FUN_SUM(t1.c1).

HASH GROUP BY

Example 2: An execution plan containing a HASH GROUP BY operator

Q2: 
obclient>EXPLAIN SELECT SUM(c2) FROM t1 GROUP BY c1 HAVING SUM(c2) > 2\G;
*************************** 1. row ***************************
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 in the execution plan display of query Q2 shows in detail the output information of the HASH GROUP BY operator.

Field

Description

output

The output expression of the operator.

filter

The filter conditions of the operator.

Because the setting requires that the sum of column c2 after grouping is greater than 2, the filter is T_FUN_SUM(t1.c2) > 2.

group

Indicates the columns to be grouped.

For example, as query Q2 has a HASH GROUP BY operator, the value of group is nil.

agg_func

Indicates the aggregate functions involved.

For example, query Q2 is to sum up the data in column c1 of table t1, therefore the value is T_FUN_SUM(t1.c1).

Note

The HASH GROUP BY operator ensures that the data is grouped by HASH algorithm in the execution.

MERGE GROUP BY

Example 3: An 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\G;
*************************** 1. row ***************************
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 in the execution plan display of query Q3 shows in detail the information of the MERGE GROUP BY operator. It shows that the MERGE GROUP BY operator was selected for the execution plan that was generated by the same SQL statement, and the basic information of the operator was the same, except for the algorithm selected for grouping in the execution. Additionally, TABLE SCAN, the No. 2 operator in the demo, returned an unsorted result, while the GROUP BY algorithm used the MERGE GROUP BY operator, so a SORT operator must be assigned.

Notice

NO_USE_HASH_AGGREGATION and USE_HASH_AGGREGATION hints can control the type of algorithm selected by the GROUP BY operator for grouping.