Oracle Max()/Min()類的效能最佳化

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