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
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
- A simple SELECT, UPDATE, or DELETE statement.
- A parent statement or subquery of a complex statement.
- A part of a compound 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.
- 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.