Re: Using hints with Oracle9i MERGE

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E4C728A.D743B26E_at_exesolutions.com...
> Hints are just that hints ... not directives. Oracle may well be looking
at your
> hint and deciding it doesn't like it.

Um no. A hint *is* a directive, that is to say IF the CBO considers the access plan specified by your hint it WILL use it. So if the hint isn't used it generally means one of two things.

1. the hint isn't syntatically correct, which doesn't appear to be the case here.
2. The CBO doesn't consider the access path that you hint, you can find this information from a 10053 trace. What this usually means in fact is that you haven't specified enough hints - often you would need an ORDERED hint as well as the USE_HASH, USE_NL type of hint.

If in doubt it is worth looking at how plan stability works. Essentially it works by storing a hinted execution plan and then using query rewrite to pass the hinted query to the CBO rather than the query as specified by the app, if you take a look at the tables where these plans are stored the number of hints can be quite staggering, the idea being of course that although Oracle is using the CBO it is effectively limited to just the desired execution plan.

I'd like to see the explain plan as well, but the version is 9i as per the subject line.

--
Niall Litchfield
Oracle DBA
Audit Commission UK