Re: SQL Performance

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