Oracle Hints
Version 10.1

Optimizer Approaches
ALL_ROWS The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
SELECT /*+ ALL_ROWS */ empno, ename, sal, job
FROM emp
WHERE empno = 7566;
FIRST_ROWS(n) The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).

This hint causes the optimizer to make these choices:

  • If an index scan is available, the optimizer may choose it over a full table scan.
  • If an index scan is available, the optimizer may choose a nested loops join over a sort-merge join whenever the associated table is the potential inner table of the nested loops.
  •  
    If an index scan is made available by an ORDER BY clause, the optimizer may choose it to avoid a sort operation.
SELECT /*+ FIRST_ROWS (100) */ empno, ename, sal, job
FROM emp
WHERE empno = 7566;
The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following syntax:
  • Set operators (UNION, INTERSECT, MINUS, UNION ALL)
  • GROUP BY clause
  • FOR UPDATE clause
  • Aggregate functions
  • Distinct Operator
RULE ?
 
 
Access Method Hints
Each following hint described in this section suggests an access method for a table.
CLUSTER explicitly chooses a cluster scan to access the specified table
 
FULL Explicitly chooses a full table scan for the specified table
SELECT /*+ FULL(person) */ person_id
FROM person
WHERE person_id > 10;
HASH Explicitly chooses a hash scan to access the specified table
 
INDEX Explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B*-tree, and bitmap indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for bitmap indexes because it is a more versatile hint
SELECT /*+ INDEX(patients ix_gender) */
name, height, weight
FROM patients
WHERE gender = 'M';
INDEX_ASC Explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values
 
INDEX_COMBINE Explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate for the table. If certain indexes are given as arguments, the optimizer tries to use some Boolean combination of those particular bitmap indexes
 
INDEX_DESC Explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in descending order of their indexed values
 
INDEX_FFS Causes a fast full index scan to be performed rather than a full table scan
 
INDEX_JOIN Explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.
 
INDEX_SS  
 
INDEX_SS_ASC  
 
INDEX_SS_DESC  
 
NO_INDEX Explicitly disallows a set of indexes for the specified table. The NO_INDEX hint applies to function-based, B*-tree, bitmap, cluster, or domain indexes.
 
NO_INDEX_FFS ?
 
NO_INDEX_SS ?
 
 
Join Order
The hints in this section suggest join orders:
LEADING ?
 
ORDERED Causes Oracle to join tables in the order in which they appear in the FROM clause.
SELECT /*+ ORDERED */ TAB1.COL1, TAB2.COL2, TAB3.COL3
FROM TAB1, TAB2, TAB3
WHERE TAB1.COL1 = TAB2.COL1
AND TAB2.COL1 = TAB3.COL1;
 
Join Operation
Each hint described in this section suggests a join operation for a table.
NO_USE_HASH ?
 
NO_USE_MERGE ?
 
NO_USE_NL ?
 
USE_HASH (table1, table2) Causes Oracle to join each specified table with another row source with a hash join.
EXPLAIN PLAN SET STATEMENT_ID = 'ABC' FOR
SELECT /*+ USE_HASH (s,i) */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;
USE_MERGE (table1, table2) Causes Oracle to join each specified table with another row source with a sort-merge join.
EXPLAIN PLAN SET STATEMENT_ID = 'ABC' FOR
SELECT /*+ USE_MERGE (s,i) */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;
USE_NL (table1, table2) Causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table.
EXPLAIN PLAN SET STATEMENT_ID = 'ABC' FOR
SELECT /*+ USE_NL (s,i) */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;
USE_NL_WITH_INDEX ?
 
 
Parallel Execution
The hints described in this section determine how statements are parallelized or not parallelized when using parallel execution.
PARALLEL Specify the desired number of concurrent servers that can be used for a parallel operation. The hint applies to the INSERT, UPDATE, and DELETE portions of a statement as well as to the table scan portion. If any parallel restrictions are violated, the hint is ignored.
 
NO_PARALLEL Overrides a PARALLEL specification in the table clause. In general, hints take precedence over table clauses.
 
PQ_DISTRIBUTE Improve parallel join operation performance. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make.
 
PARALLEL_INDEX Specify the desired number of concurrent servers that can be used to parallelize index range scans for partitioned indexes.
 
NO_PARALLEL_INDEX override a PARALLEL attribute setting on an index. In this way you can avoid a parallel index scan operation.
 
 
Several additional hints are also available.
 
Query Transformation
FACT  
 
NO_EXPAND Prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or INLISTS in the WHERE clause. Normally, the optimizer would consider using OR expansion and use this method if it decides the cost is lower than not using it.
 
NO_FACT  
 
NO_QUERY_TRANSFORMATION ?
 
NO_REWRITE Use on any query block of a request. This hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED.
 
NO_STAR_TRANSFORMATION ?
 
NO_UNNEST ?
 
REWRITE Use with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, Oracle uses that view regardless of its cost. Oracle does not consider views outside of the list. If you do not specify a view list, Oracle searches for an eligible materialized view and always uses it regardless of its cost.
 
STAR_TRANSFORMATION Makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a cost-based decision to use the best plan generated without the transformation, instead of the best plan for the transformed query.

Even if the hint is given, there is no guarantee that the transformation will take place. The optimizer will only generate the subqueries if it seems reasonable to do so. If no subqueries are generated, there is no transformed query, and the best plan for the untransformed query will be used regardless of the hint.
 
UNNEST ?
 
USE_CONCAT Forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Normally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.

The USE_CONCAT hint turns off inlist processing and OR-expands all disjunctions, including inlists.
 
 
When you use the APPEND hint for INSERT, data is simply appended to a table. Existing free space in the block is not used. INSERT /*+ APPEND */ INTO <table_name>
(column_list)
VALUES
(list_of_values);

INSERT /*+ APPEND */ INTO <table_name>
(column_list)
<SELECT statement>;
-- demo of the fact that with APPEND indexes are bulk loaded at the end of the insert.

CREATE TABLE t
AS SELECT *
FROM all_objects;

CREATE INDEX ix1_t
ON t(owner, object_name, object_type);

CREATE INDEX ix2_t
ON t(object_id);

INSERT INTO t
SELECT * FROM all_objects;

SELECT used_ublk FROM v$transaction;

ROLLBACK;

insert /*+ APPEND */ into t
SELECT * FROM all_objects;

SELECT used_ublk FROM v$transaction;
NOAPPEND Override append mode.
INSERT /*+ NOAPPEND */ INTO t
SELECT * FROM all_objects;
The DRIVING_SITE hint Forces query execution to be done at a user selected  site rather than at a site selected by Oracle. This hint can be used with either rule-based or cost-based optimization.
SELECT /*+ DRIVING_SITE(dept)*/ e.empno, e.empname
FROM emp e, dept@RSITE d
WHERE e.deptno = d.deptno;
SEMIJOIN  
 
SEMIJOIN_DRIVER  
 
SWAP_JOIN_INPUTS  
 
USE_ANTI  
 
USE_SEMI  
 
ANTIJOIN  
 
CACHE Specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.
 
CARDINALITY  
SELECT *+ cardinality(gtt 999) */ *
FROM gtt;
NOCACHE Specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.
 
MERGE Merge a view on a per-query basis.
 
NO_MERGE Cause Oracle to not merge mergeable views.
 
PUSH_JOIN_PRED Force pushing of a join predicate into the view.
 
NO_PUSH_JOIN_PRED Prevent pushing of a join predicate into the view.
 
PUSH_SUBQ Causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan. Normally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, it will improve performance to evaluate the subquery earlier.

The hint has no effect if the subquery is applied to a remote table or one that is joined using a merge join.

 
 
BITMAP
BUFFER
INLINE
MATERIALIZE
NO_ACCESS
NO_BUFFER
NO_MONITORING
NO_PUSH_GSETS
NO_PUSH_SUBQ
NO_QKN_BUFF
NO_SEMIJOIN
NOREWRITE
OR_EXPAND
 
Undocumented Optimizer Hints
BYPASS_RECURSIVE_CHECK  
 
 BYPASS_UJVC  
 
CACHE_CB  
 
CACHE_TEMP_TABLE  
 
CIV_GB  
 
COLLECTIONS_GET_REFS  
 
CPU_COSTING  
 
CUBE_GB  
 
CURSOR_SHARING_EXACT  
 
DEREF_NO_REWRITE  
 
DML_UPDATE  
 
DOMAIN_INDEX_NO_SORT  
 
DOMAIN_INDEX_SORT  
 
DYNAMIC_SAMPLING Force dynamic sampling of tables where statistics do not exist such as Global Temporary Tables.

If the table is aliased the alias name, not the table name must be used
CREATE GLOBAL TEMPORARY TABLE gtt (
testcol  NUMBER);

SELECT /*+ dynamic_sampling(gtt 2) */ *
FROM gtt;
DYNAMIC_SAMPLING_EST_CDN  
 
EXPAND_GSET_TO_UNION  
 
FORCE_SAMPLE_BLOCK  
 
GBY_CONC_ROLLUP  
 
GLOBAL_TABLE_HINTS  
 
HWM_BROKERED  
 
IGNORE_ON_CLAUSE  
 
IGNORE_WHERE_CLAUSE  
 
INDEX_RRS  
 
LIKE_EXPAND  
 
LOCAL_INDEXES  
 
MV_MERGE  
 
NESTED_TABLE_GET_REFS  
 
NESTED_TABLE_SET_REFS  
 
NESTED_TABLE_SET_SETID  
 
NO_EXPAND_GSET_TO_UNION  
 
NO_FILTERING  
 
NO_ORDER_ROLLUPS  
 
NO_PRUNE_GSETS  
 
NO_STATS_GSETS  
 
NOCPU_COSTING  
 
OVERFLOW_NOMOVE  
 
PIV_GB  
 
PIV_SSF  
 
PQ_MAP  
 
PQ_NOMAP  
 
REMOTE_MAPPED  
 
RESTORE_AS_INTERVALS  
 
SAVE_AS_INTERVALS  
 
SCN_ASCENDING  
 
SELECTIVITY  
 
SKIP_EXT_OPTIMIZER  
 
SQLLDR  
 
SYS_DL_CURSOR  
 
SYS_PARALLEL_TXN  
 
SYS_RID_ORDER  
 
TIV_GB  
 
TIV_SSF  
 
USE_TTT_FOR_GSETS  
 

Related Topics

Outlines

SELECT