Ahh, but now your questions turns to query tuning. The answer may or may not be adding/removing histograms, changing the sample size or something like that. cheers. Alan.- On Mon, Aug 15, 2011 at 1:37 PM, Orlando L <oralrnr@xxxxxxxxx> wrote: > > Thank you Ric and others. > > 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@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. >> >> **** >> > >