Re: ridiculously time-consuming subqueries

From: Kim Berg Hansen <kibeha_at_gmail.com>
Date: Fri, 14 Mar 2014 08:49:52 +0100
Message-ID: <CA+S=qd0bEDv6dc35Yh2gUiHfvdu4=Wp9=RrfrLAQXMn3N7LBTQ_at_mail.gmail.com>

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.*
>

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

Received on Fri Mar 14 2014 - 08:49:52 CET