RE: Hints

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Mon, 15 Aug 2011 10:51:50 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF01B6CD05_at_WIN02.hotsos.com>

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!    

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

Received on Mon Aug 15 2011 - 10:51:50 CDT