Re: Hints
- From: GBA-DBA <gba.oraclel@xxxxxxxxx>
- To: ric.van.dyke@xxxxxxxxxx
- Date: Mon, 15 Aug 2011 11:24:33 -0500
I remember a nice blog post by Jonathan Lewis (
http://jonathanlewis.wordpress.com) for safe/unsafe usage of hints. I just
don't have the direct link to it.
Thanks,
Galo
On Mon, Aug 15, 2011 at 10:51 AM, Ric Van Dyke <ric.van.dyke@xxxxxxxxxx>wrote:
> Keep in mind that hints aren’t hints, they are directives. Hints will be
> followed unless the optimizer can’t use the hint (tell it to use an index
> that doesn’t exist for example). ****
>
> ** **
>
> There are good hints and there are bad hints.****
>
> ** **
>
> Good hints help the optimizer do something, bad hints force the optimizer
> to do something.****
>
> ** **
>
> Examples: The cardinality hint is considered good because it can let the
> optimizer know how many rows will come back from a table when it can’t know
> (for example a Table that has been casted from a string). It can also “go
> bad” if the number of rows changes over time (the number of rows cannot be
> set dynamically as far as I know, other than dynamic SQL which has its own
> issues). ****
>
> ** **
>
> A join hint (use_nl for example) is considered bad because it forces the
> optimizer to pick that join every time. This might be true for the moment
> but is it always going to be true? Same for index or full table scan hints.
> ****
>
> ** **
>
> Hints are great for testing and should only be in production code as a
> “last resort”. ****
>
> ** **
>
> +--+--+--+--+--+--+--+--+--+--+--+--+--+--+****
>
> Ric Van Dyke****
>
> Education Director****
>
> Hotsos Ltd.****
>
> ** **
>
> Hotsos Symposium March 4-8 2012****
>
> Make your plans to be there now!****
>
> [image: Description: 2012_sym_logo_invert]****
>
> ** **
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Orlando L
> *Sent:* Monday, August 15, 2011 01:44
> *To:* oracle-l@xxxxxxxxxxxxx
> *Subject:* Hints****
>
> ** **
>
> Hello all,
>
> Are hints in queries considered bad, if so why.
>
> Orlando.
>
> ****
>
--
Regards
GBA
Other related posts:
