There is a large number of possible database hints of which we will describe the most important ones here:
KEYACCESS
The SQL optimizer ignores all secondary indexes. The primary key is selected as an access path. If this hint is applied to a table, the syntax for access hints must be selected.
During the join, all secondary indexes are ignored as the join transfer. The program selects the primary key as the transfer, if possible. If this hint is applied to joins, the syntax for join hints must be selected.
KEYRANGE
The SQL optimizer selects a strategy that selects an area of the primary key as a search range.
INDEXACCESS[(<INDEXNAME>)]
If indexes exist, one of them is used to select the least expensive access path.
If indexes exist, the program uses an index to choose the least expensive join transfer for the join. If you specify an index name, this index is selected even if it was deactivated by the user (ALTER INDEX <index_name> ON <table_name> DISABLE). If this index does not exist in the system, the program selects another index.
If this hint is applied to a table, the syntax for access hints must be selected. If this hint is applied to joins, the syntax for join hints must be selected.
KEYSCAN
The SQL optimizer does not restrict the search area for the primary key (this corresponds to a table scan).
KEYRANGE
The SQL optimizer selects a strategy that selects an area of the primary key as a search range. It automatically switches to the KEYACCESS hint.
INDEXSCAN
The search range for a secondary key is not restricted by the SQL optimizer.
INDEXRANGE
The SQL optimizer selects a strategy that selects an area of the primary key as a search range. It automatically switches to the INDEXACCESS hint.
If you want to use a dedicated index, you must specify the INDEXACCESS hint with an index name.
BUILDRESULT
A resulting set is always generated.
FETCHRESULT
Generation of a resulting set is suppressed if possible.
DISABLE_INVONLY
This hint excludes index-only strategies. All other strategies are still available.
IN_STRATEGY
A possible "IN CONDITION FOR KEY/INDEX" strategy is used.
SUBQ_STRATEGY
A possible "SUBQ CONDITION FOR KEY" or "SUBQ CONDITION FOR INDEX" strategy is used.
TRACE
The trace of the access path component is activated.
ORDERED
A join is executed in the same sequence as specified in <from_table_spec>.
COORDINATOR_JOIN
A join is processed as a "sorted merge" join and does not generate any temporary interim results.
OPERATOR_JOIN
A join is processed as a "nested loop" join and does not generate any temporary interim results.
PARALLEL_SERVER(<unsigned integer>)
The number of parallel servers is configured. 0 means that parallel processing is not used. This overrides the parameter OPTIMIZE_JOIN_PARALLEL_SERVERS.
This hint belongs to the group of join hints.
For example:
select /*+join(;parallel_server(0);) */ *
from t1, t2, t3
where t1.x = t2.y and t2.s = t3.r
NOACCESSPATH
The program ignores both primary keys and indexes as a join transfer. This hint belongs to the group of join hints.
ACCESS=<access hint list>
The program configures the one-table access for the corresponding join table. This access is only significant if the table is to be the first in the join sequence.
This hint belongs to the group of join hints.
BUFFERSIZE(<unsigned integer>)
If you use a nested loop, this hint sets the size of the read buffer to be used.
QUERYREWRITE_OP
You can use this hint to activate QUERYREWRITE for this SQL statement. This corresponds to the parameter setting OPTIMIZE_QUERYREWRITE = OPERATOR.
QUERYREWRITE_STMT
You can use this hint to switch QUERYREWRITE to the old optimization for this SQL statement. This then corresponds to the parameter setting OPTIMIZE_QUERYREWRITE = STATEMENT.
QUERYREWRITE_NO
You can use this hint to deactivate QUERYREWRITE for this SQL statement. This then corresponds to the parameter setting OPTIMIZE_QUERYREWRITE = NO