Re: Hints in Practice

On Tue, 22 Aug 2006 19:15:07 +0200, Robert Klemme wrote:

>
> All,
>
> from what I read and from my limited personal experience, hints are
> superfluous most of the time. The general recommendation seems to be

SQL is a 4th Generation Language. In it's simplest terms, that means we use the language to specify 'what' we want to accomplish, not 'how' the system is to accomplish it.

Each RDBMS implementation that supports SQL is free to interpret the request (the SQL statement) in any way it wishes. The SQL engine translates the SQL statements into a series of routines or calls that accomplish the requested task.

The method of interpretation, and the resulting execution, is entirely up the the 'vendor'.

Hints, which are implemented by several different RDBMSs, have nothing to do with the SQL syntax or standard, and therefore do not have a formal syntax or interpretation. Hints are usually implemented as SQL comments to ensure they do not interfere with the SQL request.

They can be considered directives to either the optimization engine or the execution engine (or both). Since both engines are implementation specific, hints are necessarily implementation specific.

The purpose of hints is simple - provide an increased weighting to specific optimization paths or a decreased weighting to others, to allow the execution engine to use a different implemenation sequence than would be used by default. (In Oracle's case, I view hints as a way to skew the statistics.)

The benefits of hints is also simple - they tend to shorten/minimize the graph of potential paths the engines need to analyze while selecting the calls/routines to execute the request. This does NOT imply they will provide a performance benefit at execution time.

Hints can, however, be very very difficult to understand. At one seminar, Jonathan Lewis managed to convince me that "one hint in a SQL statement is rarely enough to make the system actually implement the method (the *how*) you intend." (paraphrased - thanks Jonathan) From that prespective, alone, they should be avoided in Oracle. IOW, knowing the basics about hints is at the level of 'having enough knowledge to be dangerous'.

Hints are not the evil thing some seem to suggest. They are not portable, but then, neither is a SQL transaction that involves more than one SQL statement - simply because there is (imo, thankfully) no definition, including locking implications, of HOW the SQL interpretation shall occur.

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.