优化sql语句

Question

SELECT  
rdo.pk_invbasdoc,
--+ groupStr
max(inv.invname) as invname,--max(wh.storname) as whname, 
SUM (nvl(ntermbeginnum1,0.0)) beginnum,     
SUM (nvl(nterminnum1,0.0)) innum1,  
SUM (nvl(nterminnum2,0.0)) innum2,  
SUM (nvl(ntermoutnum1,0.0)) outnum1,   
SUM (nvl(ntermoutnum2,0.0)) outnum2,   
SUM (nvl(ntermbeginnum1,0.0) +  nvl(nterminnum1,0.0) + nvl(nterminnum2,0.0)- nvl(ntermoutnum1,0.0)- nvl(ntermoutnum2,0.0)) ntermonhandnum  
FROM 
(  
      SELECT  pk_invbasdoc,pk_stordoc,
      SUM  (nvl(ninnum,0.0)-nvl(noutnum,0.0))  ntermbeginnum1 ,   
      0.0  nterminnum1 ,   
      0.0  nterminnum2 ,  
      0.0  ntermoutnum1 , 
      0.0  ntermoutnum2 
      from view_imdetail   
      where 1=1 
      and view_imdetail.corp_id='DE031420-49A8-11DD-A6A6-83873E2C5FE2'
      and  view_imdetail.billdate < '2005-01-01'
      and view_imdetail.pk_stordoc='1001AA10000000000JWD'
      
      group by pk_invbasdoc,pk_stordoc 
      UNION  ALL  (   
      SELECT  pk_invbasdoc,pk_stordoc,
      0.0  ntermbeginnum1 ,    
      SUM  (ninnum)  nterminnum1 ,   
      0.0  nterminnum2 ,  
      0.0  ntermoutnum1 , 
      0.0  ntermoutnum2 
      from view_imdetail   
      where (nvl(ninnum,0.0)>0)   
      and view_imdetail.corp_id='DE031420-49A8-11DD-A6A6-83873E2C5FE2'
      and  view_imdetail.billdate >= '2005-01-01'
      and  view_imdetail.billdate <= '2008-01-01'
      and view_imdetail.pk_stordoc='1001AA10000000000JWD'
      
      group by pk_invbasdoc,pk_stordoc
      HAVING  (SUM(ninnum) IS NOT NULL))   
      UNION  ALL  (   
      SELECT  pk_invbasdoc,pk_stordoc,  
      0.0  ntermbeginnum1 ,    
      0.0  nterminnum1 ,  
      SUM (ninnum) nterminnum2  ,  
      0.0  ntermoutnum1 , 
      0.0  ntermoutnum2 
      from view_imdetail   
      where (nvl(ninnum,0.0)<0) 
      and view_imdetail.corp_id='DE031420-49A8-11DD-A6A6-83873E2C5FE2'
      and  view_imdetail.billdate >= '2005-01-01'
      and  view_imdetail.billdate <= '2008-01-01'
      and view_imdetail.pk_stordoc='1001AA10000000000JWD'
      
      group by pk_invbasdoc,pk_stordoc   
      HAVING  (SUM(ninnum) IS NOT NULL))   
      UNION  ALL  (   
      SELECT  pk_invbasdoc,pk_stordoc,  
      0.0  ntermbeginnum1 ,    
      0.0  nterminnum1 ,   
      0.0  nterminnum2 ,  
      SUM  (noutnum)  ntermoutnum1 , 
      0.0  ntermoutnum2 
      from view_imdetail   
      where nvl(noutnum,0.0)>0  
      and view_imdetail.corp_id='DE031420-49A8-11DD-A6A6-83873E2C5FE2'
      and  view_imdetail.billdate >= '2005-01-01'
      and  view_imdetail.billdate <= '2008-01-01'
      and view_imdetail.pk_stordoc='1001AA10000000000JWD'
      
      group by pk_invbasdoc,pk_stordoc
      HAVING  (SUM(noutnum) IS NOT NULL))   
      UNION  ALL  (   
      SELECT  pk_invbasdoc,pk_stordoc,  
      0.0  ntermbeginnum1 ,    
      0.0  nterminnum1 ,  
      0.0  nterminnum2 , 
      0.0  ntermoutnum1 , 
      SUM (noutnum) ntermoutnum2  
      from view_imdetail   
      where (nvl(noutnum,0.0)<0) 
      and view_imdetail.corp_id='DE031420-49A8-11DD-A6A6-83873E2C5FE2'
      and  view_imdetail.billdate >= '2005-01-01'
      and  view_imdetail.billdate <= '2008-01-01'
      and view_imdetail.pk_stordoc='1001AA10000000000JWD'
      
      group by pk_invbasdoc,pk_stordoc  
      HAVING  (SUM(noutnum) IS NOT NULL))         
)  rdo 
join bd_invbasdoc inv on (inv.pk_invbasdoc=rdo.pk_invbasdoc  
)
join bd_invcl invcl on ( inv.pk_invcl = invcl.pk_invcl  )  
join bd_stordoc wh on (wh.pk_stordoc=rdo.pk_stordoc

)
GROUP  BY  
rdo.pk_invbasdoc,
--+ groupStr
invcl.invclasscode, inv.invcode, inv.invname  
order by invcl.invclasscode, inv.invname

Answer 1

你这个语句那么长，我们又不知道你要实现什么。起码要简单描述一下啊。建议你用oracle分析器分析一下。

Answer 2

这么长的sql语句,到底想干什么用的?建议你把它改为存储过程,把它分为小问题解决,这么长的代码又没有什么业务逻辑指导,看都头痛了

Answer 3

你的表定义没有提供(比如说主外键定义,索引字段等等),所以没有给你做太多优化,下面给出了一个基本上不改变你的逻辑的等效的简化过的SQL,从逻辑操作上,减少了在一个中间数据集上的多次扫描和聚合操作,所以从优化角度来说,肯定比原始的SQL要更优化,当然是不是最优的结果那就需要你提供其它线索了.
SELECT  
rdo.pk_invbasdoc, 
inv.invname,
SUM (nvl(ntermbeginnum1,0.0)) beginnum,    
SUM (nvl(nterminnum1,0.0)) innum1,  
SUM (nvl(nterminnum2,0.0)) innum2,  
SUM (nvl(ntermoutnum1,0.0)) outnum1,  
SUM (nvl(ntermoutnum2,0.0)) outnum2,  
SUM (nvl(ntermbeginnum1,0.0) 
    + nvl(nterminnum1,0.0) 
    + nvl(nterminnum2,0.0)
    - nvl(ntermoutnum1,0.0)
    - nvl(ntermoutnum2,0.0)) ntermonhandnum  
FROM 
(  
      SELECT  pk_invbasdoc,
              pk_stordoc, 
              SUM (CASE WHEN billdate<'2005-01-01' THEN nvl(ninnum,0.0)-nvl(noutnum,0.0) END) ntermbeginnum1,  
              SUM (CASE WHEN billdate>='2005-01-01' 
                         AND billdate<='2008-01-01' 
                         AND ninnum>0 THEN ninnum END) nterminnum1,  
              SUM (CASE WHEN billdate>='2005-01-01' 
                         AND billdate<='2008-01-01' 
                         AND ninnum<0 THEN ninnum END) nterminnum2,  
              SUM (CASE WHEN billdate>='2005-01-01' 
                         AND billdate<='2008-01-01' 
                         AND noutnum>0 THEN noutnum END) ntermoutnum1,  
              SUM (CASE WHEN billdate>='2005-01-01' 
                         AND billdate<='2008-01-01' 
                         AND noutnum<0 THEN noutnum END) ntermoutnum2
      FROM view_imdetail  
      WHERE corp_id='DE031420-49A8-11DD-A6A6-83873E2C5FE2' 
        AND pk_stordoc='1001AA10000000000JWD' 
      GROUP BY pk_invbasdoc,pk_stordoc 
)  rdo 
JOIN bd_invbasdoc inv ON inv.pk_invbasdoc=rdo.pk_invbasdoc 
JOIN bd_invcl invcl ON inv.pk_invcl = invcl.pk_invcl  
JOIN bd_stordoc wh ON wh.pk_stordoc=rdo.pk_stordoc 
GROUP BY invcl.invclasscode,inv.invcode,inv.invname,rdo.pk_invbasdoc
ORDER BY invcl.invclasscode,inv.invname