Re: force index range scan vs index full scan through hints?

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
>
>

--
http://www.freelists.org/webpage/oracle-l