First page Back Continue Last page Graphics
NOT IN Subquery: Anti-Joins
Return rows from with no match in NOT IN subquery
3 kinds
- Hash anti-join
- Nested loops anti-join
- Merge anti-join
Can be very fast method of handling NOT IN
Affected by ALWAYS_ANTI_JOIN parameter
Notes:
Anti-joins are a useful CBO technique for returning rows for which there is no match in the NOT IN subquery.
This feature is controlled with a now undocumented parameter called ALWAYS_ANTI_JOIN. With Oracle9i, this parameter is set to CHOOSE, which means that CBO can choose between anyone of the three anti-join methods.
Refer to the supplied script initialization_parameters_undocumented.sql to display a list of undocumented parameters and their values.