3,491
社区成员
发帖
与我相关
我的任务
分享select 語法
我有一個select 語法如下，
查詢的速度相當慢，
table prd20資料約一萬多筆，
table sto14資料約一千萬筆，
請問我的語法有問題嗎？
還是我該如何修改能加快查詢速度？感謝！
select prd2001,prd2002
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'A'
group by prd2001,prd2002) ,0) A
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'B'
group by prd2001,prd2002) ,0) B
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'C'
group by prd2001,prd2002) ,0) C
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'D'
group by prd2001,prd2002) ,0) D
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'E'
group by prd2001,prd2002) ,0) E
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'F'
group by prd2001,prd2002) ,0) F
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'G'
group by prd2001,prd2002) ,0) G
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'H'
group by prd2001,prd2002) ,0) H
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'I'
group by prd2001,prd2002) ,0) I
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'J'
group by prd2001,prd2002) ,0) J
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'K'
group by prd2001,prd2002) ,0) K
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'L'
group by prd2001,prd2002) ,0) L
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'M'
group by prd2001,prd2002) ,0) M
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'U'
group by prd2001,prd2002) ,0) U
from prd20
where prd2006='12'
and prd2007 = '3'
and prd2008 = '1'
order by prd2001,prd2002;
查詢的速度相當慢，
table prd20資料約一萬多筆，
table sto14資料約一千萬筆，
請問我的語法有問題嗎？
還是我該如何修改能加快查詢速度？感謝！
select prd2001,prd2002
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'A'
group by prd2001,prd2002) ,0) A
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'B'
group by prd2001,prd2002) ,0) B
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'C'
group by prd2001,prd2002) ,0) C
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'D'
group by prd2001,prd2002) ,0) D
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'E'
group by prd2001,prd2002) ,0) E
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'F'
group by prd2001,prd2002) ,0) F
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'G'
group by prd2001,prd2002) ,0) G
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'H'
group by prd2001,prd2002) ,0) H
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'I'
group by prd2001,prd2002) ,0) I
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'J'
group by prd2001,prd2002) ,0) J
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'K'
group by prd2001,prd2002) ,0) K
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'L'
group by prd2001,prd2002) ,0) L
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'M'
group by prd2001,prd2002) ,0) M
,nvl((select sum(sto1410)
from sto14
where sto1404 = prd2001
and sto1405 = prd2002
and sto1406 = 'U'
group by prd2001,prd2002) ,0) U
from prd20
where prd2006='12'
and prd2007 = '3'
and prd2008 = '1'
order by prd2001,prd2002;
...全文
请发表友善的回复…
发表回复
linwaterbin 2013-01-07
- 打赏
- 举报
如果经常用到这个查询，排查了索引等之外，而且是8i，
可以考虑做个snapshot，和物化视图差不多
lss580711 2013-01-07
- 打赏
- 举报
google了一下，說是8i不能執行 LEFT JOIN，
忘了說明，我的oracle是8i版本，
那我該用什麼方法使查詢更快一些？
lss580711 2013-01-07
- 打赏
- 举报
謝謝你的回覆！
出現以下錯誤：
SQL> select T1.prd2001,
2 T1.prd2002,
3 SUM(DECODE(T2.sto1406,'A',NVL(T2.sto1410,0))) A,
4 SUM(DECODE(T2.sto1406,'B',NVL(T2.sto1410,0))) B,
5 SUM(DECODE(T2.sto1406,'C',NVL(T2.sto1410,0))) C,
6 SUM(DECODE(T2.sto1406,'D',NVL(T2.sto1410,0))) D,
7 SUM(DECODE(T2.sto1406,'E',NVL(T2.sto1410,0))) E,
8 SUM(DECODE(T2.sto1406,'F',NVL(T2.sto1410,0))) F,
9 SUM(DECODE(T2.sto1406,'G',NVL(T2.sto1410,0))) G,
10 SUM(DECODE(T2.sto1406,'H',NVL(T2.sto1410,0))) H,
11 SUM(DECODE(T2.sto1406,'I',NVL(T2.sto1410,0))) I,
12 SUM(DECODE(T2.sto1406,'J',NVL(T2.sto1410,0))) J,
13 SUM(DECODE(T2.sto1406,'K',NVL(T2.sto1410,0))) K,
14 SUM(DECODE(T2.sto1406,'L',NVL(T2.sto1410,0))) L,
15 SUM(DECODE(T2.sto1406,'M',NVL(T2.sto1410,0))) M,
16 SUM(DECODE(T2.sto1406,'U',NVL(T2.sto1410,0))) U
17 from prd20 T1 LEFT JOIN sto14 T2
18 ON T1.sto1406 IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','U')
19 AND T1.prd2001 = T2.sto1404 AND T1.prd2002 = T2.sto1405
20 where T1.prd2006 = '12'
21 and T1.prd2007 = '3'
22 and T1.prd2008 = '1'
23 order by T1.prd2001, T1.prd2002 ;
from prd20 T1 LEFT JOIN sto14 T2
*
ERROR 在行 17:
ORA-00933: SQL 命令的結束有問題
小海葵1 2013-01-07
- 打赏
- 举报
nvl套错地方了。你换一下。
nvl（T2.sto1410）
小海葵1 2013-01-07
- 打赏
- 举报
大致改了改。你看看。
select T1.prd2001,
T1.prd2002,
SUM(DECODE(NVL(T2.sto1406,0),'A',T2.sto1410))) A,
SUM(DECODE(NVL(T2.sto1406,0),'B',T2.sto1410))) B,
SUM(DECODE(NVL(T2.sto1406,0),'C',T2.sto1410))) C,
SUM(DECODE(NVL(T2.sto1406,0),'D',T2.sto1410))) D,
SUM(DECODE(NVL(T2.sto1406,0),'E',T2.sto1410))) E,
SUM(DECODE(NVL(T2.sto1406,0),'F',T2.sto1410))) F,
SUM(DECODE(NVL(T2.sto1406,0),'G',T2.sto1410))) G,
SUM(DECODE(NVL(T2.sto1406,0),'H',T2.sto1410))) H,
SUM(DECODE(NVL(T2.sto1406,0),'I',T2.sto1410))) I,
SUM(DECODE(NVL(T2.sto1406,0),'J',T2.sto1410))) J,
SUM(DECODE(NVL(T2.sto1406,0),'K',T2.sto1410))) K,
SUM(DECODE(NVL(T2.sto1406,0),'L',T2.sto1410))) L,
SUM(DECODE(NVL(T2.sto1406,0),'M',T2.sto1410))) M,
SUM(DECODE(NVL(T2.sto1406,0),'U',T2.sto1410))) U
from prd20 T1 LEFT JOIN sto14 T2
ON T1.sto1406 IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','U')
AND T1.prd2001 = T2.sto1404 AND T1.prd2002 = T2.sto1405
where T1.prd2006 = '12'
and T1.prd2007 = '3'
and T1.prd2008 = '1'
order by T1.prd2001, T1.prd2002
