Optimizer hints appear as a comment following the first word of the SQL statement (for example, SELECT, INSERT, DELETE, or UPDATE). Hints are differentiated from other comments by the presence of the plus sign (+) following the opening comment delimiter (/*). For instance, the FULL hint in the following example tells the optimizer to perform a full table scan when resolving the query:
SELECT /*+ FULL(E) */ *
FROM EMPLOYEE E
WHERE SALARY > 1000000
The following list shows the hints that can be used:
| Hint | Description |
|---|---|
|
ALL_ROWS |
Use the cost-based optimizer and optimize for the retrieval of all rows. |
|
AND_EQUAL (table_name index_name index_name ....) |
Retrieve rows from the specified table using each of the specified indexes and merge the results. |
|
APPEND |
Invokes a direct load insert. Only valid for insert … select from statements. |
|
BITMAP (table_name index_name) |
Retrieve rows from the specified table using the specified bitmap index. |
|
CACHE (table_name) |
Encourages rows retrieved by a full table scan to remain in the buffer cache of the SGA. |
|
CHOOSE |
If statistics have been collected for any table involved in the SQL statement, use cost-based or all-rows optimization, otherwise use rule-based optimization. |
|
CLUSTER (table_name) |
Uses a cluster scan to retrieve table rows. |
|
DRIVING_SITE (table_name) |
For a distributed SQL statement, this causes the site at which the specified table resides to be the driving site. |
|
FIRST_ROWS |
Specifies that the cost-based optimizer should optimize the statement to reduce the cost of retrieving the first row only. |
|
FULL (table_name) |
Use a full table scan to retrieve rows from the specified table. |
|
HASH (table_name) |
Use a hash scan to retrieve rows from the specified table. The table must be stored in a hash cluster. |
|
HASH_AJ |
Perform an anti-join using hash join methodology. This hint must appear after the select statement, not in sub-query. |
|
HASH_SJ |
Appears within an EXISTS sub-query. Invokes a hash semi-join. |
|
INDEX (table_name [index_name]) |
Uses the specified index to retrieve rows from the table or, if no index is specified, uses any index. |
|
INDEX_ASC (table_name [index_name]) |
Specifies an ascending index range scan using the specified index or, if no index is specified, any suitable index. |
|
INDEX_COMBINE (table_name [index_name…]) |
Instructs the optimizer to combine the specified bitmap indexes. If no bitmap indexes are specified, the optimizer chooses suitable bitmap indexes. |
|
INDEX_DESC (table_name [index_name]) |
Specifies a descending index range scan using the specified index or, if no index is specified, any suitable index. |
|
INDEX_FFS (table_name [index_name]) |
Invokes a fast full index scan using the specified index or, if no index is specified, any suitable index. A fast full scan reads the entire index in block order, using multi-block reads and possibly parallel query. |
|
MERGE |
Instructs the optimizer to perform complex view merging when resolving a query based on a view, or one that includes a sub-query in the WHERE clause. |
|
NO_MERGE |
Instructs the optimizer not to perform complex view merging when resolving a query based on a view, or one that includes a sub-query in the WHERE clause. |
|
MERGE_AJ |
Performs an anti-join using sort-merge join method. This hint must appear after the SELECT statement, not in a sub-query. |
|
MERGE_SJ |
Appears within an EXISTS sub-query. Invokes a sort-merge semi-join. |
|
NO_EXPAND (table_name) |
Oracle sometimes expands statements with OR conditions into multiple SQL statements combined by a union operation. This hint instructs the optimizer not to do this, even if it calculates that such a transformation would be beneficial. |
|
NO_INDEX (table_name [index_name] ) |
No index suppresses the use of the named indexes or, if no indexes are specified, all indexes on the named table. |
|
NO_PUSH_PRED |
Instructs not to push join conditions from the WHERE clause into a view or sub-query. |
|
NOAPPEND |
Suppresses direct load insert in an INSERT… SELECT FROM... statement. |
|
NOCACHE (table_name) |
Discourages Oracle from keeping rows retrieved by a full table scan in the buffer cache of the SGA. Overrides the cache setting on the CREATE or ALTER TABLE statement. |
|
NOPARALLEL (table_name) |
Do not use parallel processing for the SQL statement. Overrides the parallel setting on the CREATE or ALTER TABLE statement. |
|
NOPARALLEL_INDEX (table_name index_name) |
Suppresses parallelism in fast full index scans or in partitioned index access. |
|
NOREWRITE |
Prevents the SQL statement from being rewritten to take advantage of materialized views. It overrides the server parameter query_rewrite_enabled. |
|
ORDERED |
Instructs the optimizer to join the tables in exactly the left to right order specified in the FROM clause. |
|
ORDERED_PREDICATES |
Causes predicates in the WHERE clause to be evaluated in the order in which they appear in the WHERE clause. |
|
PARALLEL (table_name , degree_of_parallelism) |
Instructs the optimizer to perform parallel scans on the named table. If no degree of parallelism is specified, the default is used. |
|
PARALLEL_INDEX (table_name [index_name]) |
Parallelizes a fast full index scan, or an index scan against a partitioned index. |
|
PQ_DISTRIBUTE (table_name outer_distribution inner_distribution) |
This query determines how a parallel join using table_name is executed. Valid options for outer_distribution and inner_distribution are (not all combinations are valid) hash, broadcast, none, partition. |
|
PUSH_JOIN_PRED/ PUSH_PRED |
Push join conditions from the WHERE clause into a view or sub-query. |
|
PUSH_SUBQ |
Causes sub-queries to be processed earlier in the execution plan. Normally, sub-queries are processed last, unless the SQL statement is transformed into join. |
|
REWRITE (view_name [view_name…]) |
Restricts query rewrite to only those materialized views specified in the hint. |
|
ROWID (table_name) |
Performs a ROWID access. |
|
RULE |
Uses rule-based optimization. |
|
STAR |
Considers the STAR join methodology in preference to other methods. |
|
STAR_TRANSFORMATION |
Requests that the star transformation optimization be performed. This transforms a star query into an alternate form that can take advantage of bitmap indexes. |
|
USE_CONCAT |
Oracle sometimes expands statements with OR conditions into multiple SQL statements combined by union all. This hint instructs the optimizer to do this, even if it calculates that such a transformation would not be beneficial. |
|
USE_HASH (table_name) |
When joining to this table, use the hash join method. |
|
USE_MERGE (table_name) |
When joining to this table, use the sort-merge join method. |
|
USE_NL (table_name) |
When joining to this table, use the nested-loops join method. |
