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

Theres a script on metalink - part of trcanalzye (Carlos S?) I think.

Anyway, there's a coe_xfr_sql_profile.sql script that will dump a profile for a sq_lid to a SQL script.

In that profile script it creats you'll, see INDEX_FS or INDEX_FFS for the index you're interested in.

  You can change that line to INDEX_RS and run the script to create the profile and force a range scan and that line in the plan. Then test your performance and verify that the profile is used and that you get the range scan.

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