Re: Using hints with Oracle9i MERGE

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<b2ifdq$pes$1$8302bc10_at_news.demon.co.uk>...
> 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 )
>
>
> 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.
> >

Bob, I agree with Jonathan and Naill that hints are directives, but getting back to your problem. I would try moving the hint from the Merge line to being immediately after the Select statement and see if this modifies the select as desired.

HTH -- Mark D Powell -- Received on Fri Feb 14 2003 - 08:46:31 CST