Re: Slow Query with ROWNUM

Thank you for your email. I will give it a try and share the feedback with yo.

On Wed, Oct 18, 2023 at 7:34 PM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On 10/18/23 11:43, Amit Saroha wrote:
>
> Hi All,
>
> I want to shorten the response time to my little query, which typically
> takes 10 minutes, to only one minute.
>
> I kindly ask you to have a look and provide your insightful input in order
> to decrease the questioning time.
>
> Let me know if you need more information apart from the SQL monitoring
> report.
>
> Best Regards,
> AMIT
>
> Is it 12c or newer DB? If it is, you can try the following:
> SELECT
> DECODE(FLV.MEANING,'ORDERED_DATE',TRUNC(PHA.CREATION_DATE),'SHIPPED_DATE',TRUNC(XAI.SHIPPED_DATE),'INVOICE_DATE',TRUNC(TO_DATE(XAA.ATTRIBUTE1)))
> FROM FND_LOOKUP_VALUES FLV,PO_HEADERS_ALL PHA,XXPO01T_ASN_INBOUND
> XAI,XXPO01T_ASN_AG_LAYOUT XAA WHERE 1 = 1 AND PHA.ATTRIBUTE1 =
> XAI.DOCUMENT_NUM AND XAI.FILE_ID = XAA.FILE_ID AND XAI.INVOICE_NUM =
> XAA.INVOICE_NUMBER AND XAI.DOCUMENT_NUM = :B1 AND FLV.LOOKUP_TYPE =
> 'XXPO_ASN_CONV_DATE_CONTROL' AND NVL(FLV.TAG,'Y') = 'Y' AND FLV.LANGUAGE =
> 'US' AND FLV.ENABLED_FLAG = 'Y' AND SYSDATE BETWEEN
> NVL(TRUNC(FLV.START_DATE_ACTIVE),SYSDATE) AND
> NVL(TRUNC(FLV.END_DATE_ACTIVE),SYSDATE) FETCH FIRST 1 ROWS ONLY;
>
> Under the hood, that will use analytic functions and may prove a tad
> faster than ROWNUM. Not much, though.
>
> Regards
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

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