Question about semi-joins?

From: Ryan Gaffuri <rgaffuri_at_cox.net>
Date: 4 Nov 2003 07:07:08 -0800
Message-ID: <1efdad5b.0311040707.1fabfe02@posting.google.com>

anyone have a link to an algorithm for this? When I search google, I get alot of hits, but no algorithm. doesnt have to be oracle specific, i just want a 'basic' idea of how it works.

Also, does anyone understand how the semi-join works? Supposedly it will take an exists statement and set it up so the sub-query only runs onces? How does that work? Doesn't it have to run for each row? or if there is a duplicate then only run onc?

anyway to tune semi-joins so oracle decides whetehr to hash, merge, or use a nested loop? The always_semi_join parameter can be set to a default value. Then apparently you have to hint oracle to do a different type. Anyway to gather statistics so oracle can decide what type of semi-join to do for you?

also, a semi_nl sounds like the same thing as the stand where exists algorithm? How does it differ? Received on Tue Nov 04 2003 - 09:07:08 CST