ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 统计2008年每张单的数量---用group by 与否的巨大区别

统计2008年每张单的数量---用group by 与否的巨大区别

原创 Linux操作系统 作者:qgw521 时间:2008-12-31 14:02:58 0 删除 编辑

连接到:

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

 

SQL> set timing on;

SQL> set autotrace traceonly;

SQL> SELECT ORDNO,(SELECT SUM(QTY) FROM ORDITMDTL OD WHERE OD.ORDSEQ=O.ORDSEQ) QTY

  2  FROM ORD O

  3   WHERE O.ORDDT>TRUNC(SYSDATE,'YY');

 

已选择52316行。

 

已用时间:  00: 00: 07.09

 

执行计划

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=275 Card=274 Bytes=5

          754)

 

   1    0   SORT (AGGREGATE)

   2    1     TABLE ACCESS (CLUSTER) OF 'ORDITMDTL' (Cost=3 Card=10 By

          tes=70)

 

   3    2       INDEX (UNIQUE SCAN) OF 'ORD$CLUSTER$IDX' (NON-UNIQUE)

          (Cost=2 Card=1)

 

   4    0   TABLE ACCESS (BY INDEX ROWID) OF 'ORD' (Cost=275 Card=274

          Bytes=5754)

 

   5    4     INDEX (RANGE SCAN) OF 'ORD$IDX29' (NON-UNIQUE) (Cost=3 C

          ard=274)

 

 

 

 

 

统计信息

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

          0  recursive calls

          0  db block gets

     240722  consistent gets

      72009  physical reads

          0  redo size

    1115071  bytes sent via SQL*Net to client

      38791  bytes received via SQL*Net from client

       3489  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      52316  rows processed

 

SQL> select o.ordno,sum(od.qty) qty

  2  from orditmdtl od,ord o

  3  where od.ordseq=o.ordseq

  4  group by o.ordno;

 

已选择395483行。

 

已用时间:  00: 01: 07.29

 

执行计划

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=551663 Card=421144 B

          ytes=8844024)

 

   1    0   SORT (GROUP BY) (Cost=551663 Card=421144 Bytes=8844024)

   2    1     NESTED LOOPS (Cost=539991 Card=4684630 Bytes=98377230)

   3    2       TABLE ACCESS (FULL) OF 'ORD' (Cost=62737 Card=477254 B

          ytes=6681556)

 

   4    2       TABLE ACCESS (CLUSTER) OF 'ORDITMDTL' (Cost=1 Card=10

          Bytes=70)

 

 

 

 

 

统计信息

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

          0  recursive calls

          0  db block gets

    1781627  consistent gets

     738901  physical reads

          0  redo size

    9892057  bytes sent via SQL*Net to client

     290449  bytes received via SQL*Net from client

      26367  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

395483  rows processed

 

 

对比一下:

             不用group by                    group by   

执行时间      00: 00: 07.09                    00: 01: 07.29

consistent gets   240722                          1781627

physical reads    72009                           738901

 

对比这三项可想而知用group by 消耗资源是很大的,所以在需要用到group by的时候不妨想想能不能用其他方法去代替,以获得更好的性能.

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

请登录后发表评论 登录
全部评论

注册时间:2008-06-06

  • 博文量
    128
  • 访问量
    276795