Hi!
I don't know the exact algorithm either, but check
the execution plans:
SQL> set autot trace exp
SQL> select name from obj$
2 where exists (select 1 from tab$ where obj$.obj# = tab$.obj#);
SQL> select name from obj$
2 where exists (select 1 from tab$ where obj$.obj# = tab$.obj#);
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'OBJ$'
3 1 TABLE ACCESS (CLUSTER) OF 'TAB$'
4 3 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'OBJ$'
3 1 TABLE ACCESS (CLUSTER) OF 'TAB$'
4 3 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE)
SQL> select /*+ HASH_SJ */ name from
obj$
2 where exists (select 1 from tab$ where obj$.obj# = tab$.obj#);
2 where exists (select 1 from tab$ where obj$.obj# = tab$.obj#);
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=8168 Bytes=3
51224)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=8168 Bytes=3
51224)
1
0 HASH JOIN (SEMI) (Cost=50 Card=8168 Bytes=351224)
2 1 TABLE ACCESS (FULL) OF 'OBJ$' (Cost=25 Card=8168 Bytes=2
45040)
2 1 TABLE ACCESS (FULL) OF 'OBJ$' (Cost=25 Card=8168 Bytes=2
45040)
3
1 VIEW OF 'VW_SQ_1' (Cost=25 Card=8168
Bytes=106184)
4 3 TABLE ACCESS (FULL) OF 'TAB$' (Cost=25 Card=8168 Bytes
=106184)
4 3 TABLE ACCESS (FULL) OF 'TAB$' (Cost=25 Card=8168 Bytes
=106184)
You see in first execution plan we use FILTER for
getting our results. FILTER has several meanings, but in this case it could be
bounded nested loop (e.g. stopping for particular driving key when exists
condition is satisfied). That means, for every row in obj$ we have to check
separately whether there is any matching rows, this means lots of logical
IOs.
In second execution plan however, a hash join is
done between obj$ and distinct obj# key values from tab$
(that's the reason why we're calling it a semi-join). Semi-join allow us to replace big amount of small nested loop
IOs with few big reads and a hash join instead.
Note that I don't know whether Oracle actually
behaves this way, I'm just telling you how I think it is done, based
only on brief analysis...
Cheers,
Tanel.
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L"
<[EMAIL PROTECTED]>
Sent: Monday, November 03, 2003 5:04
PM
Subject: anyone have success with
semi-joins?
>
> Now logically you have to return the sub-query repeatedly, this would only come into play if there are duplicates right?
>
> anyone know the algorithm for the semi-join? Not just the result? I cant find it anywhere.
>
> anyone ever hint this or let oracle decide?
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <[EMAIL PROTECTED]
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>