请教一个group by 子句慢的问题

〇〇 · 发表于 2017-4-16 07:47

去掉sum,快了30%

SQL> set autot on
SQL> with t as(select mod(level,10)id,level l from dual connect by level<=1e6)
  2  select id,sum(l) from t group by id;

      ID    SUM(L)
---------- ----------
      1 5.0000E+10
      6 5.0000E+10
      2 5.0000E+10
      4 5.0000E+10
      5 5.0000E+10
      8 5.0000E+10
      3 5.0000E+10
      7 5.0000E+10
      9 5.0000E+10
      0 5.0001E+10

已选择10行。

已用时间:  00: 00: 01.59

执行计划
----------------------------------------------------------
Plan hash value: 3650845036

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |    |    1 | 26 |    3  (34)| 00:00:01 |
| 1 |  HASH GROUP BY                |    |    1 | 26 |    3  (34)| 00:00:01 |
| 2 | VIEW                      |    |    1 | 26 |    2 (0)| 00:00:01 |
|*  3 | CONNECT BY WITHOUT FILTERING|    |    |    |          |       |
| 4 |    FAST DUAL                |    |    1 |    |    2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter(LEVEL<=1e6)

统计信息
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
      642  bytes sent via SQL*Net to client
      419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      10  rows processed

SQL> with t as(select mod(level,10)id,level l from dual connect by level<=1e6)
  2  select id  from t group by id;

      ID
----------
      1
      6
      2
      4
      5
      8
      3
      7
      9
      0

已选择10行。

已用时间:  00: 00: 01.18

执行计划
----------------------------------------------------------
Plan hash value: 3650845036

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |    |    1 | 13 |    3  (34)| 00:00:01 |
| 1 |  HASH GROUP BY                |    |    1 | 13 |    3  (34)| 00:00:01 |
| 2 | VIEW                      |    |    1 | 13 |    2 (0)| 00:00:01 |
|*  3 | CONNECT BY WITHOUT FILTERING|    |    |    |          |       |
| 4 |    FAST DUAL                |    |    1 |    |    2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter(LEVEL<=1e6)

统计信息
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      0  consistent gets
      0  physical reads
      0  redo size
      525  bytes sent via SQL*Net to client
      419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      10  rows processed

SQL>