Concurrent table joining in Oracle
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 6 Jun 2007 14:30:31 -0500
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.
*************************************************************************
Other related posts:
