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