Re: Improve Query sqlm_9csc97utkgjub

Hi Tim,

I completely agree but our system processes hundreds of transactions per minute across many business verticals, including sales orders, shipping, and billing, making it difficult to reproduce the test environment.

Mr. Richard Foote block also shows that it can have impact so do you have any restricted inputs to test the impact of updates done by different business processes.

https://richardfoote.wordpress.com/category/update-indexes/ *"Now with the non-unique index, this resulted in the index doubling in size as we created an additional index entry for each and every row. After the rollback, we were effectively left with an index that not only was twice the size but had only 1/2 empty leaf blocks."*

Best Regards,
AMIT On Wed, Oct 23, 2024 at 12:14 PM Tim Gorman <tim.evdbt_at_gmail.com> wrote:

> Amit,
>
> <rant>
>
> The statement "*I'm not sure it's okay to establish an index on a column
> that's often changed*" displays a void which must first be resolved to
> the best of our ability. I'm sure a simple test case can be devised to
> provide factual evidence, one way or the other?
>
> The only way to determine benefits and costs is to test it out. Nobody
> should simply guess whether something is OK or not, regardless of how many
> years they've been doing this stuff. Nothing stays the same in this field,
> so each time an unknown is encountered, it should be tested empirically.
>
> I hate to call out names, but Jonathan Lewis (who is supposed to be
> retired but is still suspiciously active!) has been working this way for
> decades, yet I have never seen him sit back and make a technical assertion
> based on those decades of experience. Instead, in each case, he creates a
> viable and reproducible test case, then blogs to state the problem, explain
> the test case, describe the results, to arrive at a hypothesis
> transparently. This invites review by peers, and validation (or
> refutation) as necessary. My apologies to Mr Lewis for using his name (and
> reputation) in this manner, but as I began to describe the method without
> mentioning his name, I felt that I was just dancing around the point. I
> certainly do not intend to speak for him, and I certainly do not wish to
> constrain any response he might have.
>
> Anyway, it's a lot of work, but that's why they pay us, and why we're on
> this list asking (and answering) questions.
>
> We're not here to keep applying the same old solutions, day after day,
> year after year, decade after decade, googled from StackOverflow, or
> bloviated from someone with more years in the field who should know
> better. We're here on this list to understand.
>
> My apologies for the rant, and my sincere apologies to Jonathan for stolen
> valor.
>
> </rant>
>
> Hope this helps?
>
> -Tim
>
> P.S. Long story short, add the index and find a way to test the impact on
> modifications as well as queries, then share the testing and results with
> your peers to decide whether adding the index is a beneficial enough for
> queries to justify the impact on modifications.
>
>
>
> On 10/23/2024 8:30 AM, Amit Saroha wrote:
>
> Hi Petr,
> Thank you for your feedback.
> The table is regularly updated. I mean, the latest update date column in a
> row changes every few minutes, so I'm not sure whether it's okay to
> establish an index on a column that's often changed.
>
> Best Regards,
> AMIT
>
>
> On Wed, Oct 23, 2024 at 11:22 AM Petr Novak <novak_petr_at_yahoo.de> wrote:
>
>> Hallo Amit,
>>
>> Table OE_ORDER_LINES_ALL - can you create index on LAST_UPDATE_DATE ?
>> Check also percentage of chained rows in this table.
>>
>> Best Regards,
>> Petr
>>
>>
>> Am Mittwoch, 23. Oktober 2024 um 17:02:56 MESZ hat Amit Saroha <
>> eramitsaroha_at_gmail.com> Folgendes geschrieben:
>>
>>
>> Hi All,
>> I am requesting that you check the included SQL monitoring data and let
>> me know if there is anything we can do to optimize the run time.
>>
>> Thank you for your assistance and advice in advance.
>>
>> Best Regards,
>> AMIT
>>
>

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