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

forgot version info: 12.2 3-node RAC running on Centos 7.

On Fri, Aug 10, 2018 at 7:49 AM 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