GROUP BY操作是数据库中非常常见的语法，通常用于聚合函数的聚合操作。对于oracle最早的时候对于group by还是使用的Sort Group Aggregate，之后引入了一种对于大数据量group by较为高效的算法Hash Group Aggregate。
该特性由参数“_gby_hash_aggregation_enabled”控制，也可以使用hint USE_HASH_AGGREGATION/NO_USE_HASH_AGGREGATION来控制。目前绝大部分的group by算法几乎都是Hash Group Aggregate。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> explain plan for select object_type,sum(object_id),avg(object_id),count(*) from test.t1 group by object_type; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 136660032 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 47 | 705 | 432 (1)| 00:00:01 | | 1 | HASH GROUP BY | | 47 | 705 | 432 (1)| 00:00:01 | | 2 | TABLE ACCESS FULL| T1 | 71319 | 1044K| 430 (1)| 00:00:01 | --------------------------------------------------------------------------- |
当然也有Sort Group Aggregate的使用场景，就是当语句出现group by xxx order by xxx的时候
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> explain plan for select object_type,sum(object_id),avg(object_id),count(*) from test.t1 group by object_type order by object_type; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3946799371 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 47 | 705 | 432 (1)| 00:00:01 | | 1 | SORT GROUP BY | | 47 | 705 | 432 (1)| 00:00:01 | | 2 | TABLE ACCESS FULL| T1 | 71319 | 1044K| 430 (1)| 00:00:01 | --------------------------------------------------------------------------- 9 rows selected |
当然使用hint也可以让group by +order by走Hash Group Aggregate+Sort Order By
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> explain plan for select /*+USE_HASH_AGGREGATION*/ object_type,sum(object_id),avg(object_id),count(*) from test.t1 group by object_type order by object_type; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2808104874 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 47 | 705 | 432 (1)| 00:00:01 | | 1 | SORT ORDER BY | | 47 | 705 | 432 (1)| 00:00:01 | | 2 | HASH GROUP BY | | 47 | 705 | 432 (1)| 00:00:01 | | 3 | TABLE ACCESS FULL| T1 | 71319 | 1044K| 430 (1)| 00:00:01 | ---------------------------------------------------------------------------- 10 rows selected. |
介绍了一下SORT/HASH group by，下面是这篇文章的主题，当聚合操作里出现distinct时，如count(distinct xxx)时，其他数据库是不能使用Hash Group Aggregate的。以海量的vastbase为例：
|
1 2 3 4 5 6 7 8 9 |
vastbase=# explain select owner,avg(data_object_id), count(distinct object_name) from t1 group by owner; QUERY PLAN -------------------------------------------------------------------- GroupAggregate (cost=811.06..902.40 rows=2 width=93) Group By Key: owner -> Sort (cost=811.06..833.89 rows=9132 width=53) Sort Key: owner -> Seq Scan on t1 (cost=0.00..210.32 rows=9132 width=53) (5 rows) |
而oracle 从11gr2版本开始，CBO应对这种场景会对sql进行转换，生成一个DAG inline view，并且会多做一次Hash Group Aggregate。这个查询转换叫做Distinct Aggregate Transformation，由参数“_optimizer_distinct_agg_transform”控制，也可以使用hint TRANSFORM_DISTINCT_AGG/NO_TRANSFORM_DISTINCT_AGG 在sql级别控制。
先来看看oracle的执行计划：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> explain plan for select object_type,sum(object_id),avg(object_id),count(distinct object_name) from test.t1 group by object_type; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3244420040 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 47 | 4794 | | 1378 (1)| 00:00:01 | | 1 | HASH GROUP BY | | 47 | 4794 | | 1378 (1)| 00:00:01 | | 2 | VIEW | VW_DAG_0 | 71319 | 7104K| | 1378 (1)| 00:00:01 | | 3 | HASH GROUP BY | | 71319 | 3760K| 4504K| 1378 (1)| 00:00:01 | | 4 | TABLE ACCESS FULL| T1 | 71319 | 3760K| | 430 (1)| 00:00:01 | ----------------------------------------------------------------------------------------- 11 rows selected. |
可以看到中间生成了一个DAG VIEW，并且执行计划出现了两次HASH GROUP BY，并未出现SORT GROUP BY。
从10053 可以发现oracle会做一个DAGG_TRANSFORM转换，将sql进行改写
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DAGG_TRANSFORM: transforming query block SEL$1 (#0) 转换前： qbcp (before transform): qb SEL$1 (#0):******* UNPARSED QUERY IS ******* SELECT "T1"."OBJECT_TYPE" "OBJECT_TYPE",SUM("T1"."OBJECT_ID") "SUM(OBJECT_ID)",AVG("T1"."OBJECT_ID") "AVG(OBJECT_ID)",COUNT(DISTINCT "T1"."OBJECT_NAME") "COUNT(DISTI NCTOBJECT_NAME)" FROM "TEST"."T1" "T1" GROUP BY "T1"."OBJECT_TYPE" pgactx->ctxqbc (before transform): qb SEL$1 (#0):******* UNPARSED QUERY IS ******* SELECT "T1"."OBJECT_TYPE" "OBJECT_TYPE",SUM("T1"."OBJECT_ID") "SUM(OBJECT_ID)",AVG("T1"."OBJECT_ID") "AVG(OBJECT_ID)",COUNT(DISTINCT "T1"."OBJECT_NAME") "COUNT(DISTI NCTOBJECT_NAME)" FROM "TEST"."T1" "T1" GROUP BY "T1"."OBJECT_TYPE" Registered qb: SEL$5771D262 0x907d63d8 (SPLIT QUERY BLOCK FOR DISTINCT AGG OPTIM SEL$1; SEL$1) 转换后： qbcp (after transform): qb SEL$C33C846D (#0):******* UNPARSED QUERY IS ******* SELECT "VW_DAG_0"."ITEM_2" "OBJECT_TYPE",SUM("VW_DAG_0"."ITEM_3") "SUM(OBJECT_ID)",DECODE(NVL(SUM("VW_DAG_0"."ITEM_5"),0),0,TO_NUMBER(NULL),SUM("VW_DAG_0"."ITEM_3")/ NVL(SUM("VW_DAG_0"."ITEM_5"),0)) "AVG(OBJECT_ID)",COUNT("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTOBJECT_NAME)" FROM (SELECT "T1"."OBJECT_NAME" "ITEM_1","T1"."OBJECT_TYPE " "ITEM_2",SUM("T1"."OBJECT_ID") "ITEM_3",SUM("T1"."OBJECT_ID") "ITEM_4",COUNT("T1"."OBJECT_ID") "ITEM_5" FROM "TEST"."T1" "T1" GROUP BY "T1"."OBJECT_NAME","T1"."OBJ ECT_TYPE") "VW_DAG_0" GROUP BY "VW_DAG_0"."ITEM_2" pgactx->ctxqbc (after transform): qb SEL$C33C846D (#0):******* UNPARSED QUERY IS ******* SELECT "VW_DAG_0"."ITEM_2" "OBJECT_TYPE",SUM("VW_DAG_0"."ITEM_3") "SUM(OBJECT_ID)",DECODE(NVL(SUM("VW_DAG_0"."ITEM_5"),0),0,TO_NUMBER(NULL),SUM("VW_DAG_0"."ITEM_3")/ NVL(SUM("VW_DAG_0"."ITEM_5"),0)) "AVG(OBJECT_ID)",COUNT("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTOBJECT_NAME)" FROM (SELECT "T1"."OBJECT_NAME" "ITEM_1","T1"."OBJECT_TYPE " "ITEM_2",SUM("T1"."OBJECT_ID") "ITEM_3",SUM("T1"."OBJECT_ID") "ITEM_4",COUNT("T1"."OBJECT_ID") "ITEM_5" FROM "TEST"."T1" "T1" GROUP BY "T1"."OBJECT_NAME","T1"."OBJ ECT_TYPE") "VW_DAG_0" GROUP BY "VW_DAG_0"."ITEM_2" |
格式化一下转换后的sql
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT "VW_DAG_0"."ITEM_2" "OBJECT_TYPE", SUM("VW_DAG_0"."ITEM_3") "SUM(OBJECT_ID)", DECODE(NVL(SUM("VW_DAG_0"."ITEM_5"), 0), 0, TO_NUMBER(NULL), SUM("VW_DAG_0"."ITEM_3") / NVL(SUM("VW_DAG_0"."ITEM_5"), 0)) "AVG(OBJECT_ID)", COUNT("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTOBJECT_NAME)" FROM (SELECT "T1"."OBJECT_NAME" "ITEM_1" ,"T1"."OBJECT_TYPE " "ITEM_2" ,SUM("T1"."OBJECT_ID") "ITEM_3" ,SUM("T1"."OBJECT_ID") "ITEM_4" ,COUNT("T1"."OBJECT_ID") "ITEM_5" FROM "TEST"."T1" "T1" GROUP BY "T1"."OBJECT_NAME","T1"."OBJECT_TYPE") "VW_DAG_0" GROUP BY "VW_DAG_0"."ITEM_2" |
其原理就是先构造一个inline view对distinct的字段也做group by 形成一个DAG view，再对DAG view生成等价的改写。巧妙的避开了distinct造成的不能HASH GROUP BY的场景。非常值得国产数据库学习，应该目前很少有国产数据库会支持该特性。