Re: Oracle Hint Behavior
Date: Wed, 30 Jan 2008 17:03:56 -0600
Message-ID: <Il7oj.9841$hI1.5740@nlpi061.nbdc.sbc.com>
Mark D Powell wrote:
> On Jan 30, 9:28 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
>> Oracle 10.2.0.3.0 >> 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 9.2.0.5 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... Received on Wed Jan 30 2008 - 17:03:56 CST