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

连接到:

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

转载于:http://blog.itpub.net/13387766/viewspace-524783/