oracle max 速度,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