RE: ridiculously time-consuming subqueries
Date: Thu, 13 Mar 2014 16:16:22 -0600
Message-ID: <36800EC4761690448F1B444A1AEF44BB6D2EBD34_at_EXMBXC5.crha.bewell.ca>
Update for this issue:
To answer the question, it is Oracle 10g EE (10.2.0.3.0) The original explain plans were extremely complex and time-consuming.
I modified the first subquery to use an analytic function, as follows:
(SELECT MAX(SV.RECIPIENT_CMV_IGG)
KEEP (DENSE_RANK FIRST
ORDER BY PT.DATE_OF_TRANS DESC NULLS LAST,
SV.RECIPIENT_CMV_IGG DESC NULLS LAST,
ROWNUM ASC)
FROM PATIENT_DEMO PD, PAT_TRANSPLANT PT, DOCUMENTS DOCS,
TX_DOCUMENTS TXD, OTTRSDF.SDF_SEROLOGY_VERIFICATION_TO SV
WHERE PD.PAT_ID = PT.PAT_ID
AND DOCS.DOC_ID = TXD.DOC_ID
AND DOCS.DOC_ID = SV.DOC_ID
AND PT.TRANSPLANT_ID = TXD.TRANSPLANT_ID AND DOCS.DOC_KIND_ID = 10108
) AS CMV, This now runs in a reasonable amount of time. However, it is giving me incorrect results. For the last patient returned, it is returning “POSITIVE”, but for that patient there is only one transplant (so no multiples there), one document 10108 (so no multiples there), and one SV record (so no multiples there), and the only CMV result in the database for that patient in the SV table is “NEGATIVE”. I’m having trouble even imagining where the result “POSITIVE” is coming from.
Cheers, - Bill.
From: David Fitzjarrell [mailto:oratune_at_yahoo.com] Sent: Wednesday, March 12, 2014 12:25 PM To: William Threlfall; Oracle-L_at_freelists.org Subject: Re: ridiculously time-consuming subqueries
I have to ask which release of Oracle are you using? Second, does subquery factoring help any (using WITH)? You could combine those two queries into one WITH query.
To know why these are taking so long we'll need to see plans for each of the queries (I suspect the plans will be the same).
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"
On Wednesday, March 12, 2014 11:52 AM, William Threlfall <William.Threlfall_at_albertahealthservices.ca> wrote: 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.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 13 2014 - 23:16:22 CET