Re: Theory of joins

From: Peter Sharman <psharman_at_us.oracle.com>
Date: Wed, 27 Jan 1999 11:59:54 -0800
Message-ID: <36AF703A.3FDC479E@us.oracle.com>

Fernando

A semi-join returns rows that match an EXISTS subquery. For example:

select a, b, c
from d
where EXISTS (select 'x'

                        from e
                        where e.a = d.a).

The optimizer uses a nested loop algorithm for these subqueries by default, but you can change this either by setting the ALWAYS_SEMI_JOIN initialization parameter or using MERGE_SJ or HASH_SJ hints in the subquery.

HTH. Pete

"Fernando Sánchez" wrote:

> Hello,
>
> I'd like to know what is called a semi-join. Not an Oracle-specific question.
>
> Thanks
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

--

Regards

Pete

---

Peter Sharman                              Email: psharman_at_us.oracle.com
WISE Course Development Manager            Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education               (650)607 0109 (local)

San Francisco

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

---

Received on Wed Jan 27 1999 - 13:59:54 CST