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@xxxxxxxxxxxxxxx <mailto:mark.bobak@xxxxxxxxxxxxxxx> www.proquest.com <http://www.proquest.com/> www.csa.com <http://www.csa.com/> ProQuest...Start here. ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Herring Dave - dherri Sent: Wednesday, June 06, 2007 3:31 PM To: oracle-l@xxxxxxxxxxxxx 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. ************************************************************************ *