Hints are comments in a SQL statement that pass instructions to the Oracle Database optimizer. The optimizer uses these hints to choose an execution plan for the statement, unless some condition exists that prevents the optimizer from doing so.
Hints were introduced in Oracle7, when users had little recourse if the optimizer generated suboptimal plans. Now Oracle provides a number of tools, including the SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to help you address performance problems that are not solved by the optimizer. Oracle strongly recommends that you use those tools rather than hints. The tools are far superior to hints, because when used on an ongoing basis, they provide fresh solutions as your data and database environment change.
Hints should be used sparingly, and only after you have collected statistics on the relevant tables and evaluated the optimizer plan without hints using the EXPLAIN PLAN statement. Changing database conditions as well as query performance enhancements in subsequent releases can have significant impact on how hints in your code affect performance.
The remainder of this section provides information on some commonly used hints. If you decide to use hints rather than the more advanced tuning tools, be aware that any short-term benefit resulting from the use of hints may not continue to result in improved performance over the long term.
Using Hints
A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, MERGE, or DELETE keyword.
The following syntax diagram shows hints contained in both styles of comments that Oracle supports within a statement block. The hint syntax must follow immediately after an INSERT, UPDATE, DELETE, SELECT, or MERGE keyword that begins the statement block.
hint::=
where:
The plus sign (+) causes Oracle to interpret the comment as a list of hints. The plus sign must follow immediately after the comment delimiter. No space is permitted.
hint is one of the hints discussed in this section. The space between the plus sign and the hint is optional. If the comment contains multiple hints, then separate the hints by at least one space.
string is other commenting text that can be interspersed with the hints.
The --+ syntax requires that the entire comment be on a single line.
Oracle Database ignores hints and does not return an error under the following circumstances:
The hint contains misspellings or syntax errors. However, the database does consider other correctly specified hints in the same comment.
The comment containing the hint does not follow a DELETE, INSERT, MERGE, SELECT, or UPDATE keyword.
A combination of hints conflict with each other. However, the database does consider other hints in the same comment.
The database environment uses PL/SQL version 1, such as Forms version 3 triggers, Oracle Forms 4.5, and Oracle Reports 2.5.
A global hint refers to multiple query blocks. Refer to Specifying Multiple Query Blocks in a Global Hint for more information.
Specifying a Query Block in a Hint
You can specify an optional query block name in many hints to specify the query block to which the hint applies. This syntax lets you specify in the outer query a hint that applies to an inline view.
The syntax of the query block argument is of the form @queryblock, where queryblock is an identifier that specifies a query block in the query. The queryblock identifier can either be system-generated or user-specified. When you specify a hint in the query block itself to which the hint applies, you omit the @queryblock syntax.
The system-generated identifier can be obtained by using EXPLAIN PLAN for the query. Pretransformation query block names can be determined by running EXPLAIN PLAN for the query using the NO_QUERY_TRANSFORMATION hint. See NO_QUERY_TRANSFORMATION Hint.
The user-specified name can be set with the QB_NAME hint. See QB_NAME Hint.
Specifying Global Hints
Many hints can apply both to specific tables or indexes and more globally to tables within a view or to columns that are part of indexes. The syntactic elements tablespec and indexspec define these global hints.
tablespec::=
You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint. However, do not include the schema name with the table name within the hint, even if the schema name appears in the statement.
Note:
Specifying a global hint using the tablespec clause does not work for queries that use ANSI joins, because the optimizer generates additional views during parsing. Instead, specify @queryblock to indicate the query block to which the hint applies.
indexspec::=
When tablespec is followed by indexspec in the specification of a hint, a comma separating the table name and index name is permitted but not required. Commas are also permitted, but not required, to separate multiple occurrences of indexspec.
Specifying Multiple Query Blocks in a Global Hint
Oracle Database ignores global hints that refer to multiple query blocks. To avoid this issue, Oracle recommends that you specify the object alias in the hint instead of using tablespec and indexspec.
For example, consider the following view v and table t:
CREATE VIEW v AS SELECT e.last_name, e.department_id, d.location_id FROM employees e, departments d WHERE e.department_id = d.department_id; CREATE TABLE t AS SELECT * from employees WHERE employee_id < 200;
Note:
The following examples use the EXPLAIN PLAN statement, which enables you to display the execution plan and determine if a hint is honored or ignored. Refer to EXPLAIN PLAN for more information.
The LEADING hint is ignored in the following query because it refers to multiple query blocks, that is, the main query block containing table t and the view query block v:
EXPLAIN PLAN
SET STATEMENT_ID = 'Test 1'
INTO plan_table FOR
(SELECT /*+ LEADING(v.e v.d t) */ *
FROM t, v
WHERE t.department_id = v.department_id);
The following SELECT statement returns the execution plan, which shows that the LEADING hint was ignored:
SELECT id, LPAD(' ',2*(LEVEL-1))||operation operation, options, object_name, object_alias
FROM plan_table
START WITH id = 0 AND statement_id = 'Test 1'
CONNECT BY PRIOR id = parent_id AND statement_id = 'Test 1'
ORDER BY id;
ID OPERATION OPTIONS OBJECT_NAME OBJECT_ALIAS
--- -------------------- ---------- ------------- --------------------
0 SELECT STATEMENT
1 HASH JOIN
2 HASH JOIN
3 TABLE ACCESS FULL DEPARTMENTS D@SEL$2
4 TABLE ACCESS FULL EMPLOYEES E@SEL$2
5 TABLE ACCESS FULL T T@SEL$1
The LEADING hint is honored in the following query because it refers to object aliases, which can be found in the execution plan that was returned by the previous query:
EXPLAIN PLAN
SET STATEMENT_ID = 'Test 2'
INTO plan_table FOR
(SELECT /*+ LEADING(E@SEL$2 D@SEL$2 T@SEL$1) */ *
FROM t, v
WHERE t.department_id = v.department_id);
The following SELECT statement returns the execution plan, which shows that the LEADING hint was honored:
SELECT id, LPAD(' ',2*(LEVEL-1))||operation operation, options,
object_name, object_alias
FROM plan_table
START WITH id = 0 AND statement_id = 'Test 2'
CONNECT BY PRIOR id = parent_id AND statement_id = 'Test 2'
ORDER BY id;
ID OPERATION OPTIONS OBJECT_NAME OBJECT_ALIAS
--- -------------------- ---------- ------------- --------------------
0 SELECT STATEMENT
1 HASH JOIN
2 HASH JOIN
3 TABLE ACCESS FULL EMPLOYEES E@SEL$2
4 TABLE ACCESS FULL DEPARTMENTS D@SEL$2
5 TABLE ACCESS FULL T T@SEL$1
Hints by Functional Category
Table 2-23 lists the hints by functional category and contains cross-references to the syntax and semantics for each hint. An alphabetical reference of the hints follows the table.
Table 2-23 Hints by Functional Category
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4