Re: Hints

I have few queries that run slow, but I have to force them to use indexes. With the use of indexes they run much faster, with statistics present. I am left with the classic question of why is the optimizer not using my indexes and why do I have to force it.

On Mon, Aug 15, 2011 at 10:51 AM, Ric Van Dyke <ric.van.dyke_at_hotsos.com>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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Orlando L
> *Sent:* Monday, August 15, 2011 01:44
> *To:* oracle-l_at_freelists.org
> *Subject:* Hints****
>
> ** **
>
> Hello all,
>
> Are hints in queries considered bad, if so why.
>
> Orlando.
>
> ****
>

--
http://www.freelists.org/webpage/oracle-l