[ 2013-11-30 ]

A Beginner’s Guide to Optimizer Hints

It is often easy to forget this, but in many ways it is after we hit the execute button that the really exciting stuff starts with our code. A number of engines silently spring into action; including the optimizer. The optimizer analyses your SQL statement and decides the most efficient way to execute it based on the objects involved in the statement and the conditions you’re subjecting them to. Your database automatically gathers stats about your objects – stuff like the number of rows, number of distinct values, of nulls, data distribution – and the optimizer uses this information in its decision-making. (You can study the explain plan to see what decisions the optimizer has taken.)  The optimizer arrives at its conclusions, often in barely a whisper of time.
And when the SQL statement is executed, you sit back and you feel like a genius.
And that’s it, right? THE END.
Optimizer Hints
Well, not necessarily. The optimizer is the hero of our story; let me introduce the potential villains, Optimizer Hints. An optimizer hint is a code snippet within an SQL statement controlling the decisions of the optimizer. Hints give us the opportunity, in cases where we have superior knowledge about the database, to influence the optimizer. In fact, the very name is a misnomer – they are not hints; they are commands that override the optimizer (as long as the hint is valid and the _OPTIMIZER_IGNORE_HINTS initialization parameter is not TRUE).
Continuar aqui

No hay comentarios:

Publicar un comentario