第二個查詢多一個rownum,速度就會快一點,其中kq_view是一個視圖,不知道是什麼原因,請指教,謝謝!
[php]
SQL> set timing on
SQL> set autotrace on
SQL> set linesize 999
SQL> select kq_view.*
2from kq_view
3 where 1 = 1
4 and oeb04 = 'GN90P150261B0123';
OEA02OEB01 OEB03 OEB04
OEB12 SUM006 SL B OEB16OEA04OEB15FLOSL V_SUM009 OEA03
---------- ---------- ---------- -------------------- ---------- ---------- ---------- - ---------- ---------- ---------- -- ---------- ---------- -----
2006/03/24 S7E-630223
4 GN90P150261B0123
200 75
0 N 2006/05/16 WE000562006/05/16 沒 17
0 ATP
目前歷時: 00:00:04.47
執行計畫
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=318 Card=94 Bytes=19
270)
10 HASH JOIN (OUTER) (Cost=318 Card=94 Bytes=19270)
21 HASH JOIN (OUTER) (Cost=191 Card=1 Bytes=175)
32 HASH JOIN (OUTER) (Cost=118 Card=1 Bytes=130)
43 VIEW (Cost=5 Card=1 Bytes=86)
54 SORT (ORDER BY) (Cost=5 Card=1 Bytes=75)
65
NESTED LOOPS (Cost=3 Card=1 Bytes=75)
76
TABLE ACCESS (FULL) OF 'OEB_FILE' (Cost=2 Card
=1 Bytes=42)
86
TABLE ACCESS (BY INDEX ROWID) OF 'OEA_FILE' (C
ost=1 Card=1 Bytes=33)
98
INDEX (UNIQUE SCAN) OF 'OEA_01' (UNIQUE)
103 VIEW (Cost=113 Card=885 Bytes=38940)
11 10 SORT (GROUP BY) (Cost=113 Card=885 Bytes=46905)
12 11
TABLE ACCESS (FULL) OF 'TC_SAPA_FILE' (Cost=106
Card=885 Bytes=46905)
132 VIEW (Cost=72 Card=2373 Bytes=106785)
14 13 SORT (GROUP BY) (Cost=72 Card=2373 Bytes=111531)
15 14 TABLE ACCESS (FULL) OF 'TC_SAPC_FILE' (Cost=57 Car
d=2373 Bytes=111531)
161 VIEW (Cost=126 Card=4423 Bytes=132690)
17 16 SORT (GROUP BY) (Cost=126 Card=4423 Bytes=141536)
18 17 TABLE ACCESS (FULL) OF 'TC_SAPA_FILE' (Cost=106 Card
=4423 Bytes=141536)
統計值
----------------------------------------------------------
0recursive calls
56db block gets
4349consistent gets
5398physical reads
0redo size
1194bytes sent via SQL*Net to client
503bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
3sorts (memory)
1sorts (disk)
1rows processed
SQL> select rownum, kq_view.*
2from kq_view
3 where 1 = 1
4 and oeb04 = 'GN90P150261B0123';
ROWNUM OEA02OEB01 OEB03 OEB04
OEB12 SUM006 SL B OEB16OEA04OEB15FLOSL V_SUM0
---------- ---------- ---------- ---------- -------------------- ---------- ---------- ---------- - ---------- ---------- ---------- -- ---------- --------
1 2006/03/24 S7E-630223
4 GN90P150261B0123
200 75
0 N 2006/05/16 WE000562006/05/16 沒 17
目前歷時: 00:00:00.91
執行計畫
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=278 Card=44 Bytes=90
20)
10 COUNT
21 MERGE JOIN (OUTER) (Cost=278 Card=44 Bytes=9020)
32 HASH JOIN (OUTER) (Cost=172 Card=1 Bytes=175)
43 NESTED LOOPS (OUTER) (Cost=112 Card=1 Bytes=130)
54 VIEW (Cost=5 Card=1 Bytes=86)
65
SORT (ORDER BY) (Cost=5 Card=1 Bytes=75)
76
NESTED LOOPS (Cost=3 Card=1 Bytes=75)
87
TABLE ACCESS (FULL) OF 'OEB_FILE' (Cost=2 Ca
rd=1 Bytes=42)
97
TABLE ACCESS (BY INDEX ROWID) OF 'OEA_FILE'
(Cost=1 Card=1 Bytes=33)
109
INDEX (UNIQUE SCAN) OF 'OEA_01' (UNIQUE)
114 VIEW (Cost=108 Card=1 Bytes=44)
12 11
SORT (GROUP BY) (Cost=108 Card=9 Bytes=477)
13 12
TABLE ACCESS (FULL) OF 'TC_SAPA_FILE' (Cost=10
6 Card=9 Bytes=477)
143 VIEW (Cost=59 Card=24 Bytes=1080)
15 14 SORT (GROUP BY) (Cost=59 Card=24 Bytes=1128)
16 15
TABLE ACCESS (FULL) OF 'TC_SAPC_FILE' (Cost=57 C
ard=24 Bytes=1128)
172 BUFFER (SORT) (Cost=278 Card=44 Bytes=1320)
18 17 VIEW (Cost=106 Card=44 Bytes=1320)
19 18 SORT (GROUP BY) (Cost=106 Card=44 Bytes=1408)
20 19
TABLE ACCESS (FULL) OF 'TC_SAPA_FILE' (Cost=106
Card=44 Bytes=1408)
統計值
----------------------------------------------------------
0recursive calls
0db block gets
4349consistent gets
4135physical reads
0redo size
1251bytes sent via SQL*Net to client
503bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
5sorts (memory)
0sorts (disk)
1rows processed
[/php]
|
