Jonathan Lewis kindly responded with the following which perfectly
describes what we are experiencing. He's been unable to post to oracle-l
for unknown reasons and asked me to forward:
"It's possible that the optimizer considers OR-expansion (since you're on
12.2), at which point your index_rs_asc may not be relevant because it
doesn't address any of the expanded query blocks (sel$1 would become
sel$1_1 union all sel$1_2 union all ....). After testing or-expansion the
optimizer may decide that it's cheaper to do one full scan and filter
rather than multiple range scans; possibly using the hint
OR_EXPAND(@{query_block_name}) will force the expansion and then leave
Oracle using the index range scan in each branch.
Regards
Jonathan Lewis"
On Fri, Aug 10, 2018 at 7:50 AM Chris Stephens <cstephens16@xxxxxxxxx>
wrote:
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 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
