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.
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 */
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
SQL reference books
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