| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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
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-lReceived on Wed Jun 06 2007 - 14:30:31 CDT
 |
 |