Re: Using hints with Oracle9i MERGE

Jonathan Lewis wrote:

> The commonest reason why Oracle will refuse
> to use a hinted index when the hint syntax is
> correct is that the index has no columns declared
> as NOT NULL.
>
> Niall is correct - barring a few bugs, oddities
> regarding internal rewrites that take place before
> optimisation, and the cases where Oracle stops
> optimising before it gets to a point where your
> hint is relevant - hints are directives.
>
> If hints were not directives, how could Oracle
> choose to ignore the hint ? Presumably by
> deciding that another path was better. But
> then hints would always be redundant, because
> Oracle would only obey them when they hinted
> the path that Oracle considered to be the best
> path - which is the path that Oracle would have
> taken without the hint. If this were the case,
> the only hints that could be of use would be
> the hints telling Oracle to use some feature
> that was a feature (e.g. unnesting) that was
> 'uncosted' in your specific version of Oracle
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Coming soon one-day tutorials:
> Cost Based Optimisation
> Trouble-shooting and Tuning
> Indexing Strategies
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> ____UK_______March 19th
> ____USA_(FL)_May 2nd
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA_(CA, TX)_August
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> DA Morgan wrote in message <3E4CB76B.16BBD96B_at_exesolutions.com>...
> >
> >I have heard this before but it doesn't jive with my experience. I
> have put
> >together demos many times where a table exists, a simple index
> exists, and a
> >hint to use the index is ignored because Oracle determines the full
> table scan
> >to be more efficient.
> >
> >If I am incorrect I would be fascinated to see some examples.
> >

Thanks for the clarification. The NOT NULL might well be the issue. I'll explore that this weekend.

Daniel Morgan Received on Fri Feb 14 2003 - 10:45:17 CST