Re: Inputs Needed to Optimizing a SQL Query

Hi,

looks like you did not include ENTRY_STATUS_CODE in the index as I suggested.
ENTRY_STATUS_CODE is essential. It must be in the Index.

Thanks

Lothar

Am 20.08.2024 um 18:51 schrieb Amit Saroha:
> Hello Everyone,
>
> Even after I built the index and ran various tests, the query still
> takes tens of seconds to execute. Please review the monitoring report
> that is attached after the index is created and let us know if there
> is anything that can be done.
>
> Best Regards,
> AMIT
>
>
> On Mon, Aug 12, 2024 at 12:42 PM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
> 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