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 (Oracle 8i)
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 (Oracle 8i)
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…]) (Oracle 8i)
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 (Oracle 8.0+)
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.
This is a companion discussion topic for the original entry at https://blog.toadworld.com/spotlight_on_oracle_-_isolating_and_tuning_problem_sql