SORT AGGREGATE

最新推荐文章于 2022-11-22 23:36:23 发布

WTK870424

最新推荐文章于 2022-11-22 23:36:23 发布

阅读量5.5k

点赞数 1

分类专栏：数据库文章标签： sql function disk

数据库专栏收录该内容

61 篇文章

订阅专栏

本文详细解释了SQL中的SORTAGGREGATE与GROUP BY两个选项的区别及其在不同场景下的应用，包括它们如何影响执行计划和性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SORT AGGREGATE做为sort的option之一比较特殊，他并不做sort

SORT AGGREGATE作用于所有的data set上，用于aggregate function，例如sum, count, avg, min, max

SQL> select max(x) from test;

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=5)
   1 0 SORT (AGGREGATE)
   2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'TEST_IDX' (NON-UNIQUE) (
   Cost=3 Card=7140 Bytes=35700)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
   2 consistent gets
   0 physical reads
   0 redo size
   491 bytes sent via SQL*Net to client
   650 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory) -- no sort here
   0 sorts (disk)
   1 rows processed

如果aggregate function不是作用于与所有的data set上，还是作用于不同的group上，那么操作类型将会变为SORT (GROUP BY)，这时会有sort发生

SQL> select x,count(*) from test group by x;

7138 rows selected.

Execution Plan
----------------------------------------------------------
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=7137 Bytes=3
   5685)

   1 0 SORT (GROUP BY) (Cost=20 Card=7137 Bytes=35685)
   2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=3 Card=7140 Bytes=35
   700)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
   19 consistent gets
   0 physical reads
   0 redo size
   151790 bytes sent via SQL*Net to client
   5875 bytes received via SQL*Net from client
   477 SQL*Net roundtrips to/from client
   1 sorts (memory)
   0 sorts (disk)
   7138 rows processed