SELECT /*+ hint --or-- text */ statement body -- or -- SELECT --+ hint --or-- text statement body |
Where:
- /*, */ - These are the comment delimiters for multi-line comments
- -- - This is the comment delimiter for a single line comment (not usually used for hints)
- + - This tells Oracle a hint follows, it must come immediately after the /*
- hint - This is one of the allowed hints
- text - This is the comment text
Oracle Hint
|
Meaning
|
+
|
Must be immediately after comment indicator, tells Oracle this is a list of hints.
|
ALL_ROWS
|
Use the cost based approach for best throughput.
|
CHOOSE
|
Default, if statistics are available will use cost, if not, rule.
|
FIRST_ROWS
|
Use the cost based approach for best response time.
|
RULE
|
Use rules based approach; this cancels any other hints specified for this statement.
|
Access Method Oracle Hints:
| |
CLUSTER(table)
|
This tells Oracle to do a cluster scan to access the table.
|
FULL(table)
|
This tells the optimizer to do a full scan of the specified table.
|
HASH(table)
|
Tells Oracle to explicitly choose the hash access method for the table.
|
HASH_AJ(table)
|
Transforms a NOT IN subquery to a hash anti-join.
|
ROWID(table)
|
Forces a rowid scan of the specified table.
|
INDEX(table [index])
|
Forces an index scan of the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.
|
INDEX_ASC (table [index])
|
Same as INDEX only performs an ascending search of the index chosen, this is functionally identical to the INDEX statement.
|
INDEX_DESC(table [index])
|
Same as INDEX except performs a descending search. If more than one table is accessed, this is ignored.
|
INDEX_COMBINE(table index)
|
Combines the bitmapped indexes on the table if the cost shows that to do so would give better performance.
|
INDEX_FFS(table index)
|
Perform a fast full index scan rather than a table scan.
|
MERGE_AJ (table)
|
Transforms a NOT IN subquery into a merge anti-join.
|
AND_EQUAL(table index index [index index index])
|
This hint causes a merge on several single column indexes. Two must be specified, five can be.
|
NL_AJ
|
Transforms a NOT IN subquery into a NL anti-join (nested loop).
|
HASH_SJ(t1, t2)
|
Inserted into the EXISTS subquery; This converts the subquery into a special type of hash join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
|
MERGE_SJ (t1, t2)
|
Inserted into the EXISTS subquery; This converts the subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
|
NL_SJ
|
Inserted into the EXISTS subquery; This converts the subquery into a special type of nested loop join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
|
Oracle Hints for join orders and transformations:
| |
ORDERED
|
This hint forces tables to be joined in the order specified. If you know table X has fewer rows, then ordering it first may speed execution in a join.
|
STAR
|
Forces the largest table to be joined last using a nested loops join on the index.
|
STAR_TRANSFORMATION
|
Makes the optimizer use the best plan in which a start transformation is used.
|
FACT(table)
|
When performing a star transformation use the specified table as a fact table.
|
NO_FACT(table)
|
When performing a star transformation do not use the specified table as a fact table.
|
PUSH_SUBQ
|
This causes nonmerged subqueries to be evaluated at the earliest possible point in the execution plan.
|
REWRITE(mview)
|
If possible forces the query to use the specified materialized view, if no materialized view is specified, the system chooses what it calculates is the appropriate view.
|
NOREWRITE
|
Turns off query rewrite for the statement, use it for when data returned must be concurrent and can't come from a materialized view.
|
USE_CONCAT
|
Forces combined OR conditions and IN processing in the WHERE clause to be transformed into a compound query using the
|
NO_MERGE (table)
|
This causes Oracle to join each specified table with another row source without a sort-merge join.
|
NO_EXPAND
|
Prevents OR and IN processing expansion.
|
Oracle Hints for Join Operations:
| |
USE_HASH (table)
|
This causes Oracle to join each specified table with another row source with a hash join.
|
USE_NL(table)
|
This operation forces a nested loop using the specified table as the controlling table.
|
USE_MERGE(table,[table, - ])
|
This operation forces a sort-merge-join operation of the specified tables.
|
DRIVING_SITE
|
The hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.
|
LEADING(table)
|
The hint causes Oracle to use the specified table as the first table in the join order.
|
Oracle Hints for Parallel Operations:
| |
[NO]APPEND
|
This specifies that data is to be or not to be appended to the end of a file rather than into existing free space. Use only with INSERT commands.
|
NOPARALLEL (table
|
This specifies the operation is not to be done in parallel.
|
PARALLEL(table, instances)
|
This specifies the operation is to be done in parallel.
|
PARALLEL_INDEX
|
Allows parallelization of a fast full index scan on any index.
|
Other Oracle Hints:
| |
CACHE
|
Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list when the table is full table scanned.
|
NOCACHE
|
Specifies that the blocks retrieved for the table in the hint are placed at the least recently used end of the LRU list when the table is full table scanned.
|
[NO]APPEND
|
For insert operations will append (or not append) data at the HWM of table.
|
UNNEST
|
Turns on the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to FALSE.
|
NO_UNNEST
|
Turns off the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to TRUE.
|
PUSH_PRED
|
Pushes the join predicate into the view.
|
/*+ ALL_ROWS */ Pone la consulta a costes y la optimiza para que consuma el menor número
de recursos posibles.
/*+ FIRST_ROWS */ Pone la consulta a costes la optimiza para conseguir el mejor tiempo de
respuesta.
/*+ CHOOSE */ Pone la consulta a costes.
/*+ RULE */ Pone la consulta a reglas.
/*+ INDEX( tabla índice ) */ Fuerza la utilización del índice indicado para la tabla indicada
/*+ ORDERED */ Hace que las combinaciones de las tablas se hagan en el mismo orden en
que aparecen en el join.EJEMPLO
SQL> select /*+ index(t1) */ v1 from t1 where n1 >0; Execution Plan ---------------------------------------------------------- Plan hash value: 1429545322 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4000 | 566K| 2523 (1)| 00:00:13 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 4000 | 566K| 2523 (1)| 00:00:13 | |* 2 | INDEX RANGE SCAN | T1_I1 | 4000 | | 10 (0)| 00:00:01 |
