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
