Oracle SQL Hints

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

the hint is logically wrong (is invalid or cannot be meaningfully applied), or
the query transformer transforms an SQL statement such that a hint does not make sense (for example leading, ordered or qb_name).

Jonathan Lewis' list of the Big Five Hints

Jonathan Lewis' list of the Big Five Hints is:

merge, no_merge
push_pred, no_push_pred
unnest, no_unnest
push_subq, no_push_subq
driving_site

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. See also the `CONNECT BY PUMP` plan operation.
`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` and the `opt_param` hint.
`opt_estimate`	`opt_estimate` influences costing and cardinality estimates. This hint supersedes `cardinality`.
`opt_param`	Sets the value of 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. See also the `resmgr:pq queued` wait event.
`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.