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

From: Prince Kumar <gspk_at_yahoo.com>
Date: 23 Sep 2003 14:22:51 -0700
Message-ID: <629275ba.0309231322.133a94c2_at_posting.google.com>

To answer your question:

1- Version?               9.2.0.3
2- Execution plan?        I will have it attached later. At this time

I am looking for how to modify "OR" part of the query without changing the semantics.
3- What do you mean by "correlation"?

                         The table in subquery referring back to the

table outside the subquery.

thanks,

danielroy10junk_at_hotmail.com (Daniel Roy) wrote in message news:<3722db.0309230605.61216fc8_at_posting.google.com>...
> Questions:
> 1- Version?
> 2- Execution plan?
> 3- What do you mean by "correlation"?
>
> Daniel
>
> > 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 - 23:22:51 CEST