Re: Oracle Hint Behavior

From: Mark D Powell <>
Date: Wed, 30 Jan 2008 06:48:49 -0800 (PST)
Message-ID: <>

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 -- Received on Wed Jan 30 2008 - 08:48:49 CST

Original text of this message