Re: Improve Query sqlm_9csc97utkgjub

 Amit,   

If feasible & use case fits, may be you   

could explore creating a virtual index   

 and test your process (or procedures)   

 and find out if it helps & then based on   

 the results expand the visibility of   

 index to limit a potential downside of   

 creation of this index..                        

Rajeev      

>
> On Oct 23, 2024 at 1:54 PM, <Tim Gorman (mailto:tim.evdbt_at_gmail.com)> wrote:
>
>
>
>
> 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...
>
>
>
> Create an accurate copy of the table in question, including all indexing, constraints, and triggers
>
> Create a PL/SQL block to replicate the transactions into the table (i.e. INSERT, UPDATE, DELETE, MERGE, etc)
>
> Run the PL/SQL block using multiple SQL*Plus sessions, before and after creating the index in question
>
> At a minimum, compare the differences in elapsed times of the SQL*Plus sessions, before and after creating the index in question
>
>
>
> Comparing elapsed times is only the most basic comparison to understand differences. You might also want to consider compare other metrics, such as physical reads, logical reads, redo generated, etc. As you mentioned, the size of the resulting index as a result of the modifications to the column is also a concern, and should be measured and understood.
>
>
>
>
> To quote another legend, Cary Millsap frequently uses an aphorism borrowed from carpentry of "measure twice and cut once". The point being that once a decision is made, there is usually no way to reverse it. This is not a recommendation to begin an endless loop of analysis (a.k.a. "analysis paralysis"), but merely to "measure twice" rather than once, especially if it is not too difficult to measure twice. Again, in no way am I suggesting that Mr Millsap would agree with me, and I am not attempting to constrain anything he would advise.
>
>
>
>
> The simple test case described above, which couldn't possibly consume more than 8-16 hours of effort (likely far less) might end up confirming your assumptions.
>
>
>
> On the other hand, what if it doesn't?
>
>
>
> And that's the whole point.
>
>
>
>
> Hope this helps,
>
>
>
> -Tim
>
>
>
>
>
>
>
>
>
>
>
>
> 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 (mailto: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 (mailto: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 (mailto: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