I have the following query, which is takling couple of minutes to
return 500+ rows!
I have all the proper indexes and statistics is upto date.
I correlation after the OR clause is the culprit. I have tried a few
ways to get the sum and then join with the table pr. But am not
getting the exact result.
Can someone show me the way to write this query without using the
correlation? My brain is blanked out!
select recordno, (select sum(amount)
from pmt
where cnyno = 1234 and
pmtdt <= sysdate and
state = 'X' and
(pmt.key = pr.recordno
or
pmt.key in
(select recordno from pr cr
where cr.cnyno = pr.cnyno and
cr.parentrec = pr.recordno
)
)
) tot
from pr
where pr.cnyno = 1234 and
pr.created <= sysdate
thanks!
