Re: Improve Query sqlm_9csc97utkgjub

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