Re: Slow Query with ROWNUM

Hi Amit,

Could you provide all statistics of the table XXPO01T_ASN_INBOUND (all indexes, columns, histograms)?
Firstly, I'd check if DOCUMENT_NUM is really varchar2 (from the Binds section we can see that :B1 is varchar2). Then, I'd check if there is an index on it. Obviously, it would be better to have an index on this column, as we see that out of 826,000 rows fetched by predicates on the INVOICE_NUM and FILE_ID from the table by the time of the RTSM report execution, there wasn't a single row satisfying the predicate on "XAI"."DOCUMENT_NUM"=:B1.

On Thu, Oct 19, 2023 at 1:26 AM Amit Saroha <eramitsaroha_at_gmail.com> wrote:

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

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

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