Yes, this should be possible, but you need multiple scans, not one.
Add the USE_CONCAT hint and a FIRST_ROWS_1 hint as well.
Otherwise you might want to rewrite the query using union all instead of the OR
Von : cstephens16@xxxxxxxxx
Datum : 10/08/2018 - 14:50 (GMT)
An : Oracle-L@xxxxxxxxxxxxx
Betreff : 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@xxxxxxxxx> wrote:
Is there a way to tell Oracle to use an index range scan instead of an index
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.