Re: Oracle Hint Behavior

From: Anurag Varma <>
Date: Thu, 31 Jan 2008 07:38:55 -0800 (PST)
Message-ID: <>

On Jan 30, 6:03 pm, Michael Austin <> wrote:
> Mark D Powell wrote:
> > On Jan 30, 9:28 am, "Dereck L. Dietz" <> wrote:
> >> Oracle
> >> Windows 2003 Server
> >> What is the behavior of hints in Oracle 10g? I knew that prior to 10g
> >> Oracle would treat them as only "suggestions" but I thought in 10g they
> >> would be mostly followed.
> >> We have a query which is doing a full table scan even with hints being used
> >> to try to get it to use an index. I can see why it would choose a full
> >> table scan considering the percentage of the table being returned but I'd
> >> like to be able to explain why the hint is being ignored now.
> >> Thanks.
> > Hints are directives to the optimizer and if valid will be followed;
> > however, there are operations on the SQL such as query transformation
> > that can render what is syntaxically a valid hint invalid. There are
> > also optimizer decisions that are made prior to the hint being
> > considered that can render the hint invalid such as the choice to hash
> > join.
> > Multiple hints are often necessary to try to force a specific plan and
> > you may need to disable the pushing of predicates or sub-queries
> > (hints available) to get the plan you want to test.
> > To get help for your specific SQL you should post the SQL and the
> > explain plan. Otherwise all anyone can provide is general cases.
> > HTH -- Mark D Powell --
> There is an interesting way to influence the optimizer without the use
> of hints that is not written in any books and coding it seems - well,
> ummm, redundant.
> I have used this on many different queries where we could not get the
> optimizer to use a particular index no matter how hard we tried.
> This is an over-simplified query, but you will get the point.
> select a.a, b.b, c.c
> from tablea a, tableb b, tablec c
> where b.a = a.a
> and c.a = a.a
> and c.a = a.a !!!<<<<NOTE added a second time
> ......
> The optimizer sees this and says "OH!!! you really did want THAT Index..."
> Again this is an over-simplifed example. The optimizer was pulled out
> of DEC Rdb right after Oracle acquired Rdb back in '94/'95 timeframe.
> Working with Rdb Engineering, this is something we discovered shortly
> before the DEC fire sale. Not too long ago, I had a colleague that was
> having a horrendous time with performance and noticed that there was an
> index not being used that "should have been". I offered this solution
> and the query used the index and performance went from ~30 minutes to
> 2-3 minutes (data warehouse timeframe using CBO). I have more
> recently had similar results using 10gR2.
> Yeah, it's a kludge, but it does work -- and BTW - still works in Rdb as
> well.
> cool part is that this is all ANSI compliant and if you ever have to
> move to another db engine (heaven forbid (cough,cough)) - it will still
> execute unchanged. May not get the same performance, but it would still
> run...

If you could change the sql, why not just add a hint instead of this kludge?
Also, can you provide a working example to show this? As far as I understand,
the optimizer will rewrite your statement to remove the redundant line.

Adding a hint will make your intentions more clear .. rather than adding a
redundant line which most of DBA's will think was a typo?

Anurag Received on Thu Jan 31 2008 - 09:38:55 CST

Original text of this message