Performs a nested loops semi join between two row sources
This operation was introduced in Oracle 9.0.1
This operation is implemented in the following versions
|
Columns do not need NOT NULL constraints. At least one table must be analyzed
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,c2 NUMBER); CREATE TABLE t2 (c1 NUMBER,c2 NUMBER);
In Oracle 9.2 the table must be analyzed:
ANALYZE TABLE t1 COMPUTE STATISTICS;
The statement
SELECT c2 FROM t1 WHERE EXISTS ( SELECT /*+ NL_SJ */ c1 FROM t2 WHERE t1.c1 = t2.c1 );
generates the following 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'