大致步骤：

SQL> select id , avg(age) from tt1 group by id;

ID AVG(AGE)
---------- ----------
6 0
4 0
3 0
0 0
10 1

执行计划
----------------------------------------------------------
Plan hash value: 112617873

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 30 | 5 (20)| 00:00:01 |
| 1 | HASH GROUP BY | | 5 | 30 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TT1 | 5 | 30 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

从上面执行计划我们能看的出来10g里面CBO下group by子句对应的执行计划是HASH GROUP BY，而不是以前的SORT GROUP BY，因此group by之后oracle对数据并没有排序。

SQL> select id , avg(age) from tt1 group by id order by id;

ID AVG(AGE)
---------- ----------
0 0
3 0
4 0
6 0
10 1

执行计划
----------------------------------------------------------
Plan hash value: 3830178054

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 30 | 5 (20)| 00:00:01 |
| 1 | SORT GROUP BY | | 5 | 30 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TT1 | 5 | 30 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed

SQL>

当在分组之后加上order by子句（group by id order by id）时执行计划由HASH GROUP BY变成了SORT GROUP BY，而SORT GROUP BY可以保证结果是进行过排序处理的。

SQL> select /*+ rule */ id , avg(age) from tt1 group by id ;

ID AVG(AGE)
---------- ----------
0 0
3 0
4 0
6 0
10 1

执行计划
----------------------------------------------------------
Plan hash value: 3830178054

-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | TABLE ACCESS FULL| TT1 |
-----------------------------------

Note
-----
- rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed

SQL>

上面是在rbo下产生的执行计划，依然使用的是SORT GROUP BY，也就是说rbo下group by之后数据还是进行了排序操作！