Oracle in a Nutshell

Miscellaneous hints

The following hints cover other types of optimization choices:

APPEND

Enables direct-path INSERTs if you are running in serial mode (i.e., you are not running Enterprise Edition).

NOAPPEND

Disables direct-path INSERTs for the statement.

CACHE( table )

Places table in the buffer cache at the most recently used end of the least recently used (LRU) list when a full table scan is performed, as for a small lookup table.

NOCACHE

Places table in the buffer cache at the least recently used end of the LRU list when a full table scan is performed. With Oracle9i Release 2, small tables are automatically cached.

UNNEST

Specifies that a subquery is merged with the parent query. This hint is new with Oracle9i.

NO_UNNEST

Turns off UNNEST for specific query blocks. This hint is new with Oracle9i.

PUSH_PRED( table )

Pushes a join predicate into a view. This hint was called PUSH_JOIN_PRED in Oracle8.

NO_PUSH_PRED( table )

Prevents pushing a join predicate into a view. This hint was called NO_PUSH_JOIN_PRED in Oracle8.

PUSH_SUBQ

Forces nonmerged subqueries to be evaluated at the earliest possible step in the optimization process. Appropriate if the subquery is inexpensive and limits the number of rows evaluated.

NO_PUSH_SUBQ

Forces nonmerged subqueries to be evaluated at the earliest possible step in the optimization process. New with Oracle9i.

ORDERED_PREDICATES

Forces optimizer to preserve the order of the predicates in the WHERE clause. This hint is specified in the WHERE clause. New ...