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 ;

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

Related:

SQLcourse.com
SQL reference books

DELETE
EXPLAIN PLAN
INSERT
SELECT
TRUNCATE

UPDATE
DBMS_LOCK
DBMS_SQL

  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


© Copyright SS64.com 1999-2013
Some rights reserved