Re: Inputs Needed to Optimizing a SQL Query

Hi Amit

That is what I ment when writing I am missing information. If export_flag is always null, then it is not needed in the index.

as  NL versus HJ: the step was done only 16.000 times, NL should be fine. Consider compressing the index.

Thanks

LOthar

Am 12.08.2024 um 18:10 schrieb Amit Saroha:
> 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.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