Oracle Max()/Min()类的性能优化

工作中的经验。

此文写得仓促，较为完整的探讨在

http://karsus.itpub.net/post/36558/470885

[@more@]

1.SELECT MAX(SEQ) FROM MO_ROUTE WHERE ISN=UPPER('NF1S6500020029' ) AND STEP <> '0';是在OMS的 TOP SQL按Disk IO排序出最高的SQL.

2.select SEQ FROM MO_ROUTE WHERE ISN=UPPER('NF1S6500020029' ) AND STEP <> '0' and rownum=1 order by seq desc;是改写之后的.

2比1的效能好很多，几乎完全消除了physical reads. consistent gets也少很多。按我的经验，MAX/MIN 类这样改写效能都会比原先好（包括SQL Server也是如此,rownum使用TOP 1替代，在SQL2005上效果稍弱一些）。

1. PLAN:

2. 15:45:29 SQL> SELECT MAX(SEQ) FROM MO_ROUTE WHERE ISN=UPPER('NF1S6500020029' ) AND STEP <> '0';

3. 22

4.

5. Elapsed: 00:00:00.26

6.

7. Execution Plan

8. ----------------------------------------------------------

9. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=30)

10. 1 0 SORT (AGGREGATE)

11. 2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MO_ROUTE' (Cost

12. =13 Card=9 Bytes=270)

13.

14. 3 2 INDEX (RANGE SCAN) OF 'MO_ROUTE1' (UNIQUE) (Cost=4 Car

15. d=9)

16.

17.

18.

19.

20.

21. Statistics

22. ----------------------------------------------------------

23. 18 recursive calls

24. 0 db block gets

25. 27 consistent gets-------------多次运行cache后，25

26. 23 physical reads--------------多次运行cache后，0

27. 0 redo size

28. 518 bytes sent via SQL*Net to client

29. 655 bytes received via SQL*Net from client

30. 2 SQL*Net roundtrips to/from client

31. 0 sorts (memory)

32. 0 sorts (disk)

33. 1 rows processed

2．PLAN

15:51:27 SQL> select seq FROM MO_ROUTE WHERE ISN=UPPER('NF1S6500020029' ) AND STEP <> '0' and rownum=1 order by seq desc;

22

Elapsed: 00:00:00.00

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=30)

1 0 COUNT (STOPKEY)

2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MO_ROUTE' (Cost

=13 Card=9 Bytes=270)

3 2 INDEX (RANGE SCAN DESCENDING) OF 'MO_ROUTE1' (UNIQUE)

(Cost=4 Card=9)

Statistics

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

0 recursive calls

0 db block gets

5 consistent gets 首次执行和多次执行一样。

0 physical reads

0 redo size

513 bytes sent via SQL*Net to client

655 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

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

转载于:http://blog.itpub.net/10856805/viewspace-1000136/