Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Question about semi-joins?

Re: Question about semi-joins?

From: Daniel Roy <danielroy10junk_at_hotmail.com>
Date: 4 Nov 2003 13:34:30 -0800
Message-ID: <3722db.0311041334.5ce99c2d@posting.google.com>


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



SELECT STATEMENT
 FILTER
  TABLE ACCESS BY INDEX ROWID SO_HEADERS_ALL    INDEX RANGE SCAN SO_HEADERS_N2
  TABLE ACCESS BY INDEX ROWID SO_LINES_ALL    INDEX RANGE SCAN SO_LINES_N1 In this example, for every row meeting the condition of the outer query, the correlated EXISTS subquery is executed. If a row meeting the condition is found in the so_lines_all table, then the row from so_headers_all is returned."

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US