请各位高人帮忙看一个sql，谢谢！！！▲▲▲▲▲▲▲▲▲▲

[betagain]

因为数据比较多，所以运行以下sql的速度比较慢(.net+oracle),请
我已经黔驴技穷了，各位高手看看能不能给换个写法，优化下，谢谢！！！

TRIM(A.SHHNCD || '･' || A.SHHNNM) AS SHH,
DECODE(A.ZKSBMK,'0','A','2','B','9','C') ZKSBMK,
ROUND(C.HZAISU/DECODE(A.IRISU,'','1','0','1',A.IRISU),0) AS HZAISU,
ROUND(D.NZAISU/DECODE(A.IRISU,'','1','0','1',A.IRISU),0) AS NZAISU,
DECODE(TRIM(E.TANIKB), '','',TRIM(E.TANIKB || '･' || E.TNIKNJNM)) AS DAN,
(ROUND(C.HZAISU/DECODE(A.IRISU,'','1','0','1',A.IRISU),0) + ROUND(D.NZAISU/DECODE(A.IRISU,'','1','0','1',A.IRISU),0) ) AS GOKEI
FROM MTSHHN A, MTTORI B,
(SELECT SHHNCD, SUM(ZAISU) AS HZAISU
FROM TRLPZR WHERE ZAISU > '0'
AND YDT >= '20080801'
AND YDT <= '20080831'
AND LPCD = '700000' GROUP BY SHHNCD) C,
(SELECT SHHNCD, SUM(ZAISU) AS NZAISU
FROM TRLPZR WHERE ZAISU > '0'
AND YDT >= '20080801'
AND YDT <= '20080831'
AND LPCD = '800000' GROUP BY SHHNCD) D,
MTTANI E
WHERE
A.LPZKKB = '1'
AND B.TORIKB = 'M'
AND A.TRO2CD = B.TORICD
AND A.SHHNCD = C.SHHNCD
AND A.SHHNCD = D.SHHNCD
AND E.TANIKB(+) = '0'
ORDER BY A.SHHNCD, A.SHHNNM

[bugchen888]

MTTANI E 这个表跟其他表没有关联条件吗?

[rexyudl]

做个视图，或者存储过程！

[ab5669]

建议把上述SQL代码作成存储过程, 在.net中执行存储过程

[betagain]

上面的有字符的错误，我重新发一下

TRIM(A.SHHNCD || '･' || A.SHHNNM) AS SHH,
DECODE(A.ZKSBMK,'0','A','2','B','9','C') ZKSBMK,
ROUND(C.HZAISU/DECODE(A.IRISU,'','1','0','1',A.IRISU),0) AS HZAISU,
ROUND(D.NZAISU/DECODE(A.IRISU,'','1','0','1',A.IRISU),0) AS NZAISU,
DECODE(TRIM(E.TANIKB), '','',TRIM(E.TANIKB || '･' || E.TNIKNJNM)) AS DAN,
(ROUND(C.HZAISU/DECODE(A.IRISU,'','1','0','1',A.IRISU),0) + ROUND(D.NZAISU/DECODE(A.IRISU,'','1','0','1',A.IRISU),0) ) AS GOKEI
FROM MTSHHN A, MTTORI B,
(SELECT SHHNCD, SUM(ZAISU) AS HZAISU
FROM TRLPZR WHERE ZAISU > '0'
AND YDT >= '20080801'
AND YDT <= '20080831'
AND LPCD = '700000' GROUP BY SHHNCD) C,
(SELECT SHHNCD, SUM(ZAISU) AS NZAISU
FROM TRLPZR WHERE ZAISU > '0'
AND YDT >= '20080801'
AND YDT <= '20080831'
AND LPCD = '800000' GROUP BY SHHNCD) D,
MTTANI E
WHERE
A.LPZKKB = '1'
AND B.TORIKB = 'M'
AND A.TRO2CD = B.TORICD
AND A.SHHNCD = C.SHHNCD
AND A.SHHNCD = D.SHHNCD
AND E.TANIKB(+) = '0'
ORDER BY A.SHHNCD, A.SHHNNM

[wamlaw]

呼唤大家~~~帮帮我啊，谢谢~~~

[oracledbalgtu]

建个符合index:
create index i_test on TRLPZR(YDT, LPCD ,ZAISU );

试试

[Quote=引用 1 楼 betagain 的回复:]
上面的有字符的错误，我重新发一下

TRIM(A.SHHNCD || '･' || A.SHHNNM) AS SHH,
DECODE(A.ZKSBMK,'0','A','2','B','9','C') ZKSBMK,
ROUND(C.HZAISU/DECODE(A.IRISU,'','1','0','1',A.IRISU),0) AS HZAISU,
ROUND(D.NZAISU/DECODE(A.IRISU,'','1','0','1',A.IRISU),0) AS NZAISU,
DECODE(TRIM(E.TANIKB), '','',TRIM(E.TANIKB || '･' || E.TNIKNJNM)) AS DAN,
(ROUND(C.HZAISU/DECODE(A.IRISU,'','1',…
[/Quote]

[betagain]

谢谢你！直接把这句话写在前面就行吗？我尝试一下~

[betagain]

我的sql是写在.net里的，那这句话怎么添加呢

[sleepzzzzz]

index或从业务逻辑角度进行优化。

[betagain]

我刚才看了一下，这个表原来已经建立了这样的index


能帮忙改写sql吗