研究了一下，group by是否排序是受隐含参数_gby_hash_aggregation_enabled控制的，如下测试：
从10g之后，group by默认采用了hash group by，不再排序了。
SQL> alter system set "_gby_hash_aggregation_enabled"=false;
System altered.
SQL> set autot traceonly
SQL> select object_type,max(object_name) from test group by object_type;
41 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2603667166
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 380K| 27M| 1262 (2)| 00:00:16 |
| 1 | SORT GROUP BY | | 380K| 27M| 1262 (2)| 00:00:16 |
| 2 | TABLE ACCESS FULL| TEST | 380K| 27M| 1250 (1)| 00:00:15 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
5683 consistent gets
0 physical reads
0 redo size
2019 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
41 rows processed
SQL> alter system set "_gby_hash_aggregation_enabled"=true;
System altered.
SQL> select object_type,max(object_name) from test group by object_type;
41 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3061K| 224M| 1379 (10)| 00:00:17 |
| 1 | HASH GROUP BY | | 3061K| 224M| 1379 (10)| 00:00:17 |
| 2 | TABLE ACCESS FULL| TEST | 3061K| 224M| 1273 (3)| 00:00:16 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5612 consistent gets
0 physical reads
0 redo size
1995 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
41 rows processed
从10g之后，group by默认采用了hash group by，不再排序了。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-742876/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
service的创建与使用
下一篇：
RAC环境下dataguard的搭建
请登录后发表评论
登录
全部评论
