这个sql 怎么优化,难道oracle 比sqlserver差??

xlongbuilder 2009-02-09 04:33:04

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.548

Elapsed: 00:01:58.04

Execution 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

...全文

114 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

notmain 2009-02-10

打赏
举报

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

成本太高了,肯定没有索引
写法也比较奇怪用左连接吧

notmain 2009-02-10