在10gR2中，group by由以前的sort group by改成了hash group by，这种算法上的改进，取消了sort group by必须进行的排序操作。官方文档上说hash group by的性能强于sort group by，但经过我的测试，不管是逻辑读和cost都是一样的。
SQL>create table test as select * from dba_objects;
SQL> set timing on
SQL> set autotrace traceonly
SQL> select status,count(*) from test group by status;
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 1435881708
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 286 (4)| 00:00:04 |
| 1 | HASH GROUP BY | | 2 | 14 | 286 (4)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST | 89088 | 609K| 279 (2)| 00:00:04 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1230 consistent gets
0 physical reads
0 redo size
432 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> alter session set "_gby_hash_aggregation_enabled" = false;
会话已更改。
SQL> select status,count(*) from test group by status;
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 2603667166
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 286 (4)| 00:00:04 |
| 1 | SORT GROUP BY | | 2 | 14 | 286 (4)| 00:00:04 |
| 2 | TABLE ACCESS FULL| TEST | 89088 | 609K| 279 (2)| 00:00:04 |
---------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1230 consistent gets
0 physical reads
0 redo size
432 bytes sent via SQL*Net to client
350 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed