RE: ridiculously time-consuming subqueries

Doesn't that make the whole query (including the main query) a set of correlated queries ? Oracle might be passing the PATIENT_DEMO and executing the sub query repeatedly for each value.  

Look at the execution plan and execution stats (row source or a trace file).  

Hemant K Chitale    

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William Threlfall Sent: Thursday, March 13, 2014 1:52 AM
To: Oracle-L_at_freelists.org
Subject: ridiculously time-consuming subqueries  

Hi experts,  

I have a query in which I am trying to get the CMV and EBV results for the patient's last transplant.

I put in the following subquery code:  

  (SELECT RECIPIENT_CMV_IGG     FROM PAT_TRANSPLANT PT, SDF_SEROLOGY_VERIFICATION_TO SV,          DOCUMENTS DOCS, TX_DOCUMENTS TXD     WHERE PT.PAT_ID = PD.PAT_ID     AND PT.PAT_ID = DOCS.PAT_ID     AND PT.PAT_ID = LTV.PAT_ID     AND PT.TRANSPLANT_ID = TXD.TRANSPLANT_ID     AND PT.DATE_OF_TRANS = LTV.LAST_TRANS_DATE     AND DOCS.DOC_ID = TXD.DOC_ID     AND DOCS.DOC_ID = SV.DOC_ID     AND DOCS.DOC_KIND_ID = 10108   ) AS CMV,   (SELECT RECIPIENT_EBV     FROM PAT_TRANSPLANT PT, SDF_SEROLOGY_VERIFICATION_TO SV,          DOCUMENTS DOCS, TX_DOCUMENTS TXD     WHERE PT.PAT_ID = PD.PAT_ID     AND PT.PAT_ID = DOCS.PAT_ID     AND PT.PAT_ID = LTV.PAT_ID     AND PT.TRANSPLANT_ID = TXD.TRANSPLANT_ID     AND PT.DATE_OF_TRANS = LTV.LAST_TRANS_DATE     AND DOCS.DOC_ID = TXD.DOC_ID     AND DOCS.DOC_ID = SV.DOC_ID     AND DOCS.DOC_KIND_ID = 10108   ) AS EBV   In the above code, PD = PATIENT_DEMO and LTV = LAST_TRANSPLANT_VIEW, which are in the FROM clause in the main query.  

When I comment out these subqueries, the rest of the main query executes in less than 5 minutes, but with these subqueries in I had to stop the query because it was running for over an hour and still wasn't finished yet.  

Can anyone see any obvious explanation for why these subqueries are taking so long to execute?

Can anyone suggest a better way to get the CMV and EBV results for the last transplant?  

I only want one CMV and one EBV result (i.e. one value for each, in one row) returned per patient. I am not certain if there are multiple 10108 documents attached to the last transplant for any patients - that is possible. In that case I want the result to be "POSITIVE" if any of them are POSITIVE, otherwise "NEGATIVE".  

Thanks, - Bill.    

Willliam J. (Bill) Threlfall, MSc

Clinical Informatics Coordinator - OTTR

Transplant Services, University of Alberta Hospital

Alberta Health Services

Aberhart Centre, Room 9221

8440 112 Street

Edmonton, AB T6G 2B7  

Telephone: 780-407-6175 FAX: 780-407-8981  

Alberta Health Services

www.albertahealhservices.ca  

This message, and any documents attached hereto, is intended only for the addressee and may contain privileged or confidential information. any unauthorized disclosure is strictly prohibited. If you have received this message in error, please notify us immediately so that we may correct our internal records. Please then delete the original message. Thank you.        

This message and any attached documents are only for the use of the intended recipient(s), are confidential and may contain privileged information. Any unauthorized review, use, retransmission, or other disclosure is strictly prohibited. If you have received this message in error, please notify the sender immediately, and then delete the original message. Thank you.

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html.

--
http://www.freelists.org/webpage/oracle-l