group by 需要排序吗？

研究了一下，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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-742876/