|
ERGE |
Do not use merge (from Oracle 10g) |
| USE_HASH |
The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join. The syntax of the USE_HASH hint is USE_HASH(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join. |
| NO_USE_HASH |
Do not use hash (from Oracle 10g) |
| Hints for Parallel Execution |
| PARALLEL |
The PARALLEL hint allows you to specify the desired number of concurrent query servers that can be used for the query. The syntax is PARALLEL(table number number). The PARALLEL hint must use the table alias if an alias is specified in the query. The PARALLEL hint can then take two values separated by commas after the table name. The first value specifies the degree of parallelism for the given table, the second value specifies how the table is to be split among the instances of a parallel server. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters (described in a later section) to determine the default degree of parallelism. |
| NOPARALLEL / NO_PARALLEL |
The NOPARALLEL hint allows you to disable parallel scanning of a table, even if the table was created with a PARALLEL clause. In Oracle 10g this hint was renamed to NO_PARALLEL. |
| PQ_DISTRIBUTE |
The PQ_DISTRIBUTE hint improves the performance of parallel join operations. 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. |
| NO_PARALLEL_INDEX |
The NO_PARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation. |
| Additional Hints |
| APPEND |
When the APPEND hint is used with the INSERT statement, data is appended to the table. Existing free space in the block is not used. If a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces generation of redo. |
| NOAPPEND |
Overrides the append mode. |
| CACHE |
The CACHE hint specifies that the blocks retrieved for the table in the hint 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. In the following example, the CACHE hint overrides the table default caching specification. |
| NOCACHE |
The NOCACHE hint 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. |
| PUSH_PRED |
The PUSH_PRED hint forces pushing of a join predicate into the view. |
| NO_PUSH_PRED |
The NO_PUSH_PRED hint prevents pushing of a join predicate into the view. |
| PUSH_SUBQ |
The PUSH_SUBQ hint causes nonmerged subqueries to be eva luated at the earliest possible place in the execution plan. |
| NO_PUSH_SUBQ |
The NO_PUSH_SUBQ hint causes non-merged subqueries to be eva luated as the last step in the execution plan. |
| QB_NAME |
Specifies a name for a query block. (from Oracle 10g) |
| CURSOR_SHARING_EXACT |
Oracle can replace literals in SQL statements with bind variables, if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint causes this behavior to be switched off. In other words, Oracle executes the SQL statement without any attempt to replace literals by bind |