Re: SQL Performance

Thank you for your feedback, I will create the index and test. Could you also advise what do you mean by re-write?

Best Regards,
AMIT On Tue, Mar 12, 2024 at 11:13 AM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> It should be possible to rewrite the statement avoiding that column level
> selects.
> Am 12.03.2024 um 16:09 schrieb Lothar Flatz:
>
> 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