Path: text.usenetserver.com!out04a.usenetserver.com!news.usenetserver.com!in04.usenetserver.com!news.usenetserver.com!nx01.iad01.newshosting.com!newshosting.com!216.196.98.140.MISMATCH!border1.nntp.dca.giganews.com!nntp.giganews.com!local02.nntp.dca.giganews.com!nntp.bt.com!news.bt.com.POSTED!not-for-mail NNTP-Posting-Date: Sat, 02 Feb 2008 05:13:41 -0600 From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server References: Subject: Re: Oracle Hint Behavior Date: Sat, 2 Feb 2008 11:11:39 -0000 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.3138 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 X-RFC2646: Format=Flowed; Response Message-ID: Lines: 42 X-Usenet-Provider: http://www.giganews.com NNTP-Posting-Host: 81.151.8.142 X-AuthenticatedUsername: NoAuthUser X-Trace: sv3-ljmSE3U3ro1zNpKKUpHTJR6dpM+CrfblTV4fXsfgrjd9RHvlsWwD1RZ59CJVVIrcURrSahJjA7oPfe1!nccshEnJqTTu+Pf992LsenZz8SQu+XLkAQ1TjAEdouqdrmT1M1y2Hq8FsSQMGwtiwFEgnTaHMg== X-Complaints-To: abuse@btinternet.com X-DMCA-Complaints-To: abuse@btinternet.com X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.37 Bytes: 2496 Xref: usenetserver.com comp.databases.oracle.server:441022 X-Received-Date: Sat, 02 Feb 2008 06:13:42 EST (text.usenetserver.com) "Dereck L. Dietz" wrote in message news:BO%nj.9627$EZ3.8946@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