Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in01.usenetserver.com!news.usenetserver.com!newspump.sol.net!news.glorb.com!postnews.google.com!i7g2000prf.googlegroups.com!not-for-mail From: Mark D Powell Newsgroups: comp.databases.oracle.server Subject: Re: Oracle Hint Behavior Date: Sat, 2 Feb 2008 18:17:33 -0800 (PST) Organization: http://groups.google.com Lines: 89 Message-ID: <37a0a873-f040-4bcc-94e9-9c8823c27222@i7g2000prf.googlegroups.com> References: NNTP-Posting-Host: 69.245.99.253 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1202005054 32107 127.0.0.1 (3 Feb 2008 02:17:34 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Sun, 3 Feb 2008 02:17:34 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: i7g2000prf.googlegroups.com; posting-host=69.245.99.253; posting-account=qJFqbQkAAACYQSLN0-cvP6ydkRfuOu6u User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.0.3705; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe) Xref: usenetserver.com comp.databases.oracle.server:441039 X-Received-Date: Sat, 02 Feb 2008 21:17:34 EST (text.usenetserver.com) On Jan 30, 6:03=A0pm, Michael Austin wrote: > Mark D Powell wrote: > > On Jan 30, 9:28 am, "Dereck L. Dietz" wrote: > >> Oracle 10.2.0.3.0 > >> Windows 2003 Server > > >> What is the behavior of hints in Oracle 10g? =A0I knew that prior to 10= g > >> 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. =A0I can see why it would choose a fu= ll > >> 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. =A0There 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. =A0Otherwise 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 =3D a.a > =A0 =A0and c.a =3D a.a > =A0 =A0and c.a =3D a.a =A0 !!!<<< ...... > > The optimizer sees this and says "OH!!! you really did want THAT Index..."= > > Again this is an over-simplifed example. =A0The 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. =A0Not 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". =A0I 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). =A0I 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)) =A0- it will still= > execute unchanged. May not get the same performance, but it would still > run...- Hide quoted text - > > - Show quoted text - This is kind of a side note but the cost base optimizer was introduced with Oracle version 7 and I seem to remember that was before Oracle bought RBD from DEC. It seems to me that Oracle hired a fellow from Ingres to write the optimizer. That does not mean that features of the RDB optimizer did not make it into the Oracle optimzer but I think your timeline is off. Have you got any refereneces? -- Mark D Powell --