Re: Hints in Practice

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 22 Aug 2006 22:06:24 -0700
Message-ID: <1156309585.128739@bubbleator.drizzle.com>

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.

<RANT WARNING>
By your logic the DBMS_LOCK package would be a vote of no-confidence in the provided locks, global temporary, iot, and partitioning a vote of no-confidence in their table structures, and RAC a vote of no-confidence in the stability of the product: An interesting way to approach a vendor that provides a robust product and gives the developer and/or DBA the CHOICE as to whether they wish to grind out slightly better scalability and performance.

> I would do anything to avoid using those hints. They are not portable and do > not conform to any standard.

Thus you don't use array processing, don't use exception handling, don't use PL/SQL packages, and heaven-forbid don't use any type of index other than B*Tree and Bitmap.

I can not subscribe to gutting those features that make a product best of breed just because they are not also present in the worst of breed. </RANT WARNING>

> It is almost always possible to build good > performing queries without hints.

With this I agree. I hardly ever use hints but then that is the advice given by the experts that know Oracle far better than I ever will.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Aug 23 2006 - 00:06:24 CDT