当前位置：首页 > news >正文

学习笔记：Query Transformation- Distinct Aggregate Transformation

news 2025/8/29 2:44:38

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！由于博客中有大量代码，通过页面浏览效果更佳。

本文转自朋友的真实案例分享。

学习笔记：Query Transformation- Distinct Aggregate Transformation

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。

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的时候

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

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为例：

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的执行计划：

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进行改写

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

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的场景。非常值得国产数据库学习，应该目前很少有国产数据库会支持该特性。

------------------作者介绍-----------------------

姓名：黄廷忠现就职：Oracle中国高级服务团队曾就职：OceanBase、云和恩墨、东方龙马等电话、微信、QQ：18081072613

个人博客: (http://www.htz.pw)

CSDN地址: (https://blog.csdn.net/wwwhtzpw)

博客园地址: (https://www.cnblogs.com/www-htz-pw)