Hi Chris,
If you are able to rewrite the query , try doing WITH *clause*,( subquery
factoring *clause)*, and join those rowsets.
Rgds,
Sachin Pawar
https://twitter.com/sach_pwr
On Fri, Aug 10, 2018 at 8:50 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
