First page Back Continue Last page Graphics

EXISTS Subquery: Semi-Joins


Notes:

An efficient method of handling an EXISTS correlated subquery is the use of the semi-join join technique. With this join method, and similar to the conventional nested loops or hash join, Oracle probes the inner table looking for a match for the outer row. However, the difference is that once a match is found, a single outer row is returned; even if there might be more matches, the search stops and the single outer row is returned.
In this example, we see that CBO used an index fast full scan (with multi-block reads) to scan the inner table’s index, resulting in only 1216 logical I/O’s:
call count cpu elapsed disk query
------- ------ -------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0
Execute 1 0.00 0.00 0 0
Fetch 65 0.42 0.40 0 1216
------- ------ -------- ---------- ---------- ----------
total 67 0.42 0.41 0 1216

Refer to the supplied script semi_joins.sql for a copy of this demonstration.