Re: Improve Query sqlm_9csc97utkgjub

Amit,

Richard's blog is undoubtedly accurate, but also undoubtedly describes a specific condition that may or may not be similar to yours.

Good advice is excellent guidance, but it is only advice, and there are many possible caveats.  It is not possible to test everything, but the decision to reject an index based on one's interpretation of a blog post is premature and incomplete.  Things change, and a blog post from years ago can be greatly impacted by different conditions, different software, different hardware, etc.  I am not suggesting Mr Foote would agree or disagree, nor am I constraining anything he stated or would advise.

Reproducing a situation involving a very busy environment is not complex...

1. Create an accurate copy of the table in question, including all indexing, constraints, and triggers
2. Create a PL/SQL block to replicate the transactions into the table (i.e. INSERT, UPDATE, DELETE, MERGE, etc)
3. Run the PL/SQL block using multiple SQL*Plus sessions, before and after creating the index in question
4. At a minimum, compare the differences in elapsed times of the SQL*Plus sessions, before and after creating the index in question

On 10/23/2024 9:56 AM, Amit Saroha wrote:
> 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