|
Category
|
Hint
|
Available For
|
Notes
|
|
ACC PATH
|
AND_EQUAL
|
/*+ CLUSTER ( tablespec ) */
|
-
|
|
ACC PATH
|
CLUSTER
|
/*+ FULL ( tablespec) */
|
Use on Clustered Tables only
|
|
ACC PATH
|
FULL
|
/*+ HASH ( tablespec ) */
|
Forces a table scan even if there are indexes.
|
|
ACC PATH
|
HASH
|
/*+ INDEX ( tablespec [TAL: indexspec ] ) */
|
Only to tables stored in a table cluster.
|
|
ACC PATH
|
INDEX
|
/*+ INDEX_ASC ( tablespec [TAL: indexspec ] ) */
|
If no indexspec is supplied, the optimizer will try to scan with each avail index.
|
|
ACC PATH
|
INDEX_ASC
|
/*+ INDEX_COMBINE ( tablespec [ indexspec [TAL: indexspec ]...] ) */
|
Essentially the same as INDEX.
|
|
ACC PATH
|
INDEX_COMBINE
|
/*+ INDEX_DESC ( tablespec [ indexspec [TAL: indexspec ]... ] ) */
|
Forces the optimizer to try multiple boolean combinations of indexes.
|
|
ACC PATH
|
INDEX_DESC
|
/*+ INDEX_DESC ( tablespec [ indexspec [TAL: indexspec ]... ] ) */
|
Essentially the same as INDEX.
|
|
ACC PATH
|
INDEX_FFS
|
/*+ INDEX_FFS ( tablespec [ indexspec [TAL: indexspec ]... ] ) */
|
Forces an index scan using specified index(es).
|
|
ACC PATH
|
INDEX_JOIN
|
/*+ INDEX_JOIN ( tablespec [ indexspec [TAL: indexspec ]... ] ) */
|
Indexes used should be based on columns in the where clause.
|
|
ACC PATH
|
INDEX_SS
|
/*+ INDEX_SS ( tablespec [ indexspec [TAL: indexspec ]... ] ) */
|
Useful with composite indexes where the first column is not used in the query, but others are.
|
|
ACC PATH
|
INDEX_SS_ASC
|
/*+ INDEX_SS_ASC ( tablespec [ indexspec [TAL: indexspec ]... ] ) */
|
Essentially the same as INDEX_SS.
|
|
ACC PATH
|
INDEX_SS_DESC
|
/*+ INDEX_SS_DESC ( tablespec [ indexspec [TAL: indexspec ]... ] ) */
|
Essentially the same as INDEX_SS.
|
|
ACC PATH
|
NO_INDEX
|
/*+ NO_INDEX ( tablespec [ indexspec [TAL: indexspec ]... ] ) */
|
Directs the Optimizer not to use specified index(es).
|
|
ACC PATH
|
NO_INDEX_FFS
|
/*+ NO_INDEX_FFS ( [ tablespec [ indexspec [TAL: indexspec ]... ] ) */
|
Directs the Optimizer to exclude a fast full scan of the specified index(es).
|
|
ACC PATH
|
NO_INDEX_SS
|
/*+ NO_INDEX_SS ( tablespec [ indexspec [TAL: indexspec ]... ] ) */
|
Directs the Optimizer to exclude a skip scan of the specified index(es).
|
|
ACC PATH
|
ROWID
|
-
|
-
|
|
JOIN OP
|
HASH_AJ
|
-
|
-
|
|
JOIN OP
|
HASH_SJ
|
-
|
-
|
|
JOIN OP
|
MERGE_AJ
|
-
|
-
|
|
JOIN OP
|
MERGE_SJ
|
-
|
-
|
|
JOIN OP
|
NL_AJ
|
-
|
-
|
|
JOIN OP
|
NL_SJ
|
-
|
-
|
|
JOIN OP
|
NO_USE_HASH
|
/*+ NO_USE_HASH ( tablespec [TAL: tablespec ]... ) */
|
Negates the use of hash joins for the table specified.
|
|
JOIN OP
|
NO_USE_MERGE
|
/*+ NO_USE_MERGE ( tablespec [TAL: tablespec ]... ) */
|
Negates the use of sort-merge joins for the table specified.
|
|
JOIN OP
|
NO_USE_NL
|
/*+ NO_USE_NL ( tablespec [TAL: tablespec ]... ) */
|
Negates the use of nested-loop joins for the table specified.
|
|
JOIN OP
|
USE_HASH
|
/*+ USE_HASH ( tablespec [TAL: tablespec ]... ) */
|
Directive to join each table specified using a hash join.
|
|
JOIN OP
|
USE_MERGE
|
/*+ NO_USE_MERGE ( tablespec [TAL: tablespec ]... ) */
|
Directive to join each table specified using a sort--merge join.
|
|
JOIN OP
|
USE_NL
|
/*+ NO_USE_NL ( tablespec [TAL: tablespec ]... ) */
|
Directive to use a nested-loop join with the specified tables as the inner table.
|
|
JOIN OP
|
USE_NL_WITH_INDEX
|
/*+ USE_NL_WITH_INDEX ( tablespec [ indexspec [TAL: indexspec ]... ] ) */
|
Directive to use a nested-loop join with the specified table as the inner table using the index specified to satisfy at least one predicate.
|
|
JOIN ORDER
|
LEADING
|
/*+ LEADING ( tablespec ) */
|
Directive to join the tables in the order specified.
|
|
JOIN ORDER
|
ORDERED
|
/*+ ORDERED */
|
Directive to join tables in the order found in the FROM clause.
|
|
JOIN ORDER
|
STAR
|
-
|
-
|
|
OPT APPROACH
|
ALL_ROWS
|
/*+ ALL_ROWS */
|
Indicates the goal is overall throughput.
|
|
OPT APPROACH
|
CHOOSE
|
-
|
-
|
|
OPT APPROACH
|
FIRST_ROWS
|
/*+ FIRST_ROWS (integer) */
|
The goal is to retrieve the first row(s) as fast as possible.
|
|
OPT APPROACH
|
RULE
|
/*+ RULE */
|
Used to disable the COST based optimizer.
|
|
OTHER
|
CACHE
|
/*+ CACHE ( tablespec ) */
|
Should be used with the FULL hint. Places data in the most-recently used area of the buffer cache.
|
|
OTHER
|
APPEND
|
/*+ APPEND */
|
Directs the optimizer to INSERT data at the end of the existing table data using direct path I/O.
|
|
OTHER
|
CURSOR_SHARING_EXACT
|
/*+ CURSOR_SHARING_EXACT */
|
Directs the Optimizer to ignore previously parsed SQL that matches, but uses bind variables. Forces the SQL to be parsed unless an exact match is found.
|
|
OTHER
|
DRIVING_SITE
|
/*+ DRIVING_SITE ( tablespec ) */
|
Used when data is joined remotely via DBLink. Normally data at the remote site is returned to the local and joined. This hint directs the optimizer to send the local data to the remote site for resolution of the join.
|
|
OTHER
|
DYNAMIC_SAMPLING
|
/*+ DYNAMIC_SAMPLING ( [TAL: tablespec ] integer ) */
|
Only used in simple SELECT statements with a single table to approximate cardinality if there are no existing statistics on the table.
|
|
OTHER
|
MODEL_MIN_ANALYSIS
|
/*+ MODEL_MIN_ANALYSIS */
|
Used with spreadsheet and model analysis to minimize compile time.
|
|
OTHER
|
NO_PUSH_PRED
|
/*+ NO_PUSH_PRED [TAL: ( tablespec ) ] */
|
Opposite of PUSH_PRED, it directs the Optimizer not to try to push the predicate into the view.
|
|
OTHER
|
NO_PUSH_SUBQ
|
/*+ NO_PUSH_SUBQ ] */
|
Opposite of PUSH_SUBQ, it directs the Optimizer not to try and evaluate the subquery first.
|
|
OTHER
|
NO_UNNEST
|
/*+ NO_UNNEST */
|
Subqueries in the WHERE clause are considered nested. A subquery can be evaluated several times for multiple results in the “parent”. Unnesting evaluates the subquery once and merges the results with the body of the “parent”. This hint directs the Optimizer NOT to unnest.
|
|
OTHER
|
NOAPPEND
|
/*+ NOAPPEND */
|
Directs the Optimizer to utilize existing space in a table and negates parallel processing.
|
|
OTHER
|
NOCACHE
|
/*+ NOCACHE ( tablespec ) */
|
Should be used with the FULL hint. Places data in the least-recently used area of the buffer cache.
|
|
OTHER
|
OPT_PARAM
|
-
|
-
|
|
OTHER
|
ORDERED_PREDICATES
|
-
|
-
|
|
OTHER
|
PUSH_PRED
|
/*+ PUSH_PRED [TAL: ( tablespec ) ] */
|
Used when one of the tables in a join is an in-line view. Forces the predicate used to join the table and the view into the view.
|
|
OTHER
|
PUSH_SUBQ
|
/*+ PUSH_SUBQ *
|
Used with an EXISTS or IN subselect to force evaluation of the subquery rather than the default behavior of the last.
|
|
OTHER
|
UNNEST
|
/*+ UNNEST */
|
Subqueries in the where clause are considered nested. A subquery could be evaluated several times for multiple results in the “parent”. Unnesting evaluates the subquery once and merges results with the body of the “parent”.
|
|
PARALLEL
|
NO_PARALLEL
|
/*+ NO_PARALLEL ( tablespec ) */
|
Directs the Optimizer not to parallel the specified table.
|
|
PARALLEL
|
NO_PARALLEL_INDEX
|
/*+ NO_PARALLEL_INDEX ( tablespec [ indexspec [TAL: indexspec ]... ] ) */
|
Directs the Optimizer not to parallel the specified index(es).
|
|
PARALLEL
|
NO_PX_JOIN_FILTER
|
/*+ NO_PX_JOIN_FILTER (tablespec) */
|
Directs the Optimizer not to try and join bitmap indexes in parallel.
|
|
PARALLEL
|
NOPARALLEL
|
/*+ NOPARALLEL ( tablespec ) */
|
Directs the Optimizer not to parallel the specified table.
|
|
PARALLEL
|
NOPAARALLEL_INDEX
|
/*+ NOPARALLEL_INDEX ( tablespec [ indexspec [TAL: indexspec ]... ] ) */
|
Directs the Optimizer not to parallel the specified index(es).
|
|
PARALLEL
|
PARALLEL
|
/*+ PARALLEL ( tablespec [ integer | TAL:DEFAULT ] ) */
|
Number specifies degrees of parallelism (how many processes).
|
|
PARALLEL
|
PARALLEL_INDEX
|
/*+ PARALLEL_INDEX ( tablespec [ indexspec [TAL: indexspec ]... ] integer | DEFAULT ) */
|
Number specifies degree of parallelism (how many processes).
|
|
PARALLEL
|
PQ_DISTRIBUTE
|
/*+ PQ_DISTRIBUTE( tablespec outer_distribution inner_distribution ) */
|
Used in parallel join operations to indicate how inner and outer tables of the joins should be processed. The values of the distributions are HASH, BROADCAST, PARTITION, and NONE. Only six combinations table distributions are valid.
|
|
PARALLEL
|
PX_JOIN_FILTER
|
/*+ PX_JOIN_FILTER (tablespec) */
|
Directs the Optimizer to try and join bitmap indexes in parallel.
|
|
QUERY TRANS
|
EXPAND_GSET_TO_UNION
|
/*+ EXPAND_GSET_TO_UNION */
|
Performs transformations on queries that have GROUP BY into Unions.
|
|
PARALLEL
|
FACT
|
/*+ FACT ( tablespec ) */
|
In the context of STAR transformation, this table should be considered a FACT table (as opposed to a DIMENSION).
|
|
PARALLEL
|
MERGE
|
/*+ MERGE ( [ view | tablespec ) */
|
Use with either an in-line view that has a Group by or Distinct in it as a joined table, or with the use of IN subquery to “merge” the “view” into the body of the rest of the query.
|
|
PARALLEL
|
NO_EXPAND
|
/*+ NO_EXPAND */
|
Used when OR condition (including IN lists) is present in the predicate to not consider transformation to compound query.
|
|
PARALLEL
|
NO_FACT
|
/*+ NO_FACT ( tablespec ) */
|
In the context of STAR transformation this table should not be considered a FACT table.
|
|
PARALLEL
|
NO_MERGE
|
/*+ NO_MERGE [ ( [ view | TAL:tablespec ) ] */
|
Directs the Optimizer not to “merge” the view into the query.
|
|
PARALLEL
|
NO_QUERY_TRANSFORMATION
|
/*+ NO_QUERY_TRANSFORMATION */
|
Directs the Optimizer not to transform OR, in-lists, in-line views, and subqueries. Try it whenever any of these conditions are present.
|
|
PARALLEL
|
NO_REWRITE
|
/*+ NO_REWRITE */
|
Directs the Optimizer not to use a Materialized View, even if one is available.
|
|
PARALLEL
|
NO_STAR_TRANSFORMATION
|
/*+ NO_STAR_TRANSFORMATION */
|
Directs the Optimizer not to try a Star Transformation.
|
|
PARALLEL
|
NO_XML_QUERY_REWRITE
|
/*+ NO_XML_QUERY_REWRITE */
|
Use only if the query is using XML functionality.
|
|
PARALLEL
|
NO_XMLINDEX_REWRITE
|
/*+ NO_XMLINDEX_REWRITE */
|
Use only if the query is using XML functionality.
|
|
PARALLEL
|
NOFACT
|
/*+ NOFACT ( tablespec ) */
|
In the context of STAR transformation, this table should not be considered a FACT table.
|
|
PARALLEL
|
NOREWRITE
|
/*+ NOREWRITE
|
Directs the Optimizer not to use a Materialized View, even if one is available.
|
|
PARALLEL
|
REWRITE
|
/*+ REWRITE
[ ( view [TAL: view ]... ) ] */
|
Directs the Optimizer to use a Materialized View instead of the underlying tables. Specify REWRITE without additional parameters. Oracle will determine if it can us a Materialized View or not.
|
|
PARALLEL
|
STAR_TRANSFORMATION
|
/*+ STAR_TRANSFORMATION */
|
Directs the Optimizer to try Star Transformation. Only try with a 3 table or more join.
|
|
PARALLEL
|
USE_CONCAT
|
/*+ USE_CONCAT */
|
Used when the OR condition (including IN lists) is present in the predicate to transform the query into a compound UNION ALL.
|
|
REAL TIME
|
MONITOR
|
/*+ MONITOR */
|
Effective only if STATSTICS_LEVEL initialization parameter is either set to ALL or TYPICAL and CONTROL_MANAGEMENT_
PACK_ACCESS is set to DIAGNOSTIC+TUNING. Turns on features of the Oracle Database Tuning Pack.
|
|
REAL TIME
|
NO_MONITOR
|
/*+ NO_MONITOR */
|
See MONITOR hint.
|