Parameter: always_semi_join

Description

always use this method for semi-join when possible

Supported Versions

This supported parameter was introduced in Oracle 8.0.4

This supported parameter was removed in Oracle 9.0.1

Unsupported Versions

This unsupported parameter was introduced in Oracle 9.0.1

Syntax

_ALWAYS_SEMI_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_semi_join" = CHOOSE;

the statement

    SELECT c2 FROM t1
    WHERE EXISTS
    (
      SELECT NULL FROM t2
      WHERE t2.c1 = t1.c1
    );

generates the execution plan

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

With _ALWAYS_SEMI_JOIN set to HASH

    ALTER SESSION SET "_always_semi_join" = HASH;

the same statement generates the same execution plan

    0 SELECT STATEMENT Optimizer=CHOOSE
    1   0    HASH JOIN (SEMI)
    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_semi_join" = NESTED_LOOPS;

the same statement generates the execution plan

    0 SELECT STATEMENT Optimizer=CHOOSE
    1   0    NESTED LOOPS (SEMI)
    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_semi_join" = MERGE;

the same statement generates the execution plan

    0 SELECT STATEMENT Optimizer=CHOOSE
    1   0    MERGE JOIN (SEMI)
    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_semi_join" = OFF;

the same statement generates the execution plan

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

Note that in Oracle 9.2 (at least) _ALWAYS_SEMI_JOIN = OFF appears to have no effect