Oracle数据库中的GROUP BY操作是对查询结果进行分组的方式。在Oracle中，有两种主要的分组方法：Sort Group By 和 Hash Group By。它们之间的区别主要在于执行策略和性能。
- Sort Group By:
Sort Group By 是一种基于排序的分组方法。在此方法中，Oracle首先对输入数据进行排序，然后在排序后的数据上执行分组操作。这种方法需要额外的排序操作，因此在数据量较大时可能会导致性能下降。然而，对于小型数据集或已经排好序的数据集，Sort Group By 可能会更快。
- Hash Group By:
Hash Group By 是一种基于哈希表的分组方法。在此方法中，Oracle使用哈希函数将输入数据映射到哈希表中的不同存储桶中。这种方法通常在处理大型数据集时表现更好，因为它不需要额外的排序操作。然而，当内存不足以容纳整个哈希表时，Hash Group By 可能会导致性能下降，因为需要将部分哈希表溢出到磁盘。
通常情况下，Oracle优化器会自动选择最合适的分组方法。但是，如果需要，可以通过优化器提示或其他设置来强制使用特定的分组方法。
除了这两种分组方法外，还有其他分组方式，例如在分布式数据库环境中的"Parallel Group By"。Parallel Group By 可以将分组操作分布到多个并行服务器上，从而实现更高的处理速度。但这种方法通常用于特定的数据库配置和并行处理环境。
10G以前GROUP BY子句可以返回排序的结果集，即使没有ORDER BY子句。
原因是因为使用了“SORT GROUP BY”，会自动排序分组字段。
从10G开始以后引入了“HASH GROUP BY”，新的内部排序算法会导致GROUP BY 子句不保证输出会按分组的列排序，也不保证结果集的顺序。
要对分组进行排序，请使用 ORDER BY 子句。
如果未指定 ORDER BY 子句，则检索行的顺序取决于用于从数据库检索行的方法。换句话说，这取决于选择的执行计划。
下边看下简单的实验：
环境：19.13.0.0.0
创建表并插入实验数据，尽量保证同一会话插入数据保证数据看起来就是无序的，当然实际上也是：
create table zkm (id int,name varchar2(20));
insert into zkm values(1,'a');
insert into zkm values(2,'b');
insert into zkm values(3,'c');
insert into zkm values(9,'i');
insert into zkm values(5,'e');
insert into zkm values(4,'d');
insert into zkm values(8,'h');
insert into zkm values(7,'g');
insert into zkm values(6,'f');
commit;
目标SQL：select id,count(name) from zkm group by id;
参数设置：alter session set statistics_level=all;
使用Hint：NO_USE_HASH_AGGREGATION来禁用“HASH GROUP BY”，这样目标SQL执行后结果集总是按照ID列进行排序返回。
并且从执行计划看是“SORT GROUP BY”。
17:06:46 ZKM@dev-app73/pdb(9)> select /*+ NO_USE_HASH_AGGREGATION */ id,count(name) from zkm group by id;
ID COUNT(NAME)
---------- -----------
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
9 rows selected.
Elapsed: 00:00:00.01
17:06:47 ZKM@dev-app73/pdb(9)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID a7kukqrrrvrra, child number 1
-------------------------------------
select /*+ NO_USE_HASH_AGGREGATION */ id,count(name) from zkm group by
id
Plan hash value: 2238836816
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 6 | | | |
| 1 | SORT GROUP BY | | 1 | 9 | 9 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 2 | TABLE ACCESS FULL| ZKM | 1 | 9 | 9 |00:00:00.01 | 6 | | | |
----------------------------------------------------------------------------------------------------------------
15 rows selected.
Elapsed: 00:00:00.06
去掉Hint后，再次执行返回的结果集则是无序的。
并且从执行计划看是“HASH GROUP BY”。
17:09:33 ZKM@dev-app73/pdb(9)> select id,count(name) from zkm group by id;
ID COUNT(NAME)
---------- -----------
6 1
1 1
7 1
2 1
8 1
5 1
4 1
3 1
9 1
9 rows selected.
Elapsed: 00:00:00.01
17:09:34 ZKM@dev-app73/pdb(9)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID dqw15j89d8r1b, child number 2
-------------------------------------
select id,count(name) from zkm group by id
Plan hash value: 201225912
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 6 | | | |
| 1 | HASH GROUP BY | | 1 | 9 | 9 |00:00:00.01 | 6 | 1558K| 1558K| 1063K (0)|
| 2 | TABLE ACCESS FULL| ZKM | 1 | 9 | 9 |00:00:00.01 | 6 | | | |
----------------------------------------------------------------------------------------------------------------
14 rows selected.
Elapsed: 00:00:00.10
从排序内存使用大小看，“HASH GROUP BY”使用的内存为1063K，“SORT GROUP BY”为2048bytes。
也可以从v$sql_workarea.last_memory_used获取信息。
由于数据量比较小，构造大量数据后执行速度为：
17:26:32 ZKM@dev-app73/pdb(9)> select id,count(name) from zkm group by id;
ID COUNT(NAME)
---------- -----------
6 2097152
7 2097152
1 2097152
8 2097152
2 2097152
5 2097152
4 2097152
9 2097152
3 2097152
9 rows selected.
Elapsed: 00:00:01.65
17:26:34 ZKM@dev-app73/pdb(9)> select /*+ NO_USE_HASH_AGGREGATION */ id,count(name) from zkm group by id;
ID COUNT(NAME)
---------- -----------
1 2097152
2 2097152
3 2097152
4 2097152
5 2097152
6 2097152
7 2097152
8 2097152
9 2097152
9 rows selected.
Elapsed: 00:00:03.13
数据量比较大的情况下，“HASH GROUP BY”要更快，当然不能得出“HASH GROUP BY”就一定快的结论。
实际上是因为避免了排序操作所以“HASH GROUP BY”会比”SORT GROUP BY“更快。
无法使用”HASH GROUP BY“的两种情况
情况1：GROUP BY后有对字段进行ORDER BY。
比如：
17:35:32 ZKM@dev-app73/pdb(9)> select id,count(name) from zkm group by id order by id;
ID COUNT(NAME)
---------- -----------
1 2097152
2 2097152
3 2097152
4 2097152
5 2097152
6 2097152
7 2097152
8 2097152
9 2097152
9 rows selected.
Elapsed: 00:00:03.36
17:36:22 ZKM@dev-app73/pdb(9)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID cns02rbymv6b6, child number 0
-------------------------------------
select id,count(name) from zkm group by id order by id
Plan hash value: 2238836816
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:03.36 | 28731 | | | |
| 1 | SORT GROUP BY | | 1 | 9 | 9 |00:00:03.36 | 28731 | 2048 | 2048 | 2048 (0)|
| 2 | TABLE ACCESS FULL| ZKM | 1 | 9 | 18M|00:00:00.49 | 28731 | | | |
----------------------------------------------------------------------------------------------------------------
14 rows selected.
Elapsed: 00:00:00.06
解决方法：使用子查询先进行GROUP BY操作，然后再外层查询使用ORDER BY子句进行排序。同时使用/*+ no_merge */防止视图合并。
17:37:19 ZKM@dev-app73/pdb(9)> select * from (select /*+ no_merge */ id,count(name) from zkm group by id) order by id;
ID COUNT(NAME)
---------- -----------
1 2097152
2 2097152
3 2097152
4 2097152
5 2097152
6 2097152
7 2097152
8 2097152
9 2097152
9 rows selected.
Elapsed: 00:00:01.69
17:37:37 ZKM@dev-app73/pdb(9)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
SQL_ID bxh00bg36g809, child number 0
-------------------------------------
select * from (select /*+ no_merge */ id,count(name) from zkm group by
id) order by id
Plan hash value: 970191995
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:01.69 | 28731 | | | |
| 1 | SORT ORDER BY | | 1 | 9 | 9 |00:00:01.69 | 28731 | 2048 | 2048 | 2048 (0)|
| 2 | VIEW | | 1 | 9 | 9 |00:00:01.69 | 28731 | | | |
| 3 | HASH GROUP BY | | 1 | 9 | 9 |00:00:01.69 | 28731 | 1558K| 1558K| 1065K (0)|
| 4 | TABLE ACCESS FULL| ZKM | 1 | 9 | 18M|00:00:00.48 | 28731 | | | |
------------------------------------------------------------------------------------------------------------------
17 rows selected.
Elapsed: 00:00:00.06
明显改写后的SQL执行速度更快。
原因是虽然还是有排序动作但是排序的结果集更更更更小了，从A-Rows看是9行，而不改写之前是对全部的行排序。
情况2：在聚合函数中多次使用distinct处理不同字段。
如SQL：select id,count(distinct name),count(distinct id) from zkm group by id order by id;
09:01:40 ZKM@dev-app73/pdb(9)> select id,count(distinct name),count(distinct id) from zkm group by id order by id;
ID COUNT(DISTINCTNAME) COUNT(DISTINCTID)
---------- ------------------- -----------------
1 1 1
2 1 1
3 1 1
4 1 1
5 1 1
6 1 1
7 1 1
8 1 1
9 1 1
9 rows selected.
Elapsed: 00:00:14.67
09:01:56 ZKM@dev-app73/pdb(9)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 7ht3gbdz1z5ts, child number 0
-------------------------------------
select id,count(distinct name),count(distinct id) from zkm group by id
order by id
Plan hash value: 2238836816
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:14.66 | 28731 | | | |
| 1 | SORT GROUP BY | | 1 | 1 | 9 |00:00:14.66 | 28731 | 2048 | 2048 | 2048 (0)|
| 2 | TABLE ACCESS FULL| ZKM | 1 | 9 | 18M|00:00:01.45 | 28731 | | | |
----------------------------------------------------------------------------------------------------------------
15 rows selected.
Elapsed: 00:00:00.06
可以看出，多个聚合函数中均使用了distinct导致无法用"HASH GROUP BY"，因为两个distinct需要去重，从结果看，对同一结果集可以同时排序两个以上不同的字段后做去重然后count，却无法同时对同一结果集做HASH去重去避免排序。
去掉其中一个distinct的话就没问题，如：select id,count(distinct name),count(id) from zkm group by id order by id;
09:13:56 ZKM@dev-app73/pdb(9)> select id,count(distinct name),count(id) from zkm group by id order by id;
ID COUNT(DISTINCTNAME) COUNT(ID)
---------- ------------------- ----------
1 1 2097152
2 1 2097152
3 1 2097152
4 1 2097152
5 1 2097152
6 1 2097152
7 1 2097152
8 1 2097152
9 1 2097152
9 rows selected.
Elapsed: 00:00:02.08
09:14:02 ZKM@dev-app73/pdb(9)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9t4u0dtgn1q0q, child number 0
-------------------------------------
select id,count(distinct name),count(id) from zkm group by id order by
id
Plan hash value: 1511739550
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:02.08 | 28731 | | | |
| 1 | SORT GROUP BY | | 1 | 9 | 9 |00:00:02.08 | 28731 | 2048 | 2048 | 2048 (0)|
| 2 | VIEW | VW_DAG_0 | 1 | 9 | 9 |00:00:02.08 | 28731 | | | |
| 3 | HASH GROUP BY | | 1 | 9 | 9 |00:00:02.08 | 28731 | 1452K| 1452K| 1192K (0)|
| 4 | TABLE ACCESS FULL| ZKM | 1 | 9 | 18M|00:00:00.44 | 28731 | | | |
----------------------------------------------------------------------------------------------------------------------
17 rows selected.
Elapsed: 00:00:00.07
参考文档
GROUP BY Clause Does Not Guarantee a Sort Without ORDER BY Clause in 10g and Above (文档 ID 345048.1)
哈希表结构，以c语言为示例，说明下哈希表的创建和实现
哈希表是一种基于键值对（key-value pairs）存储数据的数据结构。哈希表使用哈希函数将键映射到数组的某个索引位置，从而实现快速查找、插入和删除操作。由于哈希函数的使用，哈希表的平均时间复杂度为O(1)。
PostgreSQL中哈希表的实现可以在源代码中找到。这里我们简要介绍一下哈希表的创建和实现Hash Group By的过程，然后通过C语言说明Hash Group By和Sort Group By的区别。
创建哈希表：
在PostgreSQL中，可以使用 hash_create() 函数创建一个新的哈希表。以下是一个简化的示例：
#include "utils/hsearch.h"
HTAB *create_hash_table() {
HASHCTL ctl;
HTAB *hash_table;
memset(&ctl, 0, sizeof(ctl));
ctl.keysize = sizeof(int);
ctl.entrysize = sizeof(int);
ctl.hcxt = CurrentMemoryContext;
hash_table = hash_create("Example Hash Table", 100, &ctl, HASH_ELEM | HASH_CONTEXT);
return hash_table;
}
这个示例创建了一个简单的哈希表，其键和值都是整数。hash_create() 函数需要一个表名，预期的表大小，一个指向 HASHCTL 结构的指针，以及一些哈希表选项。
实现Hash Group By：
以下是一个简化的实现Hash Group By的示例：
typedef struct {
int group_key;
int aggregated_value;
} Group;
void hash_group_by(int *input_data, int input_size, HTAB *hash_table) {
for (int i = 0; i < input_size; i++) {
int key = input_data[i];
bool found;
Group *group;
group = (Group *) hash_search(hash_table, (void *) &key, HASH_ENTER, &found);
if (!found) {
group->group_key = key;
group->aggregated_value = 0;
}
group->aggregated_value += 1; // 简单计数，可以替换为其他聚合函数
}
}
在这个示例中，我们遍历输入数据，使用 hash_search() 函数在哈希表中查找相应的键。如果找到键，我们更新聚合值；如果没有找到，我们创建一个新的组并将聚合值初始化为0。
hash group by和sort group by区别
现在，我们来比较Hash Group By和Sort Group By的区别：
Hash Group By:
基于哈希表实现。
对于大型数据集效率更高，因为不需要额外的排序操作。
当内存不足以容纳整个哈希表时，可能导致性能下降，因为需要将部分哈希表溢出到磁盘。
Sort Group By:
基于排序实现。
首先对输入数据进行排序，然后在排序后的数据上执行分组操作。
对于小型数据集或已经排好序的数据集
以下是一个简化的C语言实现Sort Group By的示例。此示例假设输入数据是一维整数数组，我们对这些整数进行分组并计算每组的数量。
首先，我们需要一个排序函数，这里我们使用快速排序（qsort）：
#include <stdlib.h>
int compare(const void *a, const void *b) {
return (*(int *)a - *(int *)b);
}
void sort_data(int *data, int data_size) {
qsort(data, data_size, sizeof(int), compare);
}
接下来，我们实现Sort Group By的功能：
#include <stdio.h>
typedef struct {
int group_key;
int aggregated_value;
} Group;
Group *sort_group_by(int *input_data, int input_size, int *output_size) {
sort_data(input_data, input_size);
Group *groups = malloc(input_size * sizeof(Group));
int group_count = 0;
for (int i = 0; i < input_size;) {
int group_key = input_data[i];
int aggregated_value = 0;
while (i < input_size && input_data[i] == group_key) {
aggregated_value++;
i++;
}
groups[group_count].group_key = group_key;
groups[group_count].aggregated_value = aggregated_value;
group_count++;
}
*output_size = group_count;
return groups;
}
在这个示例中，我们首先对输入数据进行排序，然后在排序后的数据上执行分组操作。我们迭代遍历排序后的数据，将具有相同键的元素分为一组，并计算每组的数量。
请注意，这个示例是一个简化的版本，仅用于说明Sort Group By的概念。实际上，数据库系统（如PostgreSQL）在处理Sort Group By时会使用更复杂的数据结构和算法，同时处理各种数据类型和聚合函数。
