A long time ago, I attended a class given by Jonathan Lewis. Several
relevant things stood out from that session. Paraphrasing:
- Hints don't tell the optimizer to use a specific path. They simply
encourage (or discourage) a specific behaviour to be considered during
evaluation.
- The optimizer already considers a specific behaviour to be more
favoutable.
- It is quite possible that you need to discourage the current behaviour
through a hint, in addition to encouraging the new behaviour.
Therefore, using multiple hints to achieve a specific behaviour is not
unreasonable.
- For a variety of reasons, mostly gathered statistics but also
universal obstinance, a set of hints may end up selecting a completely
new behaviour. See previous item.
In addiiton to info on Jonathan's and Richard Foote's sites, you might
want to look at Dan's notes about this at
https://www.morganslibrary.org/reference/hints.html
/Hans
My opinions are my own, and not necessarily those of my employer, which
is Oracle Canada LLC
On 2018-08-10 6:49 AM, Chris Stephens 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
