從10g開始CBO下group by之後不能保證資料是按照分組欄位排序的！

從10g開始CBO下group by之後不能保證資料是按照分組欄位排序的！[@more@]

大致步驟：

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之後資料還是進行了排序操作！