10g以上的Oracle group by 語法並不會保證sort排序

今天客戶反映11.2.0.1 的資料庫 下了group by 和9i 的行為不一樣,
在網路上查了一下,

將以下參數由default 的 true è 改為false 就正常了，10g以上的group by 並不會保證撈取資料的sort , 除非修改此參數~

"_gby_hash_aggregation_enabled parameter" = false

example :
select ... from ...
group by f1, f2...

要改寫為 
(select ... from ...
group by f1, f2
order by f1, f2)

或把資料庫參數
"_gby_hash_aggregation_enabled parameter" = false


參考資料
Default Sorting behaviour of Oracle 9i in 11g along with group by clause

https://forums.oracle.com/forums/thread.jspa?threadID=2266443

底下這篇文章也有提到9i to 10g 應該注意的兩個參數

http://oradbatips.blogspot.tw/2007/07/tip-51-performance-issue-after-upgrade.html
SQL>
alter session set "_optimizer_cost_based_transformation" =off; (Disable subquery unnesting and view merging -- New 10g optimizer feature).

alter session set "_gby_hash_aggregation_enabled" = FALSE; (Disable Hash group by aggregation -- New 10g optimizer feature).