如何优化这条sql语句? Oracle(100)

-- -- 计算本期入库、出库(平均价) update t_material_pbcategory pc set --本期财务关账的入库 CURINMONEYAVERAGE=(select nvl(( -- -- 本期入库 (select nvl(sum(io.MATERIALMONEYAVERAGE), 0) from t_material_inandoutdetail io inner join t_material_baseinfo i on i.materialguid=io.materialguid where io.periodofbursar = pc.periodofbursar and io.inorout = 1 and (io.billtype = 'PD' or io.billtype = 'RK') -- or io.billtype = 'FP' and i.categorycode like trim(pc.categorycode)||'%') -- -- 减去 对本期的退货(退库) - (select nvl(sum(io.MATERIALMONEYAVERAGE), 0) from t_material_inandoutdetail io inner join t_material_baseinfo i on i.materialguid=io.materialguid inner join t_material_OutStorageMaster tkm on tkm.outstoragemasterguid=io.billguid --and tkm.OutStorageType='01' inner join t_material_instoragemaster rkm on rkm.InStorageNo=tkm.InStorageNo where io.periodofbursar = pc.periodofbursar and io.inorout = 0 and (io.billtype = 'TK' and io.periodofbursar=rkm.periodofbursar) and i.categorycode like trim(pc.categorycode)||'%') -- -- 加上 对以前会计期的退料 + (select nvl(sum(io.MATERIALMONEYAVERAGE), 0) from t_material_inandoutdetail io inner join t_material_baseinfo i on i.materialguid=io.materialguid inner join t_material_ReStorageMaster tlm on tlm.restoragemasterguid=io.billguid --and tlm.ReStorageType='01' inner join t_material_ProvideMaster flm on flm.ProvideNO=tlm.ProvideNO where io.periodofbursar = pc.periodofbursar and io.inorout = 1 and (io.billtype = 'TL' and io.periodofbursar<>tlm.periodofbursar) and i.categorycode like trim(pc.categorycode)||'%') ),0) from dual), --本期财务关账的出库 CUROUTMONEYAVERAGE=(select nvl(( -- -- 本期发料 (select nvl(sum(io.MATERIALMONEYAVERAGE), 0) from t_material_inandoutdetail io inner join t_material_baseinfo i on i.materialguid=io.materialguid where io.periodofbursar = pc.periodofbursar and io.inorout = 0 and (io.billtype = 'PD' or io.billtype = 'FL') and i.categorycode like trim(pc.categorycode)||'%') -- -- 减去 对本期的退料 - (select nvl(sum(io.MATERIALMONEYAVERAGE), 0) from t_material_inandoutdetail io inner join t_material_baseinfo i on i.materialguid=io.materialguid inner join t_material_ReStorageMaster tlm on tlm.restoragemasterguid=io.billguid --and tlm.ReStorageType='01' inner join t_material_ProvideMaster flm on flm.ProvideNO=tlm.ProvideNO where io.periodofbursar = pc.periodofbursar and io.inorout = 1 and (io.billtype = 'TL' and io.periodofbursar=tlm.periodofbursar) and i.categorycode like trim(pc.categorycode)||'%') -- -- 加上 对以前会计期的退货(退库) + (select nvl(sum(io.MATERIALMONEYAVERAGE), 0) from t_material_inandoutdetail io inner join t_material_baseinfo i on i.materialguid=io.materialguid inner join t_material_OutStorageMaster tkm on tkm.outstoragemasterguid=io.billguid --and tkm.OutStorageType='01' inner join t_material_instoragemaster rkm on rkm.InStorageNo=tkm.InStorageNo where io.periodofbursar = pc.periodofbursar and io.inorout = 0 and (io.billtype = 'TK' and io.periodofbursar<>rkm.periodofbursar) and i.categorycode like trim(pc.categorycode)||'%') ),0) from dual) where pc.periodofbursar = '200901';

看的头晕。建议多加一张表，把那些要计算的内容在平时就实时更新进去，这样就可以直接update

已经由 20分 钟，缩减至 20 秒。给出其它优化思路，方法， 发分

你更新了2个字段，每个字段又是通过3个子查询计算处理的，且这三个子查询又是从不同的数据表关联得到的，想把速度提供很多，只能向二楼说的那样了。实施看下面的语句，看看是否可以加快一点速度Update t_material_pbcategory pcSet ( CurInMoneyAverage, CurOutMoneyAverage )= (Select Sum(Nvl(A1,0)), Sum(Nvl(A2,0)) From (Select Sum(Case when io.inorout = 1 and (io.billtype = 'PD' or io.billtype = 'RK') Then io.MATERIALMONEYAVERAGE else 0 End ) A1, Sum(Case when io.inorout = 0 and (io.billtype = 'PD' or io.billtype = 'FL') Then io.MATERIALMONEYAVERAGE else 0 End )A2, Count(*) as CC From t_material_inandoutdetail io inner join t_material_baseinfo i on i.materialguid=io.materialguid Where io.periodofbursar = pc.periodofbursar and i.categorycode like trim(pc.categorycode)||'%') Union All Select Sum(Case when io.inorout = 0 and io.billtype = 'TK' then -io.MATERIALMONEYAVERAGE else 0 end) A1, Sum(Case when io.inorout = 1 and io.billtype = 'TL' then -io.MATERIALMONEYAVERAGE else 0 end) A2, Count(*) as CC From t_material_inandoutdetail io inner join t_material_baseinfo i on i.materialguid=io.materialguid inner join t_material_OutStorageMaster tkm on tkm.outstoragemasterguid=io.billguid --and tkm.OutStorageType='01' inner join t_material_instoragemaster rkm on rkm.InStorageNo=tkm.InStorageNo Where io.periodofbursar = pc.periodofbursar and io.periodofbursar=rkm.periodofbursar and i.categorycode like trim(pc.categorycode)||'%') Union All Select Sum(Case when io.inorout = 1 and (io.billtype = 'TL' then io.MATERIALMONEYAVERAGE else 0 end) A1, Sum(Case when io.inorout = 0 and (io.billtype = 'TK' then io.MATERIALMONEYAVERAGE else 0 end) A2, Count(*) as CC From t_material_inandoutdetail io inner join t_material_baseinfo i on i.materialguid=io.materialguid inner join t_material_ReStorageMaster tlm on tlm.restoragemasterguid=io.billguid --and tlm.ReStorageType='01' inner join t_material_ProvideMaster flm on flm.ProvideNO=tlm.ProvideNO Where io.periodofbursar = pc.periodofbursar and io.periodofbursar<>tlm.periodofbursar) and i.categorycode like trim(pc.categorycode)||'%') )where pc.periodofbursar = '200901';

子查询中的where 条件最好写成：Where io.periodofbursar=rkm.periodofbursar and i.categorycode like trim(pc.categorycode)||'%') and io.periodofbursar = pc.periodofbursar在oracle里面，把过滤数据最有效的条件放在最后。

不错，还有其他人不

io.periodofbursar = pc.periodofbursar改为io.periodofbursar = '200901'其它也类似