Re: Query taking too long - 2

FYI update:

I was able to get my execution time down to 30 minutes. I used this query taken directly from Jonathan Lewis's post. Thanks again for everyones help. I learned alot on this one!

SQL> EXPLAIN PLAN

  2          SET STATEMENT_ID='lisa1' for
  3      SELECT p.mrn, p.source, p.lname, p.fname, p.mname, p.dob, p.sex,
p.race,
  4             p.addr1, p.addr2, p.city, p.state, p.zip, p.country,
p.hphone,
  5             p.bphone, p.acctid, p.ssn, v.domain,
  6             nr.lname, nr.fname, nr.addr1, nr.addr2,
  7             nr.city, nr.state, nr.zip, nr.country, nr.hphone,
  8             ec.lname, ec.fname, ec.addr1, ec.addr2,
  9             ec.city, ec.state, ec.zip, ec.country, ec.hphone
 10      FROM Mars.patient p, Mars.patientnkin nr, Mars.Visit v,
 11           Mars.patientnkin ec,
 12           ( SELECT v2.acctid, v2.source, MAX(v2.updatedate) AS
updatedate
 13             FROM Mars.Visit v2
 14             GROUP BY v2.acctid, v2.source ) v3
 15      WHERE p.acctid = nr.acctid (+)
 16      AND   p.source = nr.source (+)
 17      AND   nr.contactrole (+) = 'NR'
 18      AND   p.acctid = ec.acctid (+)
 19      AND   p.source = ec.source (+)
 20      AND   ec.contactrole (+) = 'EC'
 21      AND   p.acctid = v.acctid
 22      AND   p.source = v.source
 23      AND   v.acctid = v3.acctid
 24      AND   v.source = v3.source
 25      AND   v.updatedate = v3.updatedate;

Explained.

SQL> SELECT LPAD(' ',2*LEVEL)||OPERATION||' '||OPTIONS||' '||OBJECT_NAME||' '||cost||' '||cardinality
  2 FROM PLAN_TABLE WHERE STATEMENT_ID='lisa1'   3 CONNECT BY PRIOR ID = PARENT_ID
  4 and STATEMENT_ID = 'lisa1'   5 START WITH ID = 1
  6 and STATEMENT_ID = 'lisa1'   7 ORDER BY ID; LPAD('',2*LEVEL)||OPERATION||''||OPTIONS||''||OBJECT_NAME||''||COST||''||CAR DINA

      NESTED LOOPS   161699 7023
        MERGE JOIN   133609 14045
          SORT JOIN  60833 1986324
            TABLE ACCESS FULL VISIT 7715 1986324
          SORT JOIN  72776 642653
            VIEW   56499 642653
              SORT GROUP BY  56499 642653
                TABLE ACCESS FULL VISIT 7715 1986324
        TABLE ACCESS BY INDEX ROWID PATIENT 2 455567
          INDEX UNIQUE SCAN PATIENT_PK_ACCTID 1 455567
      TABLE ACCESS FULL PATIENTNKIN 816 105151
    TABLE ACCESS BY INDEX ROWID PATIENTNKIN 2 105151
      INDEX UNIQUE SCAN PATIENTNKIN_PK 1 105151

Lisa Lewis wrote in message <79epvu$qnb$1_at_usenet01.srv.cis.pitt.edu>...
>Hi all,
>
>I am hoping someone can give me suggestions on how to get this query to run
>faster. This query is part of a pro*c program.
>
> EXEC SQL DECLARE c_adtdemo CURSOR FOR
> SELECT p.mrn, p.source, p.lname, p.fname, p.mname, p.dob, p.sex,
p.race,
> p.addr1, p.addr2, p.city, p.state, p.zip, p.country, p.hphone,
> p.bphone, p.acctid, p.ssn, v.domain,
> nr.lname, nr.fname, nr.addr1, nr.addr2,
> nr.city, nr.state, nr.zip, nr.country, nr.hphone,
> ec.lname, ec.fname, ec.addr1, ec.addr2,
> ec.city, ec.state, ec.zip, ec.country, ec.hphone
> FROM Mars.patient p, Mars.patientnkin nr, Mars.Visit v,
Mars.patientnkin
>ec
> WHERE p.acctid = nr.acctid (+)
> AND p.source = nr.source (+)
> AND nr.contactrole (+) = 'NR'
> AND p.acctid = ec.acctid (+)
> AND p.source = ec.source (+)
> AND ec.contactrole (+) = 'EC'
> AND p.acctid = v.acctid
> AND p.source = v.source
> AND v.updatedate = ( SELECT MAX(v2.updatedate)
> FROM visit v2
> WHERE v2.acctid = v.acctid
> AND v2.source = v.source );
>
>Note: the purpose of this query is to extract patient demographics along
>with data about the patients latest visit and some next of kin information.
>A little background: The patient table has about 500,000 rows. The visit
>table has about 2,000,000 rows and the patientnkin has about 450,000 rows.
>The pk for all of the tables has acctid and source as the first two
elements
>of the pk. There is no indexes defined on ec.contactrole. Note: This is
a
>decision support system with no users currently so there is no activity
>going on that should slow this down.
>
>I am obviously most concerned about the correlated subquery. What is
>strange is that the other day I ran this query against the tables described
>above and it took about 5 hours and produced about 460,000 rows. The next
>day I ran this query, based on the progress it was making, estimations
would
>put it at about 7 days to complete. The only thing I did between the two
>runs is to re-analyze some tables so that the statistics could be most up
to
>date. ( I thought that this would help but it seemed to have the opposite
>effect). I am now in the process of deleting some of my statistics to see
>what the impact of that would be. I know that I can create a temporary
>table to instead of using the correlated subquery, I will try that as a
>last resort. The strange thing is that this query did once run to
>completion in 5 hours.
>
>Please respond via e-mail.
>I would appreciate all help!!!
>
>Thanks
>Lisa
>
>
Received on Mon Feb 08 1999 - 16:25:34 CST