下面的SQL在查前1000条时只用了15秒,查前10000条时却用了12分钟,这是怎么回事啊?算正常吗(其中WC有800万记录,UC有1000万条记录)
SQL> SELECT wc.orgunty, wc.orguncd, wc.matnr, wc.ptcsty, wc.perc_mth1,
2 wc.perc_mth2, wc.perc_mth3, wc.perc_mth4, wc.perc_mth5, wc.perc_mth6,
3 wc.perc_mth7, wc.perc_mth8, wc.perc_mth9, wc.perc_mth10, wc.perc_mth11,
4 wc.perc_mth12, wc.perc_mth13, wc.perc_mth14, wc.perc_mth15,
5 wc.perc_mth16, wc.perc_mth17, wc.perc_mth18, wc.upd_src_cd, SYSDATE
6 FROM pci.burden_pct_work1 wc, pci.burden_pct uc
7 WHERE wc.orgunty = uc.orgunty(+)
8 AND wc.orguncd = uc.orguncd(+)
9 AND wc.matnr = uc.matnr(+)
10 AND wc.ptcsty = uc.ptcsty(+)
11 AND (UC.ORGUNCD IS NULL OR
12 WC.PERC_MTH1 <> UC.PERC_MTH1 OR
13 WC.PERC_MTH2 <> UC.PERC_MTH2 OR
14 WC.PERC_MTH3 <> UC.PERC_MTH3 OR
15 WC.PERC_MTH4 <> UC.PERC_MTH4 OR
16 WC.PERC_MTH5 <> UC.PERC_MTH5 OR
17 WC.PERC_MTH6 <> UC.PERC_MTH6 OR
18 WC.PERC_MTH7 <> UC.PERC_MTH7 OR
19 WC.PERC_MTH8 <> UC.PERC_MTH8 OR
20 WC.PERC_MTH9 <> UC.PERC_MTH9 OR
21 WC.PERC_MTH10 <> UC.PERC_MTH10 OR
22 WC.PERC_MTH11 <> UC.PERC_MTH11 OR
23 WC.PERC_MTH12 <> UC.PERC_MTH12 OR
24 WC.PERC_MTH13 <> UC.PERC_MTH13 OR
25 WC.PERC_MTH14 <> UC.PERC_MTH14 OR
26 WC.PERC_MTH15 <> UC.PERC_MTH15 OR
27 WC.PERC_MTH16 <> UC.PERC_MTH16 OR
28 WC.PERC_MTH17 <> UC.PERC_MTH17 OR
29 WC.PERC_MTH18 <> UC.PERC_MTH18 )
30 AND ROWNUM < 1000;
999 rows selected.
Elapsed: 00:00:15.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 COUNT (STOPKEY)
2 1 FILTER
3 2 NESTED LOOPS (OUTER)
4 3 TABLE ACCESS (FULL) OF 'BURDEN_PCT_WORK1'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'BURDEN_PCT'
6 5 INDEX (RANGE SCAN) OF 'IDX_BURDEN_PCT_TEST1' (NON-
UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4963 consistent gets
97 physical reads
0 redo size
40274 bytes sent via SQL*Net to client
1229 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
999 rows processed
SQL> SELECT WC.ORGUNTY,WC.ORGUNCD,WC.MATNR,WC.PTCSTY,
2 WC.PERC_MTH1,WC.PERC_MTH2,WC.PERC_MTH3,WC.PERC_MTH4,WC.PERC_MTH5,WC.PERC_MTH6,
3 WC.PERC_MTH7,WC.PERC_MTH8,WC.PERC_MTH9,WC.PERC_MTH10,WC.PERC_MTH11,WC.PERC_MTH12,
4 WC.PERC_MTH13,WC.PERC_MTH14,WC.PERC_MTH15,WC.PERC_MTH16,WC.PERC_MTH17,WC.PERC_MTH18,
5 WC.UPD_SRC_CD,sysdate
6 FROM PCI.BURDEN_PCT UC,PCI.BURDEN_PCT_WORK1 WC
7 WHERE WC.ORGUNTY = UC.ORGUNTY(+) AND
8 WC.ORGUNCD = UC.ORGUNCD(+) AND
9 WC.MATNR = UC.MATNR(+) AND
10 WC.PTCSTY = UC.PTCSTY(+) AND
11 (UC.ORGUNCD IS NULL OR
12 WC.PERC_MTH1 <> UC.PERC_MTH1 OR
13 WC.PERC_MTH2 <> UC.PERC_MTH2 OR
14 WC.PERC_MTH3 <> UC.PERC_MTH3 OR
15 WC.PERC_MTH4 <> UC.PERC_MTH4 OR
16 WC.PERC_MTH5 <> UC.PERC_MTH5 OR
17 WC.PERC_MTH6 <> UC.PERC_MTH6 OR
18 WC.PERC_MTH7 <> UC.PERC_MTH7 OR
19 WC.PERC_MTH8 <> UC.PERC_MTH8 OR
20 WC.PERC_MTH9 <> UC.PERC_MTH9 OR
21 WC.PERC_MTH10 <> UC.PERC_MTH10 OR
22 WC.PERC_MTH11 <> UC.PERC_MTH11 OR
23 WC.PERC_MTH12 <> UC.PERC_MTH12 OR
24 WC.PERC_MTH13 <> UC.PERC_MTH13 OR
25 WC.PERC_MTH14 <> UC.PERC_MTH14 OR
26 WC.PERC_MTH15 <> UC.PERC_MTH15 OR
27 WC.PERC_MTH16 <> UC.PERC_MTH16 OR
28 WC.PERC_MTH17 <> UC.PERC_MTH17 OR
29 WC.PERC_MTH18 <> UC.PERC_MTH18 )
30 and rownum<10000;
9999 rows selected.
Elapsed: 00:12:06.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 COUNT (STOPKEY)
2 1 FILTER
3 2 NESTED LOOPS (OUTER)
4 3 TABLE ACCESS (FULL) OF 'BURDEN_PCT_WORK1'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'BURDEN_PCT'
6 5 INDEX (RANGE SCAN) OF 'IDX_BURDEN_PCT_TEST1' (NON-
UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6324513 consistent gets
223535 physical reads
0 redo size
384233 bytes sent via SQL*Net to client
7829 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9999 rows processed
INDEX 'IDX_BURDEN_PCT_TEST1 IS ON BURDEN_PCT(ORGUNTY,ORGUNCD,MATNR,PTCSTY)