Re: Query taking too long - please help

The way you describe it -

I believe that the optimiser is always allowed to fold a correlated subquery into a non-correlated sub-query and vice versa.

I suspect that in your original run, the stats on the visit table were such that the optimiser change your

• (select correlated)

into

    in (select non-correlated)

A suggestion, by the way: I would guess that your query is going to:

        tablescan visit
        join to patient
        outer join to patient kin
        outer join to patient kin
        join / subquery to visit.

Two option:

1. Use the PUSH_SUBQ hint to make the correlated subquery execute at the earliest possible moment or
2. rewrite the query with a non-correlated: query join visit v1 patient p patientkin pk1 patientkin pk2 where (v1.acctid, v1.source, v1.updatedate) in ( select v2.acctid, v2.source, max(v2.updatedate) from visit v2 group by v2.acctid, v2.source ) or
3. As for (b) but make the subquery an in-line view, and join it to visit.

Do review the execution plan, though, and see if it makes sense.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Lisa Lewis wrote in message <79f3fk$ri3$1_at_usenet01.srv.cis.pitt.edu>...
>Hi again,
>
>An update: I created a temporary table in place of the correlated subquery
>and the query now takes about 7 hours instead of 7 days to complete.
>However, I am still a little confused because like I said in my original
>post I had ran the query with the correlated subquery the other day in 5
>hours. I suspect that it has something to do with the way the optimizer
>chose the execution path ( of course, what else could it be). By the way I
>have CHOOSE as my optimizer setting and I'm not sure if all of my tables
>were analyzed when I got the good performance of 5 hours out of that query.
>Any insights would be greatly appreciated!!
>Lisa
>
Received on Fri Feb 05 1999 - 09:57:46 CST