Re: SQL Performance

Have to correct myself, line 7 is executed 23 time, thus estimate looks ok.

Am 12.03.2024 um 16:03 schrieb Lothar Flatz:
> Hi Amit,
>
> it is both time the access on table PO_HEADERS_ALL where time is spend.
> That is in Line 7 where the selection criteria is
> "POH"."ATTRIBUTE1"=:B1 AND "POH"."ORG_ID"=:B2. As an easiest option an
> Index on (ORG_ID, ATTRIBUTE1) might be useful.
> We see that the estimates are somewhat off, as estimated was 1 row,
> whereas 23 rows were retrieved.
> An improvement of statistics might be advisable.
> On line 28, the same table, but this time selection criteria are
> ("POH"."ATTRIBUTE1"="ASN_L"."DOCUMENT_NUM" AND
> "POH"."ORG_ID"="ASN_L"."ORG_ID" AND
> "POH"."TYPE_LOOKUP_CODE"='STANDARD' AND
> NVL("POH"."CLOSED_CODE",'OPEN')='OPEN' AND
> NVL("POH"."CANCEL_FLAG",'N')='N').
>
> As the same columns as above are included and the columns proved to be
> selective in line 7 probably the same index would help.
> This , if possible create an index on PO_HEADERS_ALL (ORG_ID,
> ATTRIBUTE1).
>
> There might be some testing necessary of just one of the two columns
> is enough etc.
> However as a first attempt that might be a good startpoint.
>
> Thanks
>
> Lothar
>
> Am 12.03.2024 um 15:38 schrieb Amit Saroha:
>> Hi All,
>> I have the enclosed query running rather slowly, and I'd like to
>> enhance it such that it finishes in a few seconds. The query is a
>> SELECT statement on a view and I enclosed the view text.
>> Please review the accompanying query and monitoring report and
>> provide input for improvements.
>> I appreciate your aid and support in advance.
>>
>> Best Regards,
>> AMIT
>

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