Oracle Internals

Parameter: always_anti_join

Description

always use this method for anti-join when possible

Supported Versions

This supported parameter was removed in Oracle 9.0.1

Version	Parameter Name	Data Type	Session Modifiable	System Modifiable
7.3.4	always_anti_join	String	FALSE	FALSE
8.0.4	always_anti_join	String	FALSE	FALSE
8.0.5	always_anti_join	String	FALSE	FALSE
8.0.6	always_anti_join	String	FALSE	FALSE
8.1.5	always_anti_join	String	FALSE	FALSE
8.1.6	always_anti_join	String	FALSE	FALSE
8.1.7	always_anti_join	String	TRUE	FALSE

Unsupported Versions

This unsupported parameter was introduced in Oracle 9.0.1

Version	Parameter Name	Data Type	Session Modifiable	System Modifiable
9.0.1	_always_anti_join	String	TRUE	FALSE
9.2.0	_always_anti_join	String	TRUE	FALSE
10.1.0	_always_anti_join	String	TRUE	IMMEDIATE
10.2.0	_always_anti_join	String	TRUE	IMMEDIATE
11.1.0	_always_anti_join	String	TRUE	IMMEDIATE

Syntax

_ALWAYS_ANTI_JOIN = OFF | CHOOSE | NESTED_LOOPS | MERGE | HASH

In Oracle 9.2 the default value is CHOOSE

Example

This example was developed on an Oracle 9.2 database using a 2048 byte block size.

The example requires the following table and index definitions

    CREATE TABLE t1 (c1 NUMBER NOT NULL, c2 NUMBER);

    CREATE TABLE t2 (c1 NUMBER NOT NULL, c2 NUMBER);

    ANALYZE TABLE t1 COMPUTE STATISTICS;

    ANALYZE TABLE t2 COMPUTE STATISTICS;

With the default value for _ALWAYS_ANTI_JOIN

    ALTER SESSION SET "_always_anti_join" = CHOOSE;

the statement

    SELECT c2 FROM t1
    WHERE c1 NOT IN
    (
      SELECT c1 FROM t2
    );

generates the execution plan

    0 SELECT STATEMENT Optimizer=CHOOSE
    1   0    HASH JOIN (ANTI)
    2   1      TABLE ACCESS (FULL) OF 'T1'
    3   1      TABLE ACCESS (FULL) OF 'T2'

With _ALWAYS_ANTI_JOIN set to HASH

    ALTER SESSION SET "_always_anti_join" = HASH;

the same statement generates the same execution plan

    0 SELECT STATEMENT Optimizer=CHOOSE
    1   0    HASH JOIN (ANTI)
    2   1      TABLE ACCESS (FULL) OF 'T1'
    3   1      TABLE ACCESS (FULL) OF 'T2'

With _ALWAYS_ANTI_JOIN set to NESTED_LOOPS

    ALTER SESSION SET "_always_anti_join" = NESTED_LOOPS;

the same statement generates the execution plan

    0 SELECT STATEMENT Optimizer=CHOOSE
    1   0    NESTED LOOPS (ANTI)
    2   1      TABLE ACCESS (FULL) OF 'T1'
    3   1      TABLE ACCESS (FULL) OF 'T2'

With _ALWAYS_ANTI_JOIN set to MERGE

    ALTER SESSION SET "_always_anti_join" = MERGE;

the same statement generates the execution plan

    0 SELECT STATEMENT Optimizer=CHOOSE
    1   0    MERGE JOIN (ANTI)
    2   1      SORT (JOIN) 
    3   2        TABLE ACCESS (FULL) OF 'T1'
    4   1      SORT (JOIN) 
    5   4        TABLE ACCESS (FULL) OF 'T2'

With _ALWAYS_ANTI_JOIN set to OFF

    ALTER SESSION SET "_always_anti_join" = OFF;

the same statement generates the execution plan

    0 SELECT STATEMENT Optimizer=CHOOSE
    1   0    FILTER 
    2   1      TABLE ACCESS (FULL) OF 'T1'
    3   1      TABLE ACCESS (FULL) OF 'T2'