[20250526]SORT GROUP BY与HASH GROUP BY.txt

来源：这里教程网时间：2026-03-03 22:00:05 作者：

[20250526]SORT GROUP BY与HASH GROUP BY.txt --//我记忆里大约在10g开始oracle的group by采用hash group by算法，其结果就是其结果集不再按照group by字段排序，也提醒一些 --//开发人员必须显示控制排序方式，可以通过提示USE_HASH_AGGREGATION/NO_USE_HASH_AGGREGATION来控制采用的算法。做一个例子说 --//明一些细节，有时候采用hash group by算法并不是最佳的情况： 1.环境： SCOTT@book01p> @ ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 21.0.0.0.0 BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. SCOTT@book01p> @ sqlhintz "sort_agg|hash_agg" NAME SQL_FEATURE CLASS INVERSE TARGET_LEVEL PROPERTY VERSION VERSION_OUTLINE CON_ID ----------------------- ----------- -------------------- ----------------------- ------------ -------- -------------- --------------- ------ USE_HASH_AGGREGATION QKSFM_ALL USE_HASH_AGGREGATION NO_USE_HASH_AGGREGATION 2 0 10.2.0.1 10.2.0.5 0 NO_USE_HASH_AGGREGATION QKSFM_ALL USE_HASH_AGGREGATION USE_HASH_AGGREGATION 2 0 10.2.0.1 10.2.0.5 0 2.测试： SCOTT@book01p> create table t1 as select * from all_objects; Table created. --//分析表略。 SCOTT@book01p> @ sl all alter session set statistics_level = all; Session altered. SCOTT@book01p> Select object_type,sum(object_id),avg(object_id),count(*) from t1 group by object_type; OBJECT_TYPE SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(*) ------------------------------ -------------- -------------- ---------- INDEX 47963848 20110.6281 2385 TABLE 46586395 19435.292 2397 CLUSTER 3127 312.7 10 EDITION 134 134 1 SYNONYM 451440006 37346.1289 12088 SEQUENCE 5405583 51481.7429 105 DIRECTORY 198343 18031.1818 11 PACKAGE 30372636 37636.4758 807 VIEW 114811847 15025.7619 7641 FUNCTION 23268633 57171.0885 407 PROCEDURE 3958952 43988.3556 90 TYPE 77026974 34775.1576 2215 OPERATOR 3005473 50091.2167 60 TABLE PARTITION 23209585 40224.5841 577 INDEX PARTITION 29357424 79775.6087 368 TYPE BODY 7464132 66054.2655 113 TABLE SUBPARTITION 512080 16002.5 32 PACKAGE BODY 20327004 57583.5807 353 LIBRARY 3731825 52560.9155 71 CONSUMER GROUP 378585 21032.5 18 JOB CLASS 63169 21056.3333 3 DESTINATION 42133 21066.5 2 SCHEDULE 85012 21253 4 WINDOW 190681 21186.7778 9 SCHEDULER GROUP 84768 21192 4 EVALUATION CONTEXT 67978 22659.3333 3 TRIGGER 7458043 51791.9653 144 RULE SET 92298 23074.5 4 XML SCHEMA 1753783 44968.7949 39 INDEXTYPE 654696 59517.8182 11 JAVA CLASS 1701232733 44526.729 38207 JAVA RESOURCE 100771276 58965.0532 1709 JAVA DATA 1168076 68710.3529 17 JAVA SOURCE 264798 88266 3 34 rows selected. SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 43vzz3p38fqdy, child number 0 ------------------------------------- Select object_type,sum(object_id),avg(object_id),count(*) from t1 group by object_type Plan hash value: 136660032 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 415 (100)| | 34 |00:00:00.01 | 1479 | | 1 | HASH GROUP BY | | 1 | 34 | 510 | 415 (2)| 00:00:01 | 34 |00:00:00.01 | 1479 | | 2 | TABLE ACCESS FULL| T1 | 1 | 69908 | 1024K| 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 | --------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / "T1"@"SEL$1" 21 rows selected. --//缺省采用HASH GROUP BY，注意看输出结果并没有按照OBJECT_TYPE排序。 --//给sql语句加入order by 1;后： SCOTT@book01p> Select object_type,sum(object_id),avg(object_id),count(*) from t1 group by object_type order by 1; OBJECT_TYPE SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(*) ------------------------------ -------------- -------------- ---------- CLUSTER 3127 312.7 10 CONSUMER GROUP 378585 21032.5 18 DESTINATION 42133 21066.5 2 DIRECTORY 198343 18031.1818 11 EDITION 134 134 1 EVALUATION CONTEXT 67978 22659.3333 3 FUNCTION 23268633 57171.0885 407 INDEX 47963848 20110.6281 2385 INDEX PARTITION 29357424 79775.6087 368 INDEXTYPE 654696 59517.8182 11 JAVA CLASS 1701232733 44526.729 38207 JAVA DATA 1168076 68710.3529 17 JAVA RESOURCE 100771276 58965.0532 1709 JAVA SOURCE 264798 88266 3 JOB CLASS 63169 21056.3333 3 LIBRARY 3731825 52560.9155 71 OPERATOR 3005473 50091.2167 60 PACKAGE 30372636 37636.4758 807 PACKAGE BODY 20327004 57583.5807 353 PROCEDURE 3958952 43988.3556 90 RULE SET 92298 23074.5 4 SCHEDULE 85012 21253 4 SCHEDULER GROUP 84768 21192 4 SEQUENCE 5405583 51481.7429 105 SYNONYM 451440006 37346.1289 12088 TABLE 46586395 19435.292 2397 TABLE PARTITION 23209585 40224.5841 577 TABLE SUBPARTITION 512080 16002.5 32 TRIGGER 7458043 51791.9653 144 TYPE 77026974 34775.1576 2215 TYPE BODY 7464132 66054.2655 113 VIEW 114811847 15025.7619 7641 WINDOW 190681 21186.7778 9 XML SCHEMA 1753783 44968.7949 39 34 rows selected. SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID db1k70a7favpv, child number 0 ------------------------------------- Select object_type,sum(object_id),avg(object_id),count(*) from t1 group by object_type order by 1 Plan hash value: 3946799371 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 415 (100)| | 34 |00:00:00.02 | 1479 | | | | | 1 | SORT GROUP BY | | 1 | 34 | 510 | 415 (2)| 00:00:01 | 34 |00:00:00.02 | 1479 | 4096 | 4096 | 4096 (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 69908 | 1024K| 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / "T1"@"SEL$1" 21 rows selected. --//执行语句加入order by后，采用SORT GROUP BY算法，注意看输出结果按照OBJECT_TYPE字段排序。 3.尝试加入提示： --//给有order by 字段的语句加入USE_HASH_AGGREGATION提示 SCOTT@book01p> Select /*+ USE_HASH_AGGREGATION */ object_type,sum(object_id),avg(object_id),count(*) from t1 group by object_type order by 1; --//输出略。 SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID a6k9ajfah8vr0, child number 0 ------------------------------------- Select /*+ USE_HASH_AGGREGATION */ object_type,sum(object_id),avg(object _id),count(*) from t1 group by object_type order by 1 Plan hash value: 2808104874 ------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 415 (100)| | 34 |00:00:00.01 | 1479 | | | | | 1 | SORT ORDER BY | | 1 | 34 | 510 | 415 (2)| 00:00:01 | 34 |00:00:00.01 | 1479 | 4096 | 4096 | 4096 (0)| | 2 | HASH GROUP BY | | 1 | 34 | 510 | 415 (2)| 00:00:01 | 34 |00:00:00.01 | 1479 | 1264K| 1264K| | | 3 | TABLE ACCESS FULL| T1 | 1 | 69908 | 1024K| 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / "T1"@"SEL$1" Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 --------------------------------------------------------------------------- 1 - SEL$1 - USE_HASH_AGGREGATION --//先使用HASH GROUP BY，再使用SORT ORDER BY输出。 --//给没有order by 字段的语句加入NO_USE_HASH_AGGREGATION提示 SCOTT@book01p> Select /*+ NO_USE_HASH_AGGREGATION */ object_type,sum(object_id),avg(object_id),count(*) from t1 group by object_type ; OBJECT_TYPE SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(*) ------------------------------ -------------- -------------- ---------- CLUSTER 3127 312.7 10 CONSUMER GROUP 378585 21032.5 18 DESTINATION 42133 21066.5 2 DIRECTORY 198343 18031.1818 11 .... WINDOW 190681 21186.7778 9 XML SCHEMA 1753783 44968.7949 39 34 rows selected. --//可以发现按照OBJECT_TYPE字段排序输出。 SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 654wq05g36wzj, child number 0 ------------------------------------- Select /*+ NO_USE_HASH_AGGREGATION */ object_type,sum(object_id),avg(object_id),count(*) from t1 group by object_type Plan hash value: 3946799371 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 415 (100)| | 34 |00:00:00.02 | 1479 | | | | | 1 | SORT GROUP BY | | 1 | 34 | 510 | 415 (2)| 00:00:01 | 34 |00:00:00.02 | 1479 | 4096 | 4096 | 4096 (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 69908 | 1024K| 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / "T1"@"SEL$1" Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 --------------------------------------------------------------------------- 1 - SEL$1 - NO_USE_HASH_AGGREGATION --//采用SORT GROUP BY 算法。 4.继续问题展开： --//如果将count(*)换成count(distinct object_name)，变成分组后统计有多少个不同object_name的情况。 SCOTT@book01p> Select object_type,sum(object_id),avg(object_id),count(distinct object_name) from t1 group by object_type; OBJECT_TYPE SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(DISTINCTOBJECT_NAME) ------------------------------ -------------- -------------- -------------------------- INDEX 47963848 20110.6281 2381 TABLE 46586395 19435.292 2391 SYNONYM 451440006 37346.1289 12077 CLUSTER 3127 312.7 10 SEQUENCE 5405583 51481.7429 105 VIEW 114811847 15025.7619 7638 PACKAGE 30372636 37636.4758 807 TYPE 77026974 34775.1576 2067 INDEX PARTITION 29357424 79775.6087 225 TABLE PARTITION 23209585 40224.5841 228 PROCEDURE 3958952 43988.3556 90 FUNCTION 23268633 57171.0885 407 CONSUMER GROUP 378585 21032.5 18 SCHEDULE 85012 21253 4 WINDOW 190681 21186.7778 9 OPERATOR 3005473 50091.2167 60 LIBRARY 3731825 52560.9155 71 PACKAGE BODY 20327004 57583.5807 353 XML SCHEMA 1753783 44968.7949 39 TRIGGER 7458043 51791.9653 144 RULE SET 92298 23074.5 4 JAVA CLASS 1701232733 44526.729 38207 JAVA RESOURCE 100771276 58965.0532 1708 INDEXTYPE 654696 59517.8182 11 TYPE BODY 7464132 66054.2655 113 JAVA DATA 1168076 68710.3529 17 EDITION 134 134 1 DIRECTORY 198343 18031.1818 11 JOB CLASS 63169 21056.3333 3 SCHEDULER GROUP 84768 21192 4 DESTINATION 42133 21066.5 2 TABLE SUBPARTITION 512080 16002.5 1 JAVA SOURCE 264798 88266 3 EVALUATION CONTEXT 67978 22659.3333 3 34 rows selected. --//如果对比Select object_type,sum(object_id),avg(object_id),count(*) from t1 group by object_type;的输出可以发现输出顺 --//序不同，看最后一条记录前面的是JAVA SOURCE. --//看看执行计划： SCOTT@book01p> @ dpc '' 'projection' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 0n5vt0xa0qwk0, child number 1 ------------------------------------- Select object_type,sum(object_id),avg(object_id),count(distinct object_name) from t1 group by object_type Plan hash value: 3244420040 -------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1296 (100)| | 34 |00:00:00.04 | 1479 | | | | | 1 | HASH GROUP BY | | 1 | 34 | 3468 | | 1296 (1)| 00:00:01 | 34 |00:00:00.04 | 1479 | 837K| 837K| | | 2 | VIEW | VW_DAG_0 | 1 | 69212 | 6894K| | 1296 (1)| 00:00:01 | 69212 |00:00:00.04 | 1479 | | | | | 3 | HASH GROUP BY | | 1 | 69212 | 3447K| 4416K| 1296 (1)| 00:00:01 | 69212 |00:00:00.04 | 1479 | 15M| 2089K| 9M (0)| | 4 | TABLE ACCESS FULL| T1 | 1 | 69908 | 3481K| | 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$C33C846D 2 - SEL$5771D262 / "VW_DAG_0"@"SEL$C33C846D" 3 - SEL$5771D262 4 - SEL$5771D262 / "T1"@"SEL$1" Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1; rowset=256) "ITEM_2"[VARCHAR2,23], COUNT("ITEM_1")[22], SUM("ITEM_3")[22], SUM("ITEM_5")[22] 2 - (rowset=256) "ITEM_1"[VARCHAR2,128], "ITEM_2"[VARCHAR2,23], "ITEM_3"[NUMBER,22], "ITEM_5"[NUMBER,22] 3 - (#keys=2; rowset=256) "OBJECT_TYPE"[VARCHAR2,23], "OBJECT_NAME"[VARCHAR2,128], COUNT(*)[22], SUM("OBJECT_ID")[22] 4 - (rowset=256) "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22], "OBJECT_TYPE"[VARCHAR2,23] Note ----- - statistics feedback used for this statement 37 rows selected. --//oracle依旧采用HASH GROUP BY算法，不过使用2次。 --//仔细看Column Projection Information (identified by operation id):就可以看出一些细节。 --//id=3 的输出字段信息是"OBJECT_TYPE"[VARCHAR2,23], "OBJECT_NAME"[VARCHAR2,128], COUNT(*)[22], SUM("OBJECT_ID")[22]。 --//也就是可以猜测这部分的group by实际上包含2个字段OBJECT_TYPE，OBJECT_NAME。 --//然后在这个的基础上做了1次group by OBJECT_TYPE。也就是做了查询转换。 SCOTT@book01p> @ expand_sql_text.sql 0n5vt0xa0qwk0 SELECT "A1"."OBJECT_TYPE" "OBJECT_TYPE", SUM("A1"."OBJECT_ID") "SUM(OBJECT_ID)", AVG("A1"."OBJECT_ID") "AVG(OBJECT_ID)", COUNT(DISTINCT "A1"."OBJECT_NAME") "COUNT(DISTINCTOBJECT_NAME)" FROM "SCOTT"."T1" "A1" GROUP BY "A1"."OBJECT_TYPE" PL/SQL procedure successfully completed. --//注做了格式化处理，oracle的expand sql text看不出细节，搞不懂有时候从输出可以看出执行细节有时候不行!! --//再做一个10053看看。 SCOTT@book01p> @ 10053x 0n5vt0xa0qwk0 1 /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3410_a0n5vt0xa0qwk0.trc SCOTT@book01p> @ 10053y '' TRCLINE ------------------------------------------------------------------------------------------------------------------------ Final query after transformations:******* 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(*) "ITEM_5" FROM "SCOTT"."T1" "T1" GROUP BY "T1"."OBJECT_TYPE","T1"."OB JECT_NAME") "VW_DAG_0" GROUP BY "VW_DAG_0"."ITEM_2" --//格式化后读起来更新清晰，oracle做了查询转换，可以很容易的发现内层的group by "T1"."OBJECT_TYPE", "T1"."OBJECT_NAME"。 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 (*) "ITEM_5" FROM "SCOTT"."T1" "T1" GROUP BY "T1"."OBJECT_TYPE", "T1"."OBJECT_NAME") "VW_DAG_0" GROUP BY "VW_DAG_0"."ITEM_2" --//如果给这样的语句加入NO_USE_HASH_AGGREGATION提示呢? SCOTT@book01p> Select /*+ NO_USE_HASH_AGGREGATION */ object_type,sum(object_id),avg(object_id),count(distinct object_name) from t1 group by object_type; OBJECT_TYPE SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(DISTINCTOBJECT_NAME) ------------------------------ -------------- -------------- -------------------------- CLUSTER 3127 312.7 10 CONSUMER GROUP 378585 21032.5 18 DESTINATION 42133 21066.5 2 DIRECTORY 198343 18031.1818 11 EDITION 134 134 1 EVALUATION CONTEXT 67978 22659.3333 3 FUNCTION 23268633 57171.0885 407 INDEX 47963848 20110.6281 2381 INDEX PARTITION 29357424 79775.6087 225 INDEXTYPE 654696 59517.8182 11 JAVA CLASS 1701232733 44526.729 38207 JAVA DATA 1168076 68710.3529 17 JAVA RESOURCE 100771276 58965.0532 1708 JAVA SOURCE 264798 88266 3 JOB CLASS 63169 21056.3333 3 LIBRARY 3731825 52560.9155 71 OPERATOR 3005473 50091.2167 60 PACKAGE 30372636 37636.4758 807 PACKAGE BODY 20327004 57583.5807 353 PROCEDURE 3958952 43988.3556 90 RULE SET 92298 23074.5 4 SCHEDULE 85012 21253 4 SCHEDULER GROUP 84768 21192 4 SEQUENCE 5405583 51481.7429 105 SYNONYM 451440006 37346.1289 12077 TABLE 46586395 19435.292 2391 TABLE PARTITION 23209585 40224.5841 228 TABLE SUBPARTITION 512080 16002.5 1 TRIGGER 7458043 51791.9653 144 TYPE 77026974 34775.1576 2067 TYPE BODY 7464132 66054.2655 113 VIEW 114811847 15025.7619 7638 WINDOW 190681 21186.7778 9 XML SCHEMA 1753783 44968.7949 39 34 rows selected. --//看输出就知道采用sort group by，按照OBJECT_TYPE排序。 SCOTT@book01p> @ dpc '' 'projection' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID f3u6zcjbfx37z, child number 0 ------------------------------------- Select /*+ NO_USE_HASH_AGGREGATION */ object_type,sum(object_id),avg(object_id),count(distinct object_name) from t1 group by object_type Plan hash value: 3946799371 ------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 415 (100)| | 34 |00:00:00.12 | 1479 | | | | | 1 | SORT GROUP BY | | 1 | 34 | 1734 | 415 (2)| 00:00:01 | 34 |00:00:00.12 | 1479 | 7140K| 1416K| 6346K (0)| | 2 | TABLE ACCESS FULL| T1 | 1 | 69908 | 3481K| 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / "T1"@"SEL$1" Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1; rowset=59) "OBJECT_TYPE"[VARCHAR2,23], COUNT(DISTINCT NLSSORT("OBJECT_NAME",'nls_sort=''BINARY'''))[22], COUNT("OBJECT_ID")[22], SUM("OBJECT_ID")[22] 2 - (rowset=59) "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22], "OBJECT_TYPE"[VARCHAR2,23] Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 --------------------------------------------------------------------------- 1 - SEL$1 - NO_USE_HASH_AGGREGATION --//直接就是1个SORT GROUP BY就可以，没有做查询转换。 --//不过你可以看cost就可以发现oracle有时候在采用最终的执行计划有点问题，采用SORT GROUP BY的cost成本才415，而2次hash group --//by的cost成本1296。 oracle最终还是选择hash group by的执行方式，或许oracle默认采用hash group by算法的缘故。 --//或者认为采用hash group by算法最优的缘故。 --//你可以执行如下sql语句，可以发现输出69212行，与前面hash group by 的E-Rows估算一致。 Select object_type,object_name,sum(object_id),avg(object_id) from t1 group by object_type,object_name; Select object_type,object_name,sum(object_id),avg(object_id) from t1 group by object_type,object_name order by 1,2; --//换一句话讲在这样的方式上采用SORT GROUP BY实际上执行效率也许更好，以后应该注意出现2次hash group by的情况。 --//oracle在做2个字段的hash group by时没有注意成本就很高。 --//顺便测试加入order by的情况。 SCOTT@book01p> Select object_type,sum(object_id),avg(object_id),count(distinct object_name) from t1 group by object_type order by 1; OBJECT_TYPE SUM(OBJECT_ID) AVG(OBJECT_ID) COUNT(DISTINCTOBJECT_NAME) ------------------------------ -------------- -------------- -------------------------- CLUSTER 3127 312.7 10 CONSUMER GROUP 378585 21032.5 18 DESTINATION 42133 21066.5 2 ... VIEW 114811847 15025.7619 7638 WINDOW 190681 21186.7778 9 XML SCHEMA 1753783 44968.7949 39 34 rows selected. SCOTT@book01p> @ dpc '' 'projection' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 0rmrjurdhxrmy, child number 1 ------------------------------------- Select object_type,sum(object_id),avg(object_id),count(distinct object_name) from t1 group by object_type order by 1 Plan hash value: 2434018851 -------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | | 1296 (100)| | 34 |00:00:00.05 | 1479 | | | | | 1 | SORT GROUP BY | | 1 | 34 | 3468 | | 1296 (1)| 00:00:01 | 34 |00:00:00.05 | 1479 | 4096 | 4096 | 4096 (0)| | 2 | VIEW | VW_DAG_0 | 1 | 69212 | 6894K| | 1296 (1)| 00:00:01 | 69212 |00:00:00.04 | 1479 | | | | | 3 | HASH GROUP BY | | 1 | 69212 | 3447K| 4416K| 1296 (1)| 00:00:01 | 69212 |00:00:00.04 | 1479 | 15M| 2089K| 9756K (0)| | 4 | TABLE ACCESS FULL| T1 | 1 | 69908 | 3481K| | 412 (1)| 00:00:01 | 69908 |00:00:00.01 | 1479 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$C33C846D 2 - SEL$5771D262 / "VW_DAG_0"@"SEL$C33C846D" 3 - SEL$5771D262 4 - SEL$5771D262 / "T1"@"SEL$1" Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1; rowset=256) "ITEM_2"[VARCHAR2,23], COUNT("ITEM_1")[22], SUM("ITEM_3")[22], SUM("ITEM_5")[22] 2 - (rowset=256) "ITEM_1"[VARCHAR2,128], "ITEM_2"[VARCHAR2,23], "ITEM_3"[NUMBER,22], "ITEM_5"[NUMBER,22] 3 - (#keys=2; rowset=256) "OBJECT_TYPE"[VARCHAR2,23], "OBJECT_NAME"[VARCHAR2,128], COUNT(*)[22], SUM("OBJECT_ID")[22] 4 - (rowset=256) "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22], "OBJECT_TYPE"[VARCHAR2,23] Note ----- - statistics feedback used for this statement 37 rows selected. --//依旧在内层采用HASH GROUP BY 算法。 5.附上执行脚本代码： $ cat expand_sql_text.sql set long 20000 set serveroutput on declare L_sqltext clob := null; l_version varchar2(3) := null; l_sql clob := null; l_result clob := null; begin select regexp_replace(version,'\..*') into l_version from v$instance; select sql_fulltext into l_sqltext from v$sqlarea where sql_id='&&1'; if l_version = '11' then l_sql := 'begin dbms_sql2.expand_sql_text( :a,:b ); end;'; elsif l_version >= '12' then l_sql := 'begin dbms_utility.expand_sql_text(:a,:b); end;'; end if; execute immediate l_sql using in l_sqltext,out l_result; dbms_output.put_line(l_result); end; / set serveroutput off $ cat 10053x.sql set term off execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1'); set term on set head off @ t set head on define 1=&trc $ cat 10053y.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. ------------------------------------------------------------------------------------------------------------ -- -- File name: 10053y.sql -- Purpose: display Final query after transformations -- -- Author: lfree -- -- Usage: -- @ 10053y <trc_file> -- ------------------------------------------------------------------------------------------------------------- SET TERM OFF COLUMN trc_file NEW_VALUE v_trc_file --DEFINE trc_file = &1 SELECT NVL('&1','&TRC') trc_file FROM DUAL ; SELECT SUBSTR ('&v_trc_file', INSTR ('&v_trc_file', '/', -1) + 1) trc_file FROM DUAL; SET TERM ON DEFINE trc_file = &v_trc_file --DEFINE trc_file = &1 --COL trace_filename FOR A45 --COL adr_home FOR A45 --SELECT trace_filename, to_char(change_time, 'dd-mm-yyyy hh24:mi:ss') AS change_time, to_char(modify_time, 'dd-mm-yyyy hh24:mi:ss') AS modify_time, adr_home, con_id --FROM gv$diag_trace_file --WHERE lower(trace_filename) LIKE lower('%&v_trc_file%') --ORDER BY modify_time; column trcline format a120 SELECT trcline FROM gv$diag_trace_file_contents MATCH_RECOGNIZE ( PARTITION BY trace_filename ORDER BY line_number MEASURES payload AS trcline ALL ROWS PER MATCH PATTERN (a | b nc * | c | f n) DEFINE a AS (payload LIKE 'qksptfSQM_GetTxt(): Anonymous Block%') ,b AS (payload LIKE 'qksptfSQM_GetTxt(): Macro Text%') ,nc AS (payload NOT LIKE 'qksptfSQM_Template(): Template Text%') ,c AS (payload LIKE 'qksptfSQM_Template(): Template Text%') ,f AS (payload LIKE 'Final query after%') ) x WHERE trace_filename = '&v_trc_file'; $ cat t.sql SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File';

编辑推荐：

返回列表

[20250526]SORT GROUP BY与HASH GROUP BY.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推