GROUP BY 演算子は、SQLにおいてグループ化と集計を行うために主に使用されます。
データをグループ化するアルゴリズムには HASH アルゴリズムと MERGE アルゴリズムがあり、これにより GROUP BY 演算子は HASH GROUP BY と MERGE GROUP BY の2種類に分けられます。実行計画の生成時には、SQLオプティマイザーがこれら2つの演算子のコストを評価し、どちらの GROUP BY 演算子を使用するかを選択します。
一般的な集約関数（SUM/MAX/MIN/AVG/COUNT/STDDEV）も GROUP BY 演算子を割り当てることで実現されます。一方、集約関数のみが含まれて GROUP BY 句がないSQLでは、SCALAR GROUP BY 演算子が割り当てられます。したがって、GROUP BY 演算子は SCALAR GROUP BY、HASH GROUP BY、MERGE GROUP BY の3種類に分類できます。
SCALAR GROUP BY
例1：SCALAR GROUP BY 演算子を含む実行計画
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)
上記の例では、Q1 クエリの実行計画表示における outputs & filters に、SCALAR GROUP BY 演算子の出力情報が詳細に記載されています。
情報名 |
意味 |
|---|---|
| output | この演算子が出力する式。 |
| filter | この演算子のフィルター条件。 例では SCALAR GROUP BY 演算子に filter が設定されていないため、nil となります。 |
| group | グループ化する列。 例えば、Q1クエリでは SCALAR GROUP BY 演算子があるため、nil となります。 |
| agg_func | 関与する集約関数。 例えば、Q1クエリはテーブル t1 の c1 列のデータの合計を計算するものであるため、T_FUN_SUM(t1.c1) となります。 |
HASH GROUP BY
例2：HASH GROUP BY 演算子を含む実行計画
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)
上記の例では、Q2クエリの実行計画表示における outputs & filters が HASH GROUP BY 演算子の出力情報を詳細に示しています。
情報名 |
意味 |
|---|---|
| output | この演算子が出力する式。 |
| filter | この演算子のフィルター条件。 グループ化後の c2 列の合計が2より大きいことを設定要件としているため、T_FUN_SUM(t1.c2) > 2 となります。 |
| group | グループ化を行う列。 例えば、Q2クエリは t1 テーブルの c1 列に対してグループ化を行うため、T1.C1 となります。 |
| agg_func | 関与する集約関数。 例えば、Q2クエリではテーブル t1 の c2 列の合計を計算するため、T_FUN_SUM(t1.c2) となります。 |
説明
HASH GROUP BY 演算子は、実行時に HASH アルゴリズムを用いてグループ化することを保証します。
MERGE GROUP BY
例3：MERGE GROUP BY 演算子を含む実行計画
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)
上記の例では、Q3 クエリの実行計画表示における outputs & filters に MERGE GROUP BY 演算子の情報が詳細に記載されています。同じ SQL が実行計画を生成する際に MERGE GROUP BY 演算子を選択したことがわかります。その演算子の基本情報はすべて同じであり、最大の違いは実行時に選択されるグループ化アルゴリズムが異なる点です。また、ここでの2番目の演算子 TABLE SCAN が返す結果は無秩序な結果であり、GROUP BY アルゴリズムでは MERGE GROUP BY を採用しているため、SORT 演算子を割り当てる必要があります。
注意
NO_USE_HASH_AGGREGATION および USE_HASH_AGGREGATION ヒントは、GROUP BY 演算子がどのアルゴリズムを使用してグループ化するかを制御するために使用できます。