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.

Hints:

Optimizer Mode 
  ALL_ROWS            Cost-based optimiser
  FIRST_ROWS(n)       Cost-based optimiser, return the first n rows most efficiently
  CHOOSE              Choose between the rule-based and cost-based
  RULE                Rule-based optimiser

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.
  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 */

e.g.

SELECT /*+ some_hint */ column FROM my_table;

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

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

Life is like a 10 speed bicycle. Most of us have gears we never use. - C. Schultz

Related Commands:

Outer Joins
An online SQL Tutorial
SQL reference books

DELETE
EXPLAIN PLAN
INSERT
SELECT
TRUNCATE
UPDATE
DBMS_LOCK
DBMS_SQL

Related Views:

  DBA_SNAPSHOTS        ALL_SNAPSHOTS        USER_SNAPSHOTS 
  DBA_ALL_TABLES       ALL_ALL_TABLES       USER_ALL_TABLES
  DBA_TABLES           ALL_TABLES           USER_TABLES         TAB
  DBA_VIEWS            ALL_VIEWS            USER_VIEWS
                                                                DICTIONARY
                                                                DICT_COLUMNS