thanks chris/niall! that's exactly the solution we are pushing. they drop
and recreate the entire schema so locking won't work but we've created a
script to run after tables are recreated that sets stats and gets us the
plans we want. for some reason that's seen as too complicated. SQL_ID
changes constantly as number of "OR"'d conditions is dynamic.
thanks for all the suggestions!
On Sat, Aug 11, 2018 at 4:19 PM Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> wrote:
I do like Niall's suggestion about setting the stats. You could also lock
those stats and as long as you're not using histograms they should be good
to stay locked.
Chris
On Sat, Aug 11, 2018, 4:34 PM <niall.litchfield@xxxxxxxxx> wrote:
I suggest that you modify the code that creates the table on the fly to.
1. Create the table as currently
2..SET statistics on the table that tell the optimizer it's got lots of
rows in it (and is quite large and .. and) same for the index. Then
schedule a gather later.
On Fri, 10 Aug 2018, 14:51 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
