Re: SQL Performance

I have created the index but no improvement in run time.

Please let me know if I can try anything else?

Best Regards,
AMIT On Tue, Mar 12, 2024 at 12:20 PM Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> 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