Oracle Optimizer Hints

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. The optimizer arrives at its conclusions, often in barely a whisper of time.

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. Hints are commands that override the optimizer (as long as the hint is valid and the _OPTIMIZER_IGNORE_HINTS initialization parameter is not TRUE).

Hints are injected into DML statements within the bounds of a comment. The syntax is as follows:

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint [text]] */

below is also valid syntax :

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint [text]]

The + tells Oracle that this isn’t an ordinary comment, that it is in fact a hint. No spaces are allowed between the comment delimiter and the plus sign.

Here’s an example instructing that a full table scan should be carried out on the emp table:

SELECT /*+ FULL(emp) */ name
FROM emp
WHERE job = 'SALESMAN';

The reason to be wary of hinting is that by embedding hints in your SQL, you are overriding the optimizer and saying that you know more than it does – not just now, but every time in the future that your SQL will be run, irrespective of any other changes that may happen to your database. The likely consequence of this is that your SQL will possibly run sub-optimally now and almost certainly in the future.

There are many dozens of different hints (have a look in the v$sql_hint view), however close to half of them are undocumented.

Example :

FIRST_ROWS(n) : This hint instructs the optimizer to select a plan that returns the first n rows most efficiently.

SELECT  /*+ FIRST_ROWS(10) */ empno, ename
FROM emp
WHERE deptno = 10;

You may also want to read up about FIRST_ROWS_1, FIRST_ROWS_10 and FIRST_ROWS_100. Of interest, also, is ALL_ROWS which details the optimizer to choose the plan that most effectively returns the resultset at the minimum cost.

PARALLEL (table n): This hint tells the optimizer to use n concurrent servers for a parallel operation.

APPEND: This hint instructs the optimizer to carry out a direct-path insert. This may make INSERT … SELECT statements faster because inserted data is simply appended to the end of the table and any referential constraints are ignored.

RULE: This hint basically turns off the optimizer. This hint has been deprecated and should not be used. Never ever.