Re: Re: force index range scan vs index full scan through hints?

thanks Lothar and Nenad! I didn't actually stop to think about what I was asking optimizer to do with index range scan hint. i feel very lucky to have a resource like oracle-l to turn to when i get stuck. it makes learning much easier/more enjoyable.

On Mon, Aug 13, 2018 at 10:04 AM Noveljic Nenad <nenad.noveljic_at_vontobel.com> wrote:

> Oracle improved the OR-expansion transformation in 12.2 by making it cost
> based. But the costing isn’t done properly in all cases. As a consequence,
> the transformation can be bypassed when it shouldn’t.
>
>
>
> With the following workaround you can revert to the old-style heuristic
> OR expansion and see if it applies to your example:
>
>
>
> ALTER SESSION SET "_optimizer_cbqt_or_expansion"=off;
>
>
>
> There’s more information about this problem in
> http://nenadnoveljic.com/blog/cost-based-or-expansion-transformation/
>
>
>
> Another case where the OR-transformation is wrongly omitted is if you have
> a subquery as an OR operand, see
> http://nenadnoveljic.com/blog/disjunctive-subquery-optimization/ for more
> information.
>
>
>
> Best regards,
>
>
>
> Nenad
>
>
>
> http://nenadnoveljic.com/blog
>
>
>
>
>
>
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *l.flatz_at_bluewin.ch
> *Sent:* Montag, 13. August 2018 14:19
> *To:* cstephens16_at_gmail.com
> *Cc:* christopherdtaylor1994_at_gmail.com; oracle-l_at_freelists.org;
> niall.litchfield_at_gmail.com
> *Subject:* Re: Re: force index range scan vs index full scan through
> hints?
>
>
>
> Hi,
>
>
>
> i think you can hardly get one index range scan, whatever you do.
>
> Consider what that means:
>
> It would mean the optimizer has to
>
> - find the minimum start value out of all or clauses to start the
> scan.
> - Further it has to find the maximum end value out of all clauses to
> end the scan.
>
> That could be non trivial as the conditions could contain bind variables
> and complex conditions. The ranges could be overlaping etc.
>
> (I was told the MS SQLserver has some hardcoded logic that will do what
> you want if it recognizes a certain query pattern.
>
> This pattern is used for ISAM emulation and it resembles to some extend
> the or .. between conditions you are describing.)
>
>
>
> What you can get is a number of range scans, one for each or clause.
>
> In order to achive it you need "OR Expansion" to happen. (
> https://blogs.oracle.com/optimizer/optimizer-transformations:-or-expansion
> )
>
> You can trigger or expansion by the use_concat (oldfashioned) hint or the
> OR_EXPAND hint (newer and more precise).
>
> You can find a discussion here:
> https://jonathanlewis.wordpress.com/category/oracle/hints/.
>
>
>
> One issue that occured here was thaz your index hint can not be honored if
> or expansion did not happend first.
>
> However the index hint is an access path hint, which is checked after the
> transformation phase.
>
> The transformation phase is the phase where the optimizer will check if he
> does an or expansion or not.
>
> Once the optimizer has decided against an or expansion, the index range
> scan is not possible.
>
> Thus, seemingly the optimizer did not honor your hint.
>
>
>
> Regards
>
>
>
> Lothar
>
> ----Ursprüngliche Nachricht----
> Von : cstephens16_at_gmail.com
> Datum : 13/08/2018 - 13:33 (GMT)
> An : christopherdtaylor1994_at_gmail.com
> Cc : niall.litchfield_at_gmail.com, oracle-l_at_freelists.org
> Betreff : Re: force index range scan vs index full scan through hints?
>
> thanks chris/niall! that's exactly the solution we are pushing. they drop
> and recreate the entire schema so locking won't work but we've created a
> script to run after tables are recreated that sets stats and gets us the
> plans we want. for some reason that's seen as too complicated. SQL_ID
> changes constantly as number of "OR"'d conditions is dynamic.
>
>
>
> thanks for all the suggestions!
>
>
>
> On Sat, Aug 11, 2018 at 4:19 PM Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
> I do like Niall's suggestion about setting the stats. You could also lock
> those stats and as long as you're not using histograms they should be good
> to stay locked.
>
>
>
> Chris
>
> On Sat, Aug 11, 2018, 4:34 PM <niall.litchfield_at_gmail.com> wrote:
>
> I suggest that you modify the code that creates the table on the fly to.
>
>
>
> 1. Create the table as currently
>
> 2..SET statistics on the table that tell the optimizer it's got lots of
> rows in it (and is quite large and .. and) same for the index. Then
> schedule a gather later.
>
>
>
> On Fri, 10 Aug 2018, 14:51 Chris Stephens, <cstephens16_at_gmail.com> wrote:
>
> Is there a way to tell Oracle to use an index range scan instead of an
> index full scan?
>
>
>
> We have a table that gets created on the fly and quickly accumulates 100's
> of millions of rows before statistics are collected. Never mind why the
> table has no statistics (it's a (longish) story). As the table accumulates
> data, another process hits it with a piece of SQL that has a variable
> number of predicates of the from "id between :id1 and :id2 OR id between
> :id2 and id3 OR...". We have an index to support that SQL. Oracle initially
> chooses to full scan that index instead of range scan it until statistics
> are collected. Full scan performance is unacceptable. Is there a way to
> force the index range scan version? We can't use baselines (another story).
>
>
>
> We've tried INDEX_RS and INDEX_RS_ASC after observing INDEX hint doesn't
> get us what we want since index is already being used. INDEX_RS and
> INDEX_RS_ASC aren't in official documentation so I'm not even sure they are
> valid hints.
>
>
>
> As I write this, it occurs to me that dynamic sampling might help here and
> will try that but that obviously comes with some overhead. Any other
> options?
>
>
>
> Thanks for any insight.
>
>
>
> chris
>
>
>
>
>
>
>
>
>
> ____________________________________________________
>
> Please consider the environment before printing this e-mail.
>
> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>
>
> Important Notice
> This message is intended only for the individual named. It may contain
> confidential or privileged information. If you are not the named addressee
> you should in particular not disseminate, distribute, modify or copy this
> e-mail. Please notify the sender immediately by e-mail, if you have
> received this message by mistake and delete it from your system.
> Without prejudice to any contractual agreements between you and us which
> shall prevail in any case, we take it as your authorization to correspond
> with you by e-mail if you send us messages by e-mail. However, we reserve
> the right not to execute orders and instructions transmitted by e-mail at
> any time and without further explanation.
> E-mail transmission may not be secure or error-free as information could
> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
> processing of incoming e-mails cannot be guaranteed. All liability of
> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
> referred to as "Vontobel Group") for any damages resulting from e-mail use
> is excluded. You are advised that urgent and time sensitive messages should
> not be sent by e-mail and if verification is required please request a
> printed version. Please note that all e-mail communications to and from the
> Vontobel Group are subject to electronic storage and review by Vontobel
> Group. Unless stated to the contrary and without prejudice to any
> contractual agreements between you and Vontobel Group which shall prevail
> in any case, e-mail-communication is for informational purposes only and is
> not intended as an offer or solicitation for the purchase or sale of any
> financial instrument or as an official confirmation of any transaction.
> The legal basis for the processing of your personal data is the legitimate
> interest to develop a commercial relationship with you, as well as your
> consent to forward you commercial communications. You can exercise, at any
> time and under the terms established under current regulation, your rights.
> If you prefer not to receive any further communications, please contact
> your client relationship manager if you are a client of Vontobel Group or
> notify the sender. Please note for an exact reference to the affected group
> entity the corporate e-mail signature. For further information about data
> privacy at Vontobel Group please consult www.vontobel.com.
>

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