Initialization Parameters, 8 of 188

ALWAYS_SEMI_JOIN

Parameter type:	String
Syntax	`ALWAYS_SEMI_JOIN` = {`NESTED_LOOPS \| MERGE \| HASH`}
Parameter class:	Static
Default value:	`NESTED_LOOPS`
A semi-join returns rows that match an `EXISTS` subquery. Semi-joins can improve query performance if no index has been defined on the column that constrains the subquery. `ALWAYS_SEMI_JOIN` specifies the type of semi-join that Oracle uses. The system verifies that it is legal to perform a semi-join, and if it is, processes the subquery depending on the value of this parameter.
`NESTED_LOOPS`: Oracle uses a nested loop algorithm. `MERGE`: Oracle uses the sort merge algorithm. `HASH`: Oracle converts a correlated `EXISTS` subquery into a view query block and semi-join, which it evaluates in parallel.
See Also: Oracle8i Concepts for more information on optimizing semi-joins.