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
>>>
>>>
>>>

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