Re: Oracle Hint Behavior

"Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message news:BO%nj.9627$EZ3.8946_at_nlpi070.nbdc.sbc.com...
> 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.
>

One of the problems of hinting in 10g is that the rules of application seem to have become more formal. This MAY be a side effect of the way that cost-based query transformation works.

I have examples where I can supply hints that are considered correct in 9i, but are not valid unless I use the fully qualified hint with the query block name included.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html