ITPub博客

首页 > 数据库 > Oracle > 一次对group by的优化

一次对group by的优化

Oracle 作者:KBmohei 时间:2015-07-28 00:31:27 0 删除 编辑
原文转自:http://www.hellodba.com/reader.php?ID=115&lang=CN

1

SQL> select CDE, CID

  2  from CCMMT

  3  GROUP BY CDE, CID

  4  having max(ADT) < sysdate - 180;

 

707924 rows selected.

 

Elapsed: 00:06:17.49

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=414 Card=238583 Bytes=4771660)

   1    0   FILTER

   2    1     SORT (GROUP BY NOSORT) (Cost=414 Card=238583 Bytes=4771660)

   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'CCMMT' (Cost=414 Card=57969096 Bytes=1159381920)

   4    3         INDEX (FULL SCAN) OF 'CCMMT_TEMP_IDX' (NON-UNIQUE) (Cost=26 Card=57969096)

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

    2769177  consistent gets

    1089991  physical reads

          0  redo size

   23926954  bytes sent via SQL*Net to client

     519785  bytes received via SQL*Net from client

      47196  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

     707924  rows processed

对group by语句改写后的sql
2、

SQL> select DISTINCT CDE, CID

  2  from CCMMT

  3  where ADT < sysdate - 180

  4  minus

  5  select DISTINCT CDE, CID

  6  from CCMMT

  7  where ADT >= sysdate - 180;

 

707924 rows selected.

 

Elapsed: 00:00:21.53

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=190624 Card=2794940

          Bytes=111797600)

   1    0   MINUS

   2    1     SORT* (UNIQUE) (Cost=95312 Card=2794940 Bytes=55898800)                                               :Q13049001

   3    2       INDEX* (FAST FULL SCAN) OF 'CCMMT_UQ1' (UNIQUE) (Cost=77305 Card=2898455 Bytes=57969100)    :Q13049000

   4    1     SORT* (UNIQUE) (Cost=95312 Card=2794940 Bytes=55898800)                                               :Q13050001

   5    4       INDEX* (FAST FULL SCAN) OF 'CCMMT_UQ1' (UNIQUE) (Cost=77305 Card=2898455 Bytes=57969100)    :Q13050000

 

   2 PARALLEL_TO_SERIAL            SELECT DISTINCT C0 C0,C1 C1 FROM :Q13049000 ORDER BY C0,C1

   3 PARALLEL_TO_PARALLEL          SELECT /*+ INDEX_RRS(A1 "CCMMT_UQ1")*/ A1."CDE" C0,A1."CA

   4 PARALLEL_TO_SERIAL            SELECT DISTINCT C0 C0,C1 C1 FROM :Q13050000 ORDER BY C0,C1

   5 PARALLEL_TO_PARALLEL          SELECT /*+ INDEX_RRS(A1 "CCMMT_UQ1")*/ A1."CDE" C0,A1."CA

 

Statistics

----------------------------------------------------------

          0  recursive calls

         33  db block gets

     126566  consistent gets

     129243  physical reads

          0  redo size

   18461368  bytes sent via SQL*Net to client

     519785  bytes received via SQL*Net from client

      47196  SQL*Net roundtrips to/from client

          4  sorts (memory)

          2  sorts (disk)

     707924  rows processed


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27119051/viewspace-1753683/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2015-07-15

  • 博文量
    6
  • 访问量
    18109