Optimizer Hints
- reference
Optimizer hints enable you to supply directives to the optimizer.
You can use optimizer hints to request that the optimizer should consider specific indexes, join methods, join ordering, and so on, when creating the plan for a query. This may be useful in situations where the optimizer is not able to come up with the preferred plan, due to lack of optimizer statistics, high level of skew in data, data correlations, and so on.
Generally speaking, you should rely on the optimizer to generate the query plan.
Syntax
hint-comment ::= block-hint-comment | line-hint-comment
block-hint-comment ::= '/*+' hints '*/'
line-hint-comment ::= '--+' hints
You can supply hints to the operator within a specially-formatted hint comment.
The hint comment may be a block comment or a line comment.
There must be a plus sign +
immediately after the start of the comment; this is the distinguishing delimiter of the hint comment.
Note that a line comment includes all text up to the end of the line. Therefore, if the hint comment is a line comment, the next part of the query must start on the following line.
SELECT /*+ INDEX(airport def_inventory_airport_city) */ airportname
FROM airport
WHERE city = "San Francisco";
SELECT --+ INDEX(airport def_inventory_airport_city)
airportname
FROM airport
WHERE city = "San Francisco";
Placement
Currently, a hint comment is only supported in the SELECT
statement.
The hint comment must be located immediately after the SELECT
keyword.
For details, refer to SELECT Clause.
There can only be one hint comment in a query block. If there is more than one hint comment, a syntax error is generated. However, the hint comment may contain one or more hints.
SELECT /*+ USE_HASH(r) */
/*+ INDEX(a def_inventory_airport_city) */
a.airportname, r.airline
FROM airport a
JOIN route r
ON a.faa = r.sourceairport
WHERE a.city = "San Francisco";
This query generates a syntax error, as it contains multiple hint comments.
Format
hints ::= simple-hint-sequence | json-hint-object
simple-hint-sequence ::= simple-hint+
json-hint-object ::= '{' json-hint (',' json-hint )* '}'
Internally, the hint comment may take one of two equivalent formats:
-
The simple syntax is a plain text format, similar to the type of hint comment found in many relational databases.
-
Alternatively, you may supply optimizer hints using JSON syntax. In this case, the hint comment contains a single top-level hint object.
You cannot mix the simple syntax and the JSON syntax within the same hint comment; each hint comment must use one syntax or the other exclusively.
To use multiple hints with the simple syntax, simply specify the hints one after another within the hint comment. To use multiple hints with the JSON syntax, specify each hint as a property within the top-level hint object.
SELECT /*+ INDEX(airport def_inventory_airport_city) */
airportname, faa
FROM airport
WHERE city = "San Francisco";
SELECT /*+ {"index": {"keyspace": "airport",
"indexes": "def_inventory_airport_city"}} */
airportname, faa
FROM airport
WHERE city = "San Francisco";
SELECT /*+ INDEX(a def_inventory_airport_city) USE_HASH(r) */
a.airportname, r.airline
FROM airport a
JOIN route r
ON a.faa = r.sourceairport
WHERE a.city = "San Francisco";
SELECT /*+ {"index": {"keyspace": "a",
"indexes": "def_inventory_airport_city"},
"use_hash": {"keyspace": "r"}} */
a.airportname, r.airline
FROM airport a
JOIN route r
ON a.faa = r.sourceairport
WHERE a.city = "San Francisco";
Legacy Equivalents
Many optimizer hints have an equivalent legacy syntax using the USE
clause.
Details of these are given on the pages for individual optimizer hints, and in the pages describing the USE
clause.
For details, refer to USE INDEX Clause
and ANSI JOIN Hints.
Note that you cannot use a hint comment and the USE
clause to specify optimizer hints on the same keyspace.
If you do this, the hint comment and the USE
clause are marked as erroneous and ignored by the optimizer.
SELECT airportname, faa
FROM airport
USE INDEX (def_inventory_airport_city)
WHERE city = "San Francisco";
Explain Plans
When optimizer hints are specified for a query, the explain plan reports the status of each hint: that is, whether the hint was followed or not followed by the optimizer in choosing the query plan. Invalid hints are also reported. Specific error messages are given for any hint that is not followed, or invalid.
When the optimizer follows a simple hint, the hint is shown in the explain plan.
EXPLAIN SELECT /*+ INDEX(airport def_inventory_airport_city) */
airportname, faa
FROM airport
WHERE city = "San Francisco";
[
{
"optimizer_hints": {
"hints_followed": [
"INDEX(airport def_inventory_airport_city)"
]
},
// ...
}
]
When the optimizer follows a JSON hint, the hint is shown in the explain plan in JSON format.
EXPLAIN SELECT /*+ {"index": {"keyspace": "airport",
"indexes": "def_inventory_airport_city"}} */
airportname, faa
FROM airport
WHERE city = "San Francisco";
[
{
"optimizer_hints": {
"hints_followed": [
"hint": "{\"index\":{\"indexes\":[\"def_inventory_airport_city\"],\"keyspace\":\"airport\"}}"
]
},
// ...
}
]
When the optimizer follows multiple hints, all the followed hints are shown in the explain plan.
EXPLAIN SELECT /*+ USE_HASH(r) INDEX(a def_inventory_airport_city) */
a.airportname, r.airline
FROM airport a
JOIN route r
ON a.faa = r.sourceairport
WHERE a.city = "San Francisco";
[
{
"optimizer_hints": {
"hints_followed": [
"USE_HASH(r)",
"INDEX(a def_inventory_airport_city)"
]
},
// ...
}
]
When the optimizer follows a hint specified using the legacy USE
clause, the hint is likewise shown in the explain plan.
EXPLAIN SELECT airportname, faa
FROM airport
USE INDEX (def_inventory_airport_city)
WHERE city = "San Francisco";
[
{
"optimizer_hints": {
"hints_followed": [
"INDEX(airport def_inventory_airport_city)"
]
},
// ...
}
]
When the optimizer cannot follow a hint, any hints that cannot be followed are shown in the explain plan.
EXPLAIN SELECT /*+ USE_HASH(r) INDEX(a def_inventory_airport_city) */
a.airportname, r.airline
FROM airport a
JOIN route r
ON a.faa = r.sourceairport
WHERE a.city IS MISSING;
[
{
"optimizer_hints": {
"hints_followed": [
"USE_HASH(r)"
],
"hints_not_followed": [
"INDEX(a def_inventory_airport_city): INDEX hint cannot be followed"
]
},
// ...
}
]
When you specify an invalid hint, any invalid hints are shown in the explain plan.
EXPLAIN SELECT /*+ USE_HASH(r) INDEX_SS(a def_inventory_airport_city) */
a.airportname, r.airline
FROM airport a
JOIN route r
ON a.faa = r.sourceairport
WHERE a.city = "San Francisco";
[
{
"optimizer_hints": {
"hints_followed": [
"USE_HASH(r)"
],
"invalid_hints": [
"INDEX_SS(a def_inventory_airport_city): Invalid hint name"
]
},
// ...
}
]
Further Details
Refer to the following pages for details of individual optimizer hints:
-
Query Block Hints apply to an entire query block.
-
Keyspace Hints apply to a specific keyspace.