SELECT Statement Hints SYNTAX

SELECT Statement Hints

An SQL Hint is a type of comment added to a Select, Insert, Update or Delete SQL statement.
The hint passes instructions to the optimizer as a suggested execution plan for the SQL statement.
In the majority of cases the Cost Based Optimiser (when properly setup) will perform better than manually hinted SQL.

Hints:

Optimizer Mode
  ALL_ROWS            Use the cost-based optimiser for this query.
  FIRST_ROWS(n)       Cost-based, return the first n rows most efficiently.
  CHOOSE              Use cost-based optimisation only if stats are available.
  RULE                Use the Rule-based optimiser for this query.

Index and Cluster Hints
  AND_EQUAL (table index [index] [index])   Merge scans on several single-column indexes
  CLUSTER(table)                 Choose a cluster scan (for clusters only)
  FULL(table)                    Full table scan
  HASH(table)                    Hash scan (for clusters only)
  INDEX(table index..)          Index scan for the table
  NO_INDEX(table index..)       Don't choose an index scan for the table
  INDEX_ASC(table index..)      Index scan & where appropriate scan ascending
  INDEX_DESC(table index..)     Index scan & where appropriate scan decending
  INDEX_COMBINE(table index..)  Bitmap index scan for the table
  INDEX_FFS(table index..)      Fast full index scan
  ROWID

Join Order Hints
  ORDERED             Join tables in the order in which they appear in the WHERE clause.
                      Oracle recommend that you use the LEADING hint, which is more versatile than the ORDERED hint.
  ORDERED_PREDICATES  Preserve the order of predicate evaluation, except where used as an index key
  STAR

Joins
  DRIVING_SITE
  HASH_SJ             Hash semi-join (Exists subquery)
  MERGE_SJ            Sort merge semi-join (Exists subquery)
  NL_SJ               Nested loop semi-join (Exists subquery)
  LEADING(table)      Use the table as the first table in the join order.
  USE_HASH
  USE_MERGE
  USE_NL
   
Parallel Execution Hints
  PARALLEL(table int|DEFAULT...)        Desired number of concurrent servers for a parallel operation
  NOPARALLEL                            Override a PARALLEL specification in the table clause
  PARALLEL_INDEX(table index int|DEFAULT...)  Parallelize index range scans for partitioned indexes.
  PQ_DISTRIBUTE(table, outer_dist inner_dist) Specify how joined rows should be distributed among query servers
  NOPARALLEL_INDEX      Avoid a parallel index scan

Query Transformation Hints
  EXPAND_GSET_TO_UNION
  NO_EXPAND             Do not consider expanding OR or IN-List
  FACT(table)           Consider the hinted table a fact table
  NOFACT(table)         Don't consider the hinted table a fact table
  MERGE(table)          Merge a view definition into the accessing statement
                        complex merging of subqueries is not normally considered by the optimiser
  NO_MERGE              Do not consider merging views
  REWRITE
  NOREWRITE             Disable query rewrite for the query block (disables function-based indexes)
  STAR_TRANSFORMATION
  USE_CONCAT

Other Hints
  APPEND                Enable direct-path INSERTs
  NOAPPEND              Enable conventional INSERTs
  CACHE (table)         Cache in most recently used end of the LRU
  NOCACHE (table)       Cache in least recently used end of the LRU (default)
  CURSOR_SHARING_EXACT
  DYNAMIC_SAMPLING
  NESTED_TABLE_GET_REFS
  UNNEST
  NO_UNNEST             Turn off unnesting for specific subquery blocks.
  PUSH_PRED
  NO_PUSH_PRED          Prevent pushing a join predicate into the view
  PUSH_SUBQ
  NO_PUSH_SUBQ          Evaluate the non-merged subquery last.

Use any of the above hints /*+ like this */

Examples

SELECT /*+ some_hint */ column FROM my_table;

UPDATE /*+ some_hint */ my_table SET column = value;

INSERT /*+ some_hint */ INTO my_table value,value ;