Re: SQL Performance

Hello, that index is in theory the most obvious choice and again in theory should work. Can you upload the sql monitor with the next execution plan?

În vin., 15 mar. 2024 la 22:06, Amit Saroha <eramitsaroha_at_gmail.com> a scris:

> 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