Witam, mam taki problem, robie dosc zlozone zapytanie i w cześci WHERE musialem zamienic AND na OR. Z AND zapytanie wykonywalo sie kilka minut, natomiast z OR ten czas to ponad 2h. W jaki sposob zoptymalizowac zapytanie, zeby wykonalo sie rowniez w pare minut (danych jest bardzo duzo)? Prosze o wszelkie pomysly jak to zrobic.
select distinct e.umowa,b.statum,b.wartof,a.stid,a.statst,a.grupa,a.wartosc,
coalesce(pd.sumpd, 0) as sumpd, coalesce(npd.sumnpd, 0) as sumnpd, coalesce(inne.sumin, 0) as sumin ,
(a.wartosc - coalesce(pd.sumpd, 0) - coalesce(npd.sumnpd, 0) - coalesce(inne.sumin, 0)) as roznica
from ((((((DROLEASEF2.PLST01PF a inner join DROLEASEF2.pled02pf b on a.umowa = b.umowa)
inner join DROLEASEF2.plst60pf c on a.stid = c.stid)
inner join DROLEASEF2.plfk09pf e on e.umowa = b.umowa)
inner join DROLEASEF2.plfk08pf f on e.idoc = f.docnbr and
e.ipoz = f.poznbr)
left join
(select e.umowa,b.statum,b.wartof,a.stid,a.statst,a.grupa,a.wartosc,
sum(f.winien - f.ma) as sumpd
from ((((DROLEASEF2.plst01pf a inner join DROLEASEF2.pled02pf b on a.umowa = b.umowa)
inner join DROLEASEF2.plst60pf c on a.stid = c.stid)
inner join DROLEASEF2.plfk09pf e on e.umowa = b.umowa)
inner join DROLEASEF2.plfk08pf f on e.idoc = f.docnbr and
e.ipoz = f.poznbr)
where e.invid < 0 and f.acc1 = '03081' and
exists(select g.stid, g.invid from DROLEASEF2.plst31pf g where e.invid = g.invid and
g.stid = a.stid and g.invid <0)
group by e.umowa,b.statum,b.wartof,a.stid,a.statst, a.grupa,a.wartosc) pd
on pd.umowa = e.umowa and pd.stid = a.stid)
left join
(select e.umowa,b.statum,b.wartof,a.stid,a.statst,a.grupa,a.wartosc,
sum(f.winien - f.ma) as sumnpd
from ((((DROLEASEF2.plst01pf a inner join DROLEASEF2.pled02pf b on a.umowa = b.umowa)
inner join DROLEASEF2.plst60pf c on a.stid = c.stid)
inner join DROLEASEF2.plfk09pf e on b.umowa = e.umowa)
inner join DROLEASEF2.plfk08pf f on e.idoc = f.docnbr and
e.ipoz = f.poznbr)
where f.acc1 = '03081' and e.invid < 0 and not exists (select g.stid, g.invid from DROLEASEF2.plst31pf g where e.invid =
g.invid and g.stid = a.stid and g.invid <0)
group by e.umowa,b.statum,b.wartof,a.stid,a.statst, a.grupa,a.wartosc) npd
on npd.umowa = e.umowa and npd.stid = a.stid)
left join
(select e.umowa,b.statum,b.wartof,a.stid,a.statst,a.grupa,a.wartosc,
sum(f.winien - f.ma) as sumin
from (((((DROLEASEF2.plst01pf a inner join DROLEASEF2.pled02pf b on a.umowa = b.umowa)
inner join DROLEASEF2.plst60pf c on a.stid = c.stid)
inner join DROLEASEF2.plfk09pf e on b.umowa = e.umowa)
inner join DROLEASEF2.plfk08pf f on e.idoc = f.docnbr and
e.ipoz = f.poznbr)
inner join DROLEASEF2.plfk07pf g on g.docnbr = f.docnbr and
g.poznbr = f.poznbr)
where f.acc1 = '03081' and g.zaptyp not in (-1, -3, -4) and e.invid < 0
group by e.umowa,b.statum,b.wartof,a.stid,a.statst,
a.grupa,a.wartosc) inne
on inne.umowa = e.umowa and npd.stid = a.stid
where ((npd.sumnpd <> 0) or (a.wartosc - coalesce(pd.sumpd, 0) - coalesce(npd.sumnpd, 0) - coalesce(inne.sumin, 0)) <> 0)
and a.protdt between 1101111 and 1141111
order by e.umowa, a.stid;