First page Back Continue Last page Graphics

Semi-Join Hints


Notes:

In this example I coded the NL_SJ hint to force CBO into a nested loops semi-join (versus the hash semi-join shown in the previous example). In this case, an index range scan is used to search the inner table index, which cannot take advantage of multi-block reads. We see that the logical I/O count increased quite a bit:
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.02 0.01 0 2199
------- ------ -------- ---------- ---------- ----------
total 67 0.02 0.01 0 2199

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