我有一個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;
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
出現以下錯誤:
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 命令的結束有問題
忘了說明,我的oracle是8i版本,
那我該用什麼方法使查詢更快一些?
可以考虑做个snapshot,和物化视图差不多