Re: SQL Performance

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