Re: Using hints with Oracle9i MERGE

Niall Litchfield wrote:

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

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.

Daniel Morgan Received on Fri Feb 14 2003 - 03:31:23 CST