原帖地址:http://www.itpub.net/thread-1712774-1-1.html
oracle 9i 下的Group By 后面不加order by ,以前都有默认排序的,现在换成11g后就没有排序了,请问会是什么原因?除了一个一个sql语句加order by外,还有什么方法解决呢?
10g开始就是hashgroup by了啊,要排序,必须最后加orderby,被害了吧,除非你把_gby_hash_aggregation_enabled改为false
9i没有hashgroup by,10g才有,_gby_hash_aggregation_enabled是隐含参数,普通show parameter查不到的
SQL> select ksppinm NAME, ksppstvl VALUE
2 from x$ksppi pi, x$ksppcv cv
3 where cv.indx = pi.indx
4 and pi.ksppinm like'%_gby_hash_aggregation_enabled%';
NAME VALUE
------------------------------------------------------
_gby_hash_aggregation_enabled TRUE
SQL> SELECT/*+full(product)*/ product_id,COUNT(*) FROMdingjun123.product
2 GROUP BY product_id;
执行计划
----------------------------------------------------------
Plan hash value: 2921987230
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 30 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 10 | 30 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| PRODUCT | 10 | 30 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
SQL> ALTER SESSION SET "_gby_hash_aggregation_enabled"= FALSE;
会话已更改。
SQL> SELECT /*+full(product)*/ product_id,COUNT(*) FROMdingjun123.product
2 GROUP BY product_id;
执行计划
----------------------------------------------------------
Plan hash value: 1087780339
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 30 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 10 | 30 | 4 (25)| 00:00:01|
| 2 | TABLE ACCESS FULL| PRODUCT | 10 | 30 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------