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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxxx> 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
