Re: force index range scan vs index full scan through hints?
Date: Sat, 11 Aug 2018 22:33:05 +0200
Message-ID: <CABe10sbwpmTakOBN4mzm0Sb-rPBpHt+GWVAsoCp+t0NvUV5CQw_at_mail.gmail.com>
I suggest that you modify the code that creates the table on the fly to.
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-lReceived on Sat Aug 11 2018 - 22:33:05 CEST