Re: ridiculously time-consuming subqueries

You might want to try this technique:

with cmv as (SELECT /*+ materialize */ 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
  ),
ebv as  (SELECT /*+ materialize */ 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
  )
select ...

The /*+ materialize */ hint requests Oracle to create a temporary table with the results, which may improve performance for you. 
 
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"




On Friday, March 14, 2014 9:11 AM, William Threlfall <William.Threlfall_at_albertahealthservices.ca> wrote:
 
Kim,
 
Yes, of course, that was it exactly. *sigh*
Now I’m running it again, but now it is taking forever to run again.  *sigh*
 
Do you have any suggestions as to general methodology for how I can get query results in a better way?  Any recommendations for books or online resources to better understand how to do the type of querying that I need to do on a regular basis? 
 
The requests I get almost always require one row of one-value-per-patient results, which is why I am doing them as several scalar subqueries.  
However, the results I want are almost always buried behind several layers of one-to-many table relationships (different ones for each subquery, of course), which is why the scalar subquery method is extremely inefficient, since many complex joins are involved for each patient over and over again.
I am constantly struggling to avoid that “single-row subquery returns multiple rows” error.
 
I got the book ‘Expert Oracle’ by Tom Kyte, as suggested by someone else on this list, but only the chapter on Analytic Functions is of any use to me, and even that chapter is not very in-depth.  It doesn’t even talk about the “KEEP” and “FIRST” keywords, for example, and doesn’t really explain very many of the analytic functions very well.
 
The official Oracle SQL Reference Manual is not helpful at all in understanding how to use various features, never mind how BEST to use them.
 
I am a smart guy, but Oracle is kicking my a$$ so far.  I need to find a better way to get the results I want.
 
Cheers, - Bill.
 
 
 
From:Kim Berg Hansen [mailto:kibeha_at_gmail.com] 
Sent: Friday, March 14, 2014 1:50 AM
To: William Threlfall
Cc: Oracle-L_at_freelists.org
Subject: Re: ridiculously time-consuming subqueries
 
Bill,
 
Your modified subquery I notice you have added PATIENT_DEMO PD to the FROM list.
In your original post it is not present in the subquery FROM list, so it looks like PD is supposed to be from the main query, right?
 
So your new modified subquery does not actually contain any reference to tables from the main query anymore, as far as I can tell.
It is not correlated anymore - it evaluates the max expression on all patients that have doc_kind_id = 10108 and returns that same max expression for all rows in the main query.
I think you get POSITIVE on all rows...
I think you have been a little too quick in a cut-and-paste? ;-)
 
 
On a side note - this is nonsense:
 
ORDER BY PT.DATE_OF_TRANS     DESC NULLS LAST,
                   SV.RECIPIENT_CMV_IGG DESC NULLS LAST,
                  ROWNUM ASC
 
ROWNUM is not numbering rows in a table - it is numbering the output rows in whatever order they happen to be retrieved.
In this case the output rows will be just one row - the MAX expression.
That ROWNUM is always 1 in this case and makes no sense to put in that ORDER BY expression.
 
 
 
Regards
 
 
Kim Berg Hansen
 
http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha
 
 
 
On Thu, Mar 13, 2014 at 11:16 PM, William Threlfall <William.Threlfall_at_albertahealthservices.ca> wrote:
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.
________________________________
 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-l