, так потому и смотрю на стоимость, что время выполнение выросло очень сильно, было 1 час, сейчас уже больше 9-ти часов считается.
Код:
INSERT INTO TTRemains ( StoreLoc, Article, Quantity, Forced, CP_NoVat, CP_Full )( SELECT Location, Article, SUM(Quantity) Quantity, Forced, SUM(CP_NoVat) CP_NoVat, SUM(CP_Full) CP_Full
from ( SELECT /*+ ORDERED USE_NL(A) FULL(A) FULL(M.U_MapRep.FFMapRep) */ L.Id Location, M.Article, SUM(DECODE(L.Id,M.SaleLocationTo,1,M.SaleLocationFrom,-1,0)*M.Quantity) Quantity, DECODE(nvl(A.PrimeAlg,0),0,ForcedMapping,PrimeCostForced) Forced, nvl(SUM(DECODE(L.Id,M.SaleLocationTo,1,M.SaleLocationFrom,-1,0)*DECODE(nvl(A.PrimeAlg,0),0,DECODE(M.IncomeQ,0,0,M.IncomeNoVat*M.Quantity/M.IncomeQ),nvl(PrimeCostNoVAT,0))),0) CP_NoVat, nvl(SUM(DECODE(L.Id,M.SaleLocationTo,1,M.SaleLocationFrom,-1,0)*DECODE(nvl(A.PrimeAlg,0),0,DECODE(M.IncomeQ,0,0,M.IncomeSum*M.Quantity/M.IncomeQ),nvl(PrimeCost,0))),0) CP_Full
FROM FVMapRep M, SMStoreLocations L, TTStoreCfg A
WHERE L.Id in (M.SaleLocationTo, M.SaleLocationFrom) and L.Id = A.StoreLoc(+) and M.SaleDate between :i_Start and :i_Stop and M.Article in (select FData
from TTFilterStr
where FType=1)
GROUP BY L.Id, M.Article, DECODE(nvl(A.PrimeAlg,0),0,ForcedMapping,PrimeCostForced) )
group by Location, Article, Forced having ROUND(SUM(Quantity),3) <> 0 or ROUND(SUM(CP_NoVat),4) <> 0 or ROUND(SUM(CP_Full),4) <> 0 )