RE: Concurrent table joining in Oracle

Hi Dave,  

I think you can smack the developer...;-)  

Oracle will only ever join two row sources, using one join method, at a time. A row source consititutes of a table, index, or previously existing data set from a previous join in a preceeding step of execution.  

-Mark  

--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
734.997.4059  or 800.521.0600 x 4059
mark.bobak_at_il.proquest.com <mailto:mark.bobak_at_il.proquest.com> 
www.proquest.com <http://www.proquest.com/> 
www.csa.com <http://www.csa.com/> 

ProQuest...Start here. 

 

________________________________

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Herring Dave -
dherri
Sent: Wednesday, June 06, 2007 3:31 PM
To: oracle-l_at_freelists.org
Subject: Concurrent table joining in Oracle



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?

 

Thanks.

 

Dave

 

 

___________________________________

David C. Herring, DBA  |   A c x i o m  Delivery Center Organization

630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax

 



************************************************************************

*

The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be
legally privileged.

If the reader of this message is not the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank you.


************************************************************************

*





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