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