Re: Concurrent table joining in Oracle

  • From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
  • To: Dave.Herring@xxxxxxxxxx
  • Date: Wed, 6 Jun 2007 23:34:33 +0200

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

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   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@xxxxxxxxxx> 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"

Other related posts: