Search notes:

Oracle SQL Hints

Most hints are used to influence the optimizer and have no effect on the result of an SQL statement execution.
There are three execptions, however, which do have an effect on the result of an SQL statement (but should not fresh_mv and rewrite_or_error not also be counted among them?):
The hints are formulated within comments whose first character is a plus sign (+).
The comment must be inserted in an SQL statement directly after the one of the keywords select, update, insert, delete or merge. In all other places, a comment cannot be be recognized as a hint.
Multiple hints are separated by white space:
select /*+ hint-1 hint-2 … */
  …
from
  …;
Some hints have arguments:
select /*+ hint(argument)                 */  …
select /*+ hint(argument-1 argument_2 … ) */  …
When using component queries (for example when combined with union), both queries can have their own hint:
select /*+ hint-1 */ …
  union
select /*+ hint-2 */ …
All hints except /*+ rule */ cause the (cost based) optimizer to be used.
If mutliple hints are specified and a hint is not understood by Oracle (for example because of a typo), then Oracle will honor the hints on the left side of that hint and disregard the rest.
Many hints come in a positive and negative (inverse) version, for example expand_table and no_expand_table.

Four types of hints

There are four types of hints:
Operates on Examples Comment
Single-table one table or view index(tab ix) , use_nl(…)
Multi-table multiple table leading(tab_a tab_b)
Query-block single query blocks star_transformation(@sel$2) , unnest(@sel$2) , full(@sel$2 t1) Compare with the column qblock_name in the plan_table
Statement entire SQL statements first_rows, all_rows
Note that use_nl(tab_a tab_b) is shortcut for use_nl(tab_a) use_nl(tab_b) and therefore is not considered to be a multi-table hint.

Specifying table names in hints

If a single or multi-table hint refers to a table that is aliased, the alias needs to be specified in the hint.
Wrong:
select /*+ index(scott.emp ix_emp) */ from scott.emp  e;
Better:
select /*+ index(e) */ from scott.emp  e;
Schema names should be omitted.

When hints should be used

With an ideal optimizer, hints should not be used. However, it turns out that the optimizer sometimes creates a suboptimal plan in which case better performance can be achieved by hinting the SQL statement.
Sometimes, the characteristics of the selected data changes rapidly so that the relevant statistics are inaccurate which also might lead to a bad plan.

«Ignored» hints

At times, it seems as though the optimizer ignores the given hints.
Two likely causes for this are that either

Jonathan Lewis' list of the Big Five Hints

Jonathan Lewis' list of the Big Five Hints is:

Verifying if hints are correctly specified

Incorrectly specified hints (for example because of a typo) are silently ignored by the optimizer.
In order to verify if all hints are correct and understood by the optimizer, the «flag» +hint_report of dbms_xplan.display can be used:
explain plan for
select /*+ no_such_hint */
   42 as num
from
   dual;
The following statement reports the erroneous hint at the bottom of the result set with the E flag:
select * from table(dbms_xplan.display(format => '+hint_report'))
  …
         E -  no_such_hint

List of some hints

The following table lists some hints I've come accross. In cases where the negative (inverse) version of a hint is simply no or no_ prepended to the hint name, I've put both hints in the same line.
Hint Comment
and_equal Choose an execution plan that uses an access path that merges the scans on several single-column indexes
antijoin
all_rows Compare with first_rows
append, noappend append: If a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces generation of redo.
append_values Similar to append, but applies to insert statements with a values clause (as opposed to insert statements that get their data from subqueries).
bind_aware Force bind aware cursor sharing from the first execution
cache, nocache When performing a full table scan, this hint places data blocks at the most recently used end of the LRU list of the buffer cache. Useful for (small) lookup tables.
cardinality Not documented. For example select /*+ cardinality(t, 1234) */ from tq84_tab t;. This hint is superseded by opt_estimate.
change_dupkey_error_index One of the three hints with semantic effect. Compare with ignore_row_on_dupkey_index
cluster Perform a nested loop by the cluster index. Compare with the hash index.
clustering, no_clustering
connect_by_filtering Undocumented? Found in the shared SQL area.
containers Used together with the containers() clause in a container database (CDB root or application root).
cursor_sharing_exact Set the value of cursor_sharing to exact for the duration of the SQL statement.
dbms_stats Undocumented? Found in the shared SQL area.
driving_site Specifies the «location» where a distributed query is executed. This hint is on Jonathan Lewis' list of the Big Five Hints
dynamic_sampling Control some behavior of dynamic optimizer statistics (Generally: /*+ dynamic_sampling(0) */ or for a given table /*+ dynamic_sampling(t 0) */)
dynamic_sampling_est_cdn Undocumented?
enable_parallel_dml, disable_parallel_dml
expand_table, no_expand_table See also the expand table query transformation
fact, no_fact Used in the context of star tranformations to indicate that the hinted table should (not) be considered a fact table.
first_rows(n) Optimize for returning the first n rows. Compare with all_rows and SQL Server's FAST n query hint
fresh_mv See Connor McDonald's youtube video Become a Materialized View Super Hero!
full Access the data in a table with a full table scan, rather than using indexes (compare with the rowid and the various index* hints).
gather_plan_statistics Collect extra metrics during SQL statement execution and make them available in v$sql_plan (?) and v$sql_plan_statistics
gather_optimizer_statistics, no_gather_optimizer_statistics no_gather_optimizer_statistics suppresses gathering statistics during bulk loads (such as create table as select statements). Such statistics can then later be gathered using gather_optimizer_statistics at statement level.
grouping
hash Only applies to tables in a hash cluster. In order to join to tables with a hash join, use use_hash, hash_aj or hash_sj. Compare with use_nl and use_merge.
hash_aj
hash_sj
ignore_row_on_dupkey_index One of the three hints with semantic effect. Compare with change_dupkey_error_index
ignore_where_clause Undocumented hint, found in dynamic sampling queries. Not sure what it does, but does not seem to actually ignore a where clause, at least not if used in a top level query.
index, no_index Use (/*+ index(t) */) or don't use (/*+ noindex(t) */) an index on a table. Index name can additionally be specified: /*+ index(tab ix) */. The index to be used can be described in terms of the columns: /*+ index(tab tab(col_1, col_2…)) */
index_asc
index_combine
index_desc
index_ffs, no_index_ffs
index_join
index_ss, no_index_ss
index_ss_asc
index_ss_desc
inline Undocumented hint. Compare with materialize
inmemory, no_inmemory
inmemory_pruning, no_inmemory_pruning
leading Specify the order of tables when joining them. Compare with ordered.
materialize Undocumented hint. Store the result of a query in a with clause in a global temporary table. Useful if this result is referred to multiple times in the query. Compare with cache and inline. Apparently, an inline view is materialized when it uses rownum.
merge, no_merge Do or don't use complex view merging. These hints are on Jonathan Lewis' list of the Big Five Hints
merge_aj
merge_sj
model_min_analysis
monitor, no_monitor Do (or don't) monitor SQL statement that take less (or more) than 5 seconds. See v$sql_monitor and v$sql_plan_monitor. (Is there also a no_monitoring hint?)
no_monitoring Undocumented? Found in the shared SQL area.
native_full_outer_join, no_native_full_outer_join
nested_table_set_setid Undocumented? Found in the shared SQL area.
nl_aj
nl_sj
nlj_batching, no_nlj_batching
nlj_prefetch, no_nlj_prefetch
no_expand
no_query_transformation Prevents the optimizer from performing query transformation.
no_substrb_pad Using query keywords ci_phrasematch or ci_startswith result in errors
no_sql_tune ?
no_xml_query_rewrite
no_xmlindex_rewrite
no_zonemap
opaque_transform Not documented. Seems to be related to a serial execution at a remote site. See also Doc ID 780503.1 and the column other_tag of plan_table
optimizer_features_enable Which apparently can be set to /*+ optimizer_features_enable(default) */. See also the init parameter optimizer_features_enable.
opt_estimate opt_estimate influences costing and cardinality estimates. This hint supersedes cardinality.
opt_param Sets the value of a a limited set of init parameters for the duration of the SQL statement. For example: /*+ opt_param('_optimizer_adaptive_plans', 'false') */ or /*+ opt_param('_parallel_syspls_obey_force', 'false') */.
ordered Join tables in the order in which they appear in the from clause. Consider using the leading hint instead of ordered. Compare with SQL Server's query hint force order.
parallel, no_parallel
parallel_index, no_parallel_index
pq_concurrent_union, no_pq_concurrent_union Enable/disable concurrent processing of union and union all statements.
pq_distribute Specifies the method with which rows are distributed among producer and consumer query servers.
pq_filter Specifies how rows are processed when filtering correlated subqueries.
pq_skew, no_pq_skew
push_pred, no_push_pred Where to apply join predicates in combination with non-merged view. These hints are on Jonathan Lewis' list of the Big Five Hints
push_subq, no_push_subq push_subq instructs the optimizer to evaluate unmerged subqueries as early as possible. These hints are on Jonathan Lewis' list of the Big Five Hints
px_join_filter, no_px_join_filter
qb_name
relational Undocumented? Found in the shared SQL area.
result_cache, no_result_cache /*+ result_cache */, /*+ result_cache(snapshot = 3600) */ (3600 seconds or one hour), /*+ result_cache(shelflife = 3600) */, /*+ result_cache(syobj=true) */
retry_on_row_change One of the three hints with semantic effect.
rewrite, no_rewrite, norewrite Rewrite a query so that uses one (or more?) materialized views, if possible.
rewrite_or_error Throw ORA-30393: a query block in the statement did not rewrite to be thrown if a query fails to rewrite.
rowid Access a row by its rowid. Compare with full and the various index* hints.
rule The only hint that cause the rule based optimizer (rather than the (cost based) optimizer to be used).
shared shared(1) is the same as noparallel. shared(n) for n > 1 is the same as parallel(n).
star Try to use a star query plan. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.
star_transformation, no_star_transformation
statement_queuing, no_statement_queuing Influences if a statement is queued with parallel statement queuing.
swap_join_inputs, no_swap_join_inputs
unnest, no_unnest Do or don't unnest subqueries. These hints are on Jonathan Lewis' list of the Big Five Hints
use_band, no_use_band
use_concat See also the CONCATENATION plan operation.
use_cube, no_use_cube
use_hash, no_use_hash Compare with /*+ hash */
use_merge, no_use_merge
use_nl, no_use_nl use_nl(X) tells the optimizer to join X using a nested loop. use_nl(X Y) is a shorthand form for specifying use_nl(X) use_nl(Y).
use_nl_with_index
use_weak_name_resl Undocumented? Found in the shared SQL area.
with_plsql with_plsql is not an optimizer hint. See ORA-32034: unsupported use of WITH clause
xmlindex_sel_idx_tbl Undocumented? Found in the shared SQL area.

Global Hints

Not working for ANSI joins

Oracle's documentation has the following 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.

See also

An optimizer hint is not to be confused with SQLcl and SQL Developer select hints.
v$sql_hint, which can be joined to v$sql_feature_hierarchy to display each hint with an «SQL feature hierarchy» (See v$sql_feature_hierarchy).
If the init parameters optimizer_ignore_hints or optimizer_ignore_parallel_hints are set to true, (optimizer related? and) parallel execution related) hints will be ignored.
Erroneous hints are recorded in the column other_xml of the plan table after executing explain plan on a an SQL statement.

Index