Re: how to change this query, so that it will not use the correlation?

Questions:

1- Version?
2- Execution plan?
3- What do you mean by "correlation"?

> 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!
Received on Tue Sep 23 2003 - 16:05:22 CEST