Re: RE: Re: force index range scan vs index full scan through hints?
Date: Mon, 13 Aug 2018 17:20:01 +0200 (CEST)
Message-ID: <945232979.35932.1534173601152.JavaMail.webmail_at_bluewin.ch>
Hi Nenad,
good stuff, thanks.
Lothar
----Ursprüngliche Nachricht----
Von : nenad.noveljic_at_vontobel.com
Datum : 13/08/2018 - 17:02 (GMT)
An : l.flatz_at_bluewin.ch, cstephens16_at_gmail.com
Cc : christopherdtaylor1994_at_gmail.com, oracle-l_at_freelists.org, niall.litchfield_at_gmail.com
Betreff : RE: Re: force index range scan vs index full scan through hints?
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
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.
- 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
p.MsoNormal
{margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
margin-left: 0cm;
margin-right: 0cm;
margin-top: 0cm;
}
Please consider the environment before printing this e-mail. Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken. p { font-family: Arial;font-size:9pt }
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-lReceived on Mon Aug 13 2018 - 17:20:01 CEST