【IT168 技术文档】在10gR2中，group by由以前的sort group by改成了hash group by，这种算法上的改进，取消了sort group by必须进行的排序操作，即然是用hash算法，就存在碰撞的可能性，itpub的godlessme就碰到这样的问题，应该算是bug吧。

    下面给大家演示一下如何解决这种问题，其实要解决hash group by引起的排序不准确的问题，就是还用以前的sort group by就可以啦，10gR2中引入_gby_hash_aggregation_enabled隐藏参数，该参数默认设置为true，将它改成false即可。

    SQL> select status,count(*) from tmp_object group by status;
    STATUS COUNT(*)
    ---- -----
    INVALID 29
    VALID 10236

    Execution Plan
    -----------------------------
    Plan hash value: 3490974944
    -------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------
    | 0 | SELECT STATEMENT | | 2 | 12 | 35 (6)| 00:00:01 |
    | 1 | HASH GROUP BY | | 2 | 12 | 35 (6)| 00:00:01 |
    | 2 | TABLE ACCESS FULL| TMP_OBJECT | 10265 | 61590 | 33 (0)| 00:00:01 |
    -------------------------------------
    Statistics
    -----------------------------
    24 recursive calls
    0 db block gets
    136 consistent gets
    0 physical reads
    0 redo size
    522 bytes sent via SQL*Net to client
    385 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    2 rows processed

    SQL> col ksppinm format a39
    SQL> col ksppstvl format a39
    SQL> select ksppinm, ksppstvl
    2 from x$ksppi pi, x$ksppcv cv
    3 where cv.indx=pi.indx and pi.ksppinm like '_%' escape ''
    4 and pi.ksppinm like '%&parameter%';
    Enter value for parameter: gby
    old 4: and pi.ksppinm like '%&parameter%'
    new 4: and pi.ksppinm like '%gby%'

    KSPPINM KSPPSTVL
    -------------------- ------------
    _gby_onekey_enabled TRUE
    _gby_hash_aggregation_enabled TRUE

    SQL> alter session set "_gby_hash_aggregation_enabled"=false;
    Session altered.
    SQL> select status,count(*) from tmp_object group by status;
    STATUS COUNT(*)
    ---- -----
    INVALID 29
    VALID 10312

    Execution Plan
    -----------------------------
    Plan hash value: 1360369603
    -------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------
    | 0 | SELECT STATEMENT | | 10860 | 54300 | 32 (7)| 00:00:01 |
    | 1 | SORT GROUP BY | | 10860 | 54300 | 32 (7)| 00:00:01 |
    | 2 | TABLE ACCESS FULL| TMP_OBJECT | 10860 | 54300 | 30 (0)| 00:00:01 |
    -------------------------------------
    Statistics
    -----------------------------
    0 recursive calls
    0 db block gets
    134 consistent gets
    0 physical reads
    0 redo size
    522 bytes sent via SQL*Net to client
    385 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    2 rows processed