HASH JOIN (SEMI)

Description

Perform a hash semi join between two row sources

Versions

This operation is implemented in the following versions

7.3.4
8.0.4
8.0.5
8.0.6
8.1.5
8.1.6
8.1.7
9.0.1
9.2.0
10.1.0
10.2.0

Example

Columns do not require NOT NULL constraints. Tables do not need to be analyzed

Note that the columns involved in the anti-join must both have NOT NULL constraints. Other permutations result in FILTER execution plans

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following table definition 

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

The statement 

    SELECT c2
    FROM t1
    WHERE EXISTS
    (
      SELECT /*+ HASH_SJ */ c1 FROM t2
      WHERE t1.c1 = t2.c1
    );

generates the following 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'