資料庫的sort group by和hash group by
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時會使用更復雜的資料結構和演算法，同時處理各種資料型別和聚合函式。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-2944700/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle Hash Group ByOracle
- SORT (UNIQUE STOPKEY)/ SORT GROUP BY STOPKEYTopK
- 帶你瞭解資料庫中group by的用法資料庫
- mysql的order by和group byMySql
- laravel8資料庫操作之group by踩坑Laravel資料庫
- oracle partition by group by,詳解partition by和group by對比Oracle
- MySQL資料庫對GROUP BY子句的功能擴充套件(2)MySql資料庫套件
- MySQL資料庫對GROUP BY子句的功能擴充套件(1)MySql資料庫套件
- HybridDBPostgreSQL”Sort、Group、distinct聚合、JOIN”不懼怕資料傾斜的黑科技和原理-多階段聚合SQL
- ERP的Account group和CRM partner group的對映關係
- DISTINCT和GROUP BY的區別
- 10.2版本中hash group by一些爭議
- Exceeded memory limit for $group, but didn't allow external sort. Pass allowDiskMIT
- MySQL/InnoDB和Group CommitMySqlMIT
- oracle中Window和Window GroupOracle
- 11G的SORT GROUP BY NOSORT導致錯誤執行計劃
- Oracle GroupOracle
- oracle中distinct和group by的區別Oracle
- SQL -去重Group by 和Distinct的效率SQL
- group_replication_bootstrap_group 用於什麼boot
- 【Hive】資料傾斜優化 shuffle, join, group byHive優化
- 淺談 Checkbox Group 的雙向資料繫結
- 自關聯和動態group by
- sql中的group by 和 having 用法解析SQL
- MySQL Group ReplicationMySql
- Group by 優化優化
- sql用法——group bySQL
- Oracle group by使用Oracle
- GCD 中Group的使用GC
- SQL中Group By的使用SQL
- MySQL的Group By分組MySql
- MASM中Group的作用ASM
- MySQL 中的 distinct 和 group by 的效能比較MySql
- ClickHouse 實時資料去重final+group by
- ORA-00937: not a single-group group functionFunction
- Sql group by 分組取時間最新的一條資料SQL
- mysql order by 和 group by 順序問題MySql
- Oracle和MySQL分組查詢GROUP BYOracleMySql