Re: Concurrent table joining in Oracle

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Wed, 6 Jun 2007 23:34:33 +0200
Message-ID: <4ef2fbf50706061434n4c5f9844p651723693b4608dc@mail.gmail.com>

Single Oracle processes are single-threaded (they're actually OS threads in Windows),
in order to execute the three joins in parallel Oracle would need to "spawn" three
slave processes, and that of course doesn't happen for a serial execution.

"Oracle will only perform one join of 2 table" can be misleading; it's true that it doesn't exist a Row Source Operation (RSO) that takes three tables as input,
so a three table join is performed by having a RSO join two tables (a,b), and
another RSO joining the result to the third table (c). But (*in general*) it does not
mean that the first RSO will start, process its two inputs, and stop, and then the second
will start - actually the first can start, produce some intermediate "rows", then the second
starts and consume these "rows", then the first resume execution, etcetera.

To illustrate, consider this example:

drop table a;
drop table b;
drop table c;

create table a (id_a int);
insert into a values (1);
insert into a values (2);
create table b (id_a int, id_b int);
insert into b values (1, 10);
insert into b values (2, 20);

create table c (id_b int, id_c int);
insert into c values (10, 100);
insert into c values (20, 200);

commit;
set arraysize 1
set autotrace on
select /*+ ordered use_nl (b,c) */ a.id_a, b.id_b, c.id_c from a, b, c
where a.id_a = b.id_a * (2-a.id_a)/(2-a.id_a) and b.id_b = c.id_b;

ID_A ID_B ID_C
---------- ---------- ----------

1 10 100
ERROR:
ORA-01476: divisor is equal to zero

|   0 | SELECT STATEMENT    |      |     2 |   130 |    11   (0)| 00:00:01 |
|   1 |  NESTED LOOPS       |      |     2 |   130 |    11   (0)| 00:00:01 |
|   2 |   NESTED LOOPS      |      |     2 |    78 |     7   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| A    |     2 |    26 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| B    |     1 |    26 |     2   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | C    |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):

4 - filter("A"."ID_A"="B"."ID_A"*(2-"A"."ID_A")/(2-"A"."ID_A")) 5 - filter("B"."ID_B"="C"."ID_B")

You see - the ORA-01476 is generated as soon as the "second" row (id_a=2) of table a is considered in the join filter predicate. But you get back the row
with id_a = 1 before the error, which means that the RSO with Id = 2 (that joins
a and b) started, produced one "row", stopped, passed the "row" to the RSO with Id=1 (that joins the intermediate result to c), the single row was returned,
then Id = 2 started again, and got killed by the poisonous second row.

It could be said that in a given instant, Oracle is executing exactly one join (RSO),
but it can flip-flop between the two joins in a given time interval. So they are "all
active at the same time" much like as N programs are "active at the same time"
in my single-CPU machine.

HTH - Al

On 6/6/07, Herring Dave - dherri <Dave.Herring_at_acxiom.com> wrote:
>
> Folks,
>
>
>
> A developer had me review code today that performed a UNION ALL of 3 very
> similar 2-table joins. I suggested a rewrite to perform the join only once
> (one table is 42 million rows) and instead OR any non-key filter values and
> use CASE/DECODE for any result set differences. Their response was "I
> always thought UNION and UNION ALL perform each UNION-ed set in parallel".
> In other words, their assumption was that if 3 queries were UNION ALL-ed
> together, Oracle would perform all 3 joins at the same time.
>
>
>
> My understanding has always been that Oracle will only perform one join of
> 2 tables at one time, no more, regardless. I proved for the example above
> that this was the case (using V$SEGMENT_STATISTICS over time), but now
> before I send an email to all our developers with a general statement that
> Oracle will never perform more than one 2-table join at a time, I thought
> I'd check with you all. Does Oracle ever perform more than one 2-table join
> at a time? Should I smack our developers or myself on this?
>
>
>
>

-- 
Alberto Dell'Era
"dulce bellum inexpertis"

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 06 2007 - 16:34:33 CDT