Re: Slow Query with ROWNUM

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-1217
https://dbwhisperer.wordpress.com

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