Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Question about semi-joins?
From Oracle (version 9.2):
"Example 2-19 Querying with a Filter Created by a Subquery
SELECT h.order_number
FROM so_headers_all h
WHERE h.open_flag = 'Y'
AND EXISTS (SELECT null FROM so_lines_all l
WHERE l.header_id = h.header_id AND l.revenue_amount > 10000);
Plan
Read also a bit about joins in the performance manual, on tahiti.oracle.com. In a nutshell, the CBO will usually go for a hash join if it thinks that many rows will be returned, and the hash-area is large enough to handle them. A merge sort is usually recommended in the same situation, but from the RBO. Otherwise, nested loops are performed.
Daniel
> 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 - 15:34:30 CST