Re: Hints in Practice

Bob Jones wrote:
> "Robert Klemme" <shortcutter_at_googlemail.com> wrote in message
> news:4l0scsFe4upiU1_at_individual.net...
> >
> > All,
> >
> > from what I read and from my limited personal experience, hints are
> > superfluous most of the time. The general recommendation seems to be
> > "make sure your schema is properly done, create appropriate indexes, make
> > sure statistics are up to date and let the CBO do its work". Hints seem
> > to be useful only in very rare cases (bugs?) where the CBO doesn't come up
> > with an appropriate plan. Is this a correct summary of the situation or
> > did I miss something?
> >
> > Thanks
> >
> > robert
>
> That is correct. Oracle is about the only database I know uses explicit
> hints. That is not really a vote of confidence in their optimizer.

I would have agreed with this up to the middle 9 releases. The latter releases have few enough bugs it finally seems to work well (especially for those of us who work with products that don't allow passage of hints combined with sometimes-funky designs).

A more cynical viewpoint might be that having hints allows use of database design that conflicts with basic theory underlying optimization, so it is a vote of confidence in realpolitik.

>
> I would do anything to avoid using those hints. They are not portable and do
> not conform to any standard. It is almost always possible to build good
> performing queries without hints.

It's those "almosts" that always seem to get users so upset.

jg

--
@home.com is bogus.
"...knew almost nothing..."
http://www.signonsandiego.com/uniontrib/20060823/news_1b23banker.html