星期二, 12月 13, 2011

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).

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...