SELECT ck_wzmx.cghthm,
ck_wzmx.hz,
ck_wzmx.ck,
ck_wzmx.cz,
ck_wzmx.gg,
ck_wzmx.cd,
ck_wzmx.cghtstr1,
ck_wzmx.rkmdstr4,
ck_wzmx.rkmdh,
ck_wzmx.kh,
ck_wzmx.pm,
sum (ck_wzmx.sl1)-sum( v_ck_wzmxdhl.sl1),
sum (ck_wzmx.sl2)-sum( v_ck_wzmxdhl.sl2)
FROM
v_ck_wzmxdhl, ck_wzmx
WHERE
ck_wzmx.hz = v_ck_wzmxdhl.hz(+)
AND ck_wzmx.ck = v_ck_wzmxdhl.ck(+)
AND ck_wzmx.cz = v_ck_wzmxdhl.cz(+)
AND ck_wzmx.gg = v_ck_wzmxdhl.gg(+)
AND ck_wzmx.cd = v_ck_wzmxdhl.cd(+)
AND ck_wzmx.cghtstr1 = v_ck_wzmxdhl.cghtstr1(+)
AND NVL (ck_wzmx.rkmdstr4, ' ') = NVL (v_ck_wzmxdhl.rkmdstr4(+), ' ')
AND ck_wzmx.rkmdh = v_ck_wzmxdhl.rkmdh(+)
AND ck_wzmx.kh = v_ck_wzmxdhl.kh(+)
and ck_wzmx.pm = v_ck_wzmxdhl.pm(+)
GROUP BY ck_wzmx.cghthm,
ck_wzmx.hz,
ck_wzmx.ck,
ck_wzmx.cz,
ck_wzmx.gg,
ck_wzmx.cd,
ck_wzmx.cghtstr1,
ck_wzmx.rkmdstr4,
ck_wzmx.rkmdh,
ck_wzmx.kh,
ck_wzmx.pm,
ck_wzmx.sl1,
ck_wzmx.sl2由于是MSqlServer 转到 oracle
不能跟客户说 oracle 不行吧,没法说啊
v_ck_wzmxdhl 也是个复杂视图
v_ck_wzmxdhl 50条, ck_wzmx 40万
时间 00:01:58.04
sqlserver 不到30秒
SQL> select sum(sl1),sum(sl2) from v_ck_wzmxb_test; SUM(SL1) SUM(SL2)
---------- ----------
2113694 972333.548Elapsed: 00:01:58.04Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=55942 Card=1 Bytes
=26) 1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=55942 Card=209 Bytes=5434)
3 2 HASH (GROUP BY) (Cost=55942 Card=209 Bytes=516021)
4 3 HASH JOIN (RIGHT OUTER) (Cost=3282 Card=339401 Bytes
=837981069) 5 4 TABLE ACCESS (FULL) OF 'BD_CKKW' (TABLE) (Cost=2 C
ard=59 Bytes=1003) 6 4 HASH JOIN (RIGHT OUTER) (Cost=3277 Card=339401 Byt
es=832211252) 7 6 VIEW OF 'V_CK_WZMXDHL' (VIEW) (Cost=52 Card=48 B
ytes=9936) 8 7 UNION-ALL
9 8 HASH (GROUP BY) (Cost=36 Card=1 Bytes=1482)
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'CK_CKD_M
A' (TABLE) (Cost=1 Card=1 Bytes=1462) 11 10 NESTED LOOPS (Cost=35 Card=1 Bytes=1482)
12 11 TABLE ACCESS (FULL) OF 'CK_CKD' (TABLE
) (Cost=34 Card=5 Bytes=100) 13 11 INDEX (RANGE SCAN) OF 'CK_CKD_MA_FPHM'
(INDEX) (Cost=0 Card=1) 14 8 HASH (GROUP BY) (Cost=6 Card=2 Bytes=540)
15 14 TABLE ACCESS (BY INDEX ROWID) OF 'CK_SHD_M
D' (TABLE) (Cost=3 Card=2 Bytes=480) 16 15 NESTED LOOPS (Cost=5 Card=2 Bytes=540)
17 16 TABLE ACCESS (FULL) OF 'CK_SHD' (TABLE
) (Cost=2 Card=1 Bytes=30) 18 16 INDEX (RANGE SCAN) OF 'CK_SHD_MD_INDEX
_FPHM' (INDEX) (Cost=0 Card=34) 19 8 NESTED LOOPS (ANTI) (Cost=6 Card=1 Bytes=137
0) 20 19 TABLE ACCESS (FULL) OF 'CK_ZYD_MD' (TABLE)
(Cost=5 Card=1 Bytes=1354) 21 19 TABLE ACCESS (BY INDEX ROWID) OF 'CK_ZYD'
(TABLE) (Cost=1 Card=1 Bytes=16) 22 21 INDEX (UNIQUE SCAN) OF 'SYS_C0010425' (I
NDEX (UNIQUE)) (Cost=0 Card=1) 23 8 HASH (GROUP BY) (Cost=4 Card=44 Bytes=10604)
24 23 TABLE ACCESS (FULL) OF 'CK_SGSD_MD' (TABLE
) (Cost=3 Card=44 Bytes=10604) 25 6 HASH JOIN (RIGHT OUTER) (Cost=3222 Card=339401 B
ytes=761955245) 26 25 TABLE ACCESS (FULL) OF 'BD_CK' (TABLE) (Cost=2
Card=5 Bytes=45) 27 25 TABLE ACCESS (FULL) OF 'CK_WZMX' (TABLE) (Cost
=3218 Card=339401 Bytes=758900636)Statistics
----------------------------------------------------------
311 recursive calls
0 db block gets
10015 consistent gets
16395 physical reads
0 redo size
415 bytes sent via SQL*Net to client
435 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
ck_wzmx.hz,
ck_wzmx.ck,
ck_wzmx.cz,
ck_wzmx.gg,
ck_wzmx.cd,
ck_wzmx.cghtstr1,
ck_wzmx.rkmdstr4,
ck_wzmx.rkmdh,
ck_wzmx.kh,
ck_wzmx.pm,
sum (ck_wzmx.sl1)-sum( v_ck_wzmxdhl.sl1),
sum (ck_wzmx.sl2)-sum( v_ck_wzmxdhl.sl2)
FROM
v_ck_wzmxdhl, ck_wzmx
WHERE
ck_wzmx.hz = v_ck_wzmxdhl.hz(+)
AND ck_wzmx.ck = v_ck_wzmxdhl.ck(+)
AND ck_wzmx.cz = v_ck_wzmxdhl.cz(+)
AND ck_wzmx.gg = v_ck_wzmxdhl.gg(+)
AND ck_wzmx.cd = v_ck_wzmxdhl.cd(+)
AND ck_wzmx.cghtstr1 = v_ck_wzmxdhl.cghtstr1(+)
AND NVL (ck_wzmx.rkmdstr4, ' ') = NVL (v_ck_wzmxdhl.rkmdstr4(+), ' ')
AND ck_wzmx.rkmdh = v_ck_wzmxdhl.rkmdh(+)
AND ck_wzmx.kh = v_ck_wzmxdhl.kh(+)
and ck_wzmx.pm = v_ck_wzmxdhl.pm(+)
GROUP BY ck_wzmx.cghthm,
ck_wzmx.hz,
ck_wzmx.ck,
ck_wzmx.cz,
ck_wzmx.gg,
ck_wzmx.cd,
ck_wzmx.cghtstr1,
ck_wzmx.rkmdstr4,
ck_wzmx.rkmdh,
ck_wzmx.kh,
ck_wzmx.pm,
ck_wzmx.sl1,
ck_wzmx.sl2由于是MSqlServer 转到 oracle
不能跟客户说 oracle 不行吧,没法说啊
v_ck_wzmxdhl 也是个复杂视图
v_ck_wzmxdhl 50条, ck_wzmx 40万
时间 00:01:58.04
sqlserver 不到30秒
SQL> select sum(sl1),sum(sl2) from v_ck_wzmxb_test; SUM(SL1) SUM(SL2)
---------- ----------
2113694 972333.548Elapsed: 00:01:58.04Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=55942 Card=1 Bytes
=26) 1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=55942 Card=209 Bytes=5434)
3 2 HASH (GROUP BY) (Cost=55942 Card=209 Bytes=516021)
4 3 HASH JOIN (RIGHT OUTER) (Cost=3282 Card=339401 Bytes
=837981069) 5 4 TABLE ACCESS (FULL) OF 'BD_CKKW' (TABLE) (Cost=2 C
ard=59 Bytes=1003) 6 4 HASH JOIN (RIGHT OUTER) (Cost=3277 Card=339401 Byt
es=832211252) 7 6 VIEW OF 'V_CK_WZMXDHL' (VIEW) (Cost=52 Card=48 B
ytes=9936) 8 7 UNION-ALL
9 8 HASH (GROUP BY) (Cost=36 Card=1 Bytes=1482)
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'CK_CKD_M
A' (TABLE) (Cost=1 Card=1 Bytes=1462) 11 10 NESTED LOOPS (Cost=35 Card=1 Bytes=1482)
12 11 TABLE ACCESS (FULL) OF 'CK_CKD' (TABLE
) (Cost=34 Card=5 Bytes=100) 13 11 INDEX (RANGE SCAN) OF 'CK_CKD_MA_FPHM'
(INDEX) (Cost=0 Card=1) 14 8 HASH (GROUP BY) (Cost=6 Card=2 Bytes=540)
15 14 TABLE ACCESS (BY INDEX ROWID) OF 'CK_SHD_M
D' (TABLE) (Cost=3 Card=2 Bytes=480) 16 15 NESTED LOOPS (Cost=5 Card=2 Bytes=540)
17 16 TABLE ACCESS (FULL) OF 'CK_SHD' (TABLE
) (Cost=2 Card=1 Bytes=30) 18 16 INDEX (RANGE SCAN) OF 'CK_SHD_MD_INDEX
_FPHM' (INDEX) (Cost=0 Card=34) 19 8 NESTED LOOPS (ANTI) (Cost=6 Card=1 Bytes=137
0) 20 19 TABLE ACCESS (FULL) OF 'CK_ZYD_MD' (TABLE)
(Cost=5 Card=1 Bytes=1354) 21 19 TABLE ACCESS (BY INDEX ROWID) OF 'CK_ZYD'
(TABLE) (Cost=1 Card=1 Bytes=16) 22 21 INDEX (UNIQUE SCAN) OF 'SYS_C0010425' (I
NDEX (UNIQUE)) (Cost=0 Card=1) 23 8 HASH (GROUP BY) (Cost=4 Card=44 Bytes=10604)
24 23 TABLE ACCESS (FULL) OF 'CK_SGSD_MD' (TABLE
) (Cost=3 Card=44 Bytes=10604) 25 6 HASH JOIN (RIGHT OUTER) (Cost=3222 Card=339401 B
ytes=761955245) 26 25 TABLE ACCESS (FULL) OF 'BD_CK' (TABLE) (Cost=2
Card=5 Bytes=45) 27 25 TABLE ACCESS (FULL) OF 'CK_WZMX' (TABLE) (Cost
=3218 Card=339401 Bytes=758900636)Statistics
----------------------------------------------------------
311 recursive calls
0 db block gets
10015 consistent gets
16395 physical reads
0 redo size
415 bytes sent via SQL*Net to client
435 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
sql精简过了,where 条件所涉及字段都加过索引了
欢迎高人指点
你是说没有办法了么?
另外ck_wzmx 已经放到keep池了
0 db block gets
10015 consistent gets
16395 physical reads
0 redo size
415 bytes sent via SQL*Net to client
435 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed 成本太高了,肯定没有索引
写法也比较奇怪用左连接吧
0 db block gets
10015 consistent gets
16395 physical reads
0 redo size
415 bytes sent via SQL*Net to client
435 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed 成本太高了,肯定没有索引
写法也比较奇怪用左连接吧