Oracle in a Nutshell

Join hints

The following hints suggest a join order or a type of join operation:

ORDERED

Forces tables to be joined in the order they are listed in the FROM clause.

STAR

Forces a star query plan.

USE_NL( table [ table . . .])

Forces the use of a nested loop join, with the specified table(s) as the inner table.

USE_MERGE( table [ table . . .])

Forces the use of a sort merge join, with the specfied table(s) as the driver.

USE_HASH( table [ table . . .])

Specifies the use of a hash join with the specified table.

DRIVING_SITE( table )

Forces the join to be performed at the site containing the specified table.

LEADING( table )

Specifies the table to be used as the first table in a join. New with Oracle9i.

HASH_AJ, MERGE_AJ, NL_AJ

Used in a NOT IN subquery to specify a hash, merge, or nested loop antijoin. The NL_AJ hint is new with Oracle9i.

HASH_SJ, MERGE_SJ, NL_SJ

Specifies a hash, merge, or nested loop semijoin. The NL_SJ hint is new with Oracle9i.