group by 需要排序嗎？

lsq_008發表於2012-09-07
排序
研究了一下，group by是否排序是受隱含引數_gby_hash_aggregation_enabled控制的，如下測試：
SQL> alter system set "_gby_hash_aggregation_enabled"=false;

System altered.

SQL> set autot traceonly
SQL> select object_type,max(object_name) from test group by object_type;

41 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2603667166

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   380K|    27M|  1262   (2)| 00:00:16 |
|   1 |  SORT GROUP BY     |      |   380K|    27M|  1262   (2)| 00:00:16 |
|   2 |   TABLE ACCESS FULL| TEST |   380K|    27M|  1250   (1)| 00:00:15 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       5683  consistent gets
          0  physical reads
          0  redo size
       2019  bytes sent via SQL*Net to client
        421  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         41  rows processed

SQL> alter system set "_gby_hash_aggregation_enabled"=true;

System altered.

SQL> select object_type,max(object_name) from test group by object_type;

41 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1435881708

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  3061K|   224M|  1379  (10)| 00:00:17 |
|   1 |  HASH GROUP BY     |      |  3061K|   224M|  1379  (10)| 00:00:17 |
|   2 |   TABLE ACCESS FULL| TEST |  3061K|   224M|  1273   (3)| 00:00:16 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5612  consistent gets
          0  physical reads
          0  redo size
       1995  bytes sent via SQL*Net to client
        421  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         41  rows processed

從10g之後，group by預設採用了hash group by，不再排序了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10972173/viewspace-742876/，如需轉載，請註明出處，否則將追究法律責任。

相關文章