Oracle Optimizer Hints

Optimizer hints can be used with SQL statements to alter execution plans.Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know.

For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan.

You can use hints to specify the following:

• The optimization approach for a SQL statement
• The goal of the cost-based optimizer for a SQL statement
• The access path for a table accessed by the statement
• The join order for a join statement
• A join operation in a join statement

Note : The use of hints involves extra code that must be managed, checked, and controlled.

Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the following criteria:

• Join order
• Join method
• Access path
• Parallelization

Hints (except for the RULE hint) invoke the cost-based optimizer (CBO). If you have not gathered statistics, then defaults are used. Hints apply only to the optimization of the statement block in which they appear. A statement block is any one of the following statements or parts of statements:

• A simple SELECT, UPDATE, or DELETE statement.
• A parent statement or subquery of a complex statement.
• A part of a compound query.

For example, a compound query consisting of two component queries combined by the UNION operator has two statement blocks, one for each component query. For this reason, hints in the first component query apply only to its optimization, not to the optimization of the second component query.

You can send hints for a SQL statement to the optimizer by enclosing them in a comment within the statement. A statement block can have only one comment containing hints. This comment can only follow the SELECT, UPDATE, or DELETE keyword.
Exception:
The APPEND hint always follows the INSERT keyword, and the PARALLEL hint can follow the INSERT keyword.

The following syntax shows hints contained in both styles of comments that Oracle supports within a statement block.

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

or

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

where:

• DELETE, INSERT, SELECT, and UPDATE are keywords that begin a statement block. Comments containing hints can appear only after these keywords.
• + causes Oracle to interpret the comment as a list of hints. The plus sign must immediately follow the comment delimiter; no space is permitted.
• hint is one of the hints discussed in this section. If the comment contains multiple hints, then each hint must be separated from the others by at least one space.
• text is other commenting text that can be interspersed with the hints.

If you specify hints incorrectly, then Oracle ignores them but does not return an error:

• Oracle ignores hints if the comment containing them does not follow a DELETE, SELECT, or UPDATE keyword.
• Oracle ignores hints containing syntax errors, but considers other correctly specified hints within the same comment.
• Oracle ignores combinations of conflicting hints, but considers other hints within the same comment.
• Oracle ignores hints in all SQL statements in those environments that use PL/SQL version 1, such as Forms version 3 triggers, Oracle Forms 4.5, and Oracle Reports 2.5. These hints can be passed to the server, but the server ignores them.

The optimizer recognizes hints only when using the cost-based approach. If you include a hint (except the RULE hint) in a statement block, then the optimizer automatically uses the cost-based approach.