Re: Inputs Needed to Optimizing a SQL Query

Thank you, Lothar.

Thank you, Lothar for your input. These are very helpful. I have the questions below before creating the index on the table -

The column export flag doesn't have any rows; it is all NULL. Please advise if it is a good idea to have the index on this column. The present indexes on the table are shown below. Do you think we can use any of these instead of creating a new one? Also, does it make sense to change the NL to HJ while joining line 64 and 66?

[image: image.png]

Best Regards,
AMIT On Mon, Aug 12, 2024 at 11:22 AM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> Hi Amit,
>
> Time is spend in Line 66 and 67 almost exclusively.
> The result of the access on Line 67 on index MTL_CYCLE_COUNT_ENTRIES_N2
> is 11 million rows.
> The result of of the access on Line 66 using the rowids of line 67 is 6600
> rows.
> That case is clear. Index MTL_CYCLE_COUNT_ENTRIES_N2 is not ideal for
> that query.
> You need to construct an new index. The conditions applied in line 66
> might help you identify the columns missing. That is: (INTERNAL_FUNCTION("MCCE"."ENTRY_STATUS_CODE")
> AND NVL("MCCE"."EXPORT_FLAG",2)=2).
>
> The new index needs likely to include the following columns:
>
> "ORGANIZATION_ID", "CYCLE_COUNT_HEADER_ID","SUBINVENTORY", NVL("MCCE"."EXPORT_FLAG",2),
> ENTRY_STATUS_CODE.
>
> I am missing some information here, therefore I can not give 100% advise
> and you might need to do some tests.
>
> Thanks
>
> Lothar
>
>
> Am 12.08.2024 um 16:58 schrieb Amit Saroha:
>
> Hi All,
>
> We are using Oracle DB version 19.2 on-premises, and the enclosed query is
> giving us trouble. Our warehouse operations cannot handle the slowness of
> the query, which takes about 20 seconds to complete. If at all feasible, I
> would appreciate your feedback on how to shorten the execution time to two
> or three seconds.
>
> Please check the SQL query and monitoring that are contained, and let us
> know if there is any way to speed up the execution time.
>
> If you require any further information, just let me know and I would be
> pleased to provide it. I appreciate your assistance and backing in advance.
>
> Best Regards,
> AMIT
>
>
>

--
http://www.freelists.org/webpage/oracle-l




image/png attachment: image.png