Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 8909 invoked from network); 6 Jun 2007 16:04:11 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 6 Jun 2007 16:04:11 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EE6E16F108A; Wed, 6 Jun 2007 17:02:15 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 12201-08; Wed, 6 Jun 2007 17:02:15 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 63ABE6F10D7; Wed, 6 Jun 2007 17:02:15 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 06 Jun 2007 16:21:37 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5CDC66EF7E5 for ; Wed, 6 Jun 2007 16:21:37 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07396-01 for ; Wed, 6 Jun 2007 16:21:37 -0400 (EDT) Received: from mx01.il.proquest.com (mx00.il.proquest.com [192.195.245.51]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0B50A6EF688 for ; Wed, 6 Jun 2007 16:21:36 -0400 (EDT) X-ASG-Debug-ID: 1181161410-654900170000-JbYmij X-Barracuda-URL: http://spam.il.proquest.com:8000/cgi-bin/mark.cgi X-Barracuda-Connect: aabo-exchange05.bos.il.pqe[172.24.3.61] X-Barracuda-Start-Time: 1181161410 X-ASG-Whitelist: Client Received: from aabo-exchange05.bos.il.pqe (aabo-exchange05.bos.il.pqe [172.24.3.61]) by mx01.il.proquest.com (Spam Firewall) with ESMTP id F043BD02BC4F; Wed, 6 Jun 2007 16:23:30 -0400 (EDT) Received: from AABO-EXCHANGE02.bos.il.pqe ([172.24.3.202]) by aabo-exchange05.bos.il.pqe with Microsoft SMTPSVC(6.0.3790.3959); Wed, 6 Jun 2007 16:23:30 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/related; type="multipart/alternative"; boundary="----_=_NextPart_001_01C7A878.8C19FCEA" X-ASG-Orig-Subj: RE: Concurrent table joining in Oracle Subject: RE: Concurrent table joining in Oracle Date: Wed, 6 Jun 2007 16:23:27 -0400 Message-ID: In-Reply-To: <7ED53A68952D3B4C9540B4EFA5C76E36034C1B4B@CWYMSX04.Corp.Acxiom.net> X-MS-Has-Attach: yes X-MS-TNEF-Correlator: Thread-Topic: Concurrent table joining in Oracle From: "Bobak, Mark" To: , X-OriginalArrivalTime: 06 Jun 2007 20:23:30.0692 (UTC) FILETIME=[8C4FA440:01C7A878] X-Barracuda-Virus-Scanned: by Barracuda Spam Firewall at il.proquest.com X-archive-position: 49533 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Mark.Bobak@il.proquest.com Precedence: normal Reply-to: Mark.Bobak@il.proquest.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------_=_NextPart_001_01C7A878.8C19FCEA Content-Type: multipart/alternative; boundary="----_=_NextPart_002_01C7A878.8C19FCEA" ------_=_NextPart_002_01C7A878.8C19FCEA Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi Dave, =20 I think you can smack the developer...;-) =20 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. =20 -Mark =20 -- 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@il.proquest.com =20 www.proquest.com =20 www.csa.com =20 ProQuest...Start here.=20 =20 ________________________________ From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Herring Dave - dherri Sent: Wednesday, June 06, 2007 3:31 PM To: oracle-l@freelists.org Subject: Concurrent table joining in Oracle Folks, =20 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. =20 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? =20 Thanks. =20 Dave =20 =20 ___________________________________ 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 =20 ************************************************************************ * 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=20 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. ************************************************************************ * ------_=_NextPart_002_01C7A878.8C19FCEA Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Hi=20 Dave,
 
I=20 think you can smack the developer...;-)
 
Oracle=20 will only ever join two row sources, using one join method, at a = time.  A=20 row source consititutes of a table, index, or previously existing = data set=20 from a previous join in a preceeding step of = execution.
 
-Mark
 
--
Mark J.=20 Bobak
Senior Database Administrator, System & = Product=20 Technologies
ProQuest
789 E. Eisenhower, Parkway, = P.O. Box=20 1346
Ann Arbor MI 48106-1346
734.997.4059  or = 800.521.0600 x=20 4059
mark.bobak@il.proquest.com
www.proquest.com
www.csa.com

ProQuest...Start here.

 

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

Folks,

 

A developer had me review = code today=20 that performed a UNION ALL of 3 very similar 2-table joins.  I = suggested a=20 rewrite to perform the join only once (one table is 42 million rows) and = instead=20 OR any non-key filter values and use CASE/DECODE for any result set=20 differences.  Their response was "I always thought UNION and UNION = ALL=20 perform each UNION-ed set in parallel".  In other words, their = assumption=20 was that if 3 queries were UNION ALL-ed together, Oracle would perform = all 3=20 joins at the same time.

 

My understanding has = always been=20 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=20 V$SEGMENT_STATISTICS over time), but now before I send an email to all = our=20 developers with a general statement that Oracle will never perform more = than one=20 2-table join at a time, I thought I'd check with you all.  Does = Oracle ever=20 perform more than one 2-table join at a time?  Should I smack our=20 developers or myself on this?

 

Thanks.

 

Dave

 

___________________________________

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

630-944-4762 office=20 | 630-430-5988 wireless | 630-944-4989=20 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=20
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.
*************************************************************************=
------_=_NextPart_002_01C7A878.8C19FCEA-- ------_=_NextPart_001_01C7A878.8C19FCEA Content-Type: image/gif; name="image001.gif" Content-Transfer-Encoding: base64 Content-ID: <484042020@06062007-35CE> Content-Description: image001.gif Content-Location: image001.gif R0lGODlhmwAeAPQAAAYUdvz8/E9low8gf3WLu63O46iuzTRFktPl9MvY7I6dvIqszv7+98PJ3uP3 /eHp9oaYyv796/b+/ePn7Ji427O81tbm3mZ6sPf78/X3++Tx6v7+3P7z8/76/ezx9v///ywAAAAA mwAeAAAF/+AnjmRpnmiqrmzrvnCcBnStBlkefHU/9rQMkGbK6U7A0lCEkzmfKUag80EIDlisQPPp 2BKEw2B8uGTPBob1fBA0rtmL58PIVMzjwQGCkAYYYWcXPFVsBw1gBAl0UI1PDF0fBgCUlQANXRgM EgoDlHkAAwKeoZQHCRehnp5ypJ4HXFaVea8KfwwHlgAHDyKTn5QGCgIECoSOyC9TIqm0rDx1Ap/O AxUEqmO7zrseGrnZBx4I39TZFxIY5NkGItK0AAoGF8XHyfYqDFQe6uB/H9flxhiQwA9bngofJogB JysgKAUSVoES8OFBwXMVKnjQdK8jinySyoXC1ICaLnYlHf8CGMRAoSoAApq9mwauwkJnHiqYHCSC AQYqHoOS2CFzm7GitSoMY/fPIChekCxiu5ltgIIK7mhRBWXNKcwfQsOScAlqojdsMOeIkJChQx1+ BwlJdVhtRFa6euDClCC2L6EKaKk12MpuWQAJIFO+u8Agwo65Ig9kAKkTb1WT4fyKJWoQYLYwZRHu +BPg54fK7wQ0pgLZpABNblFbjtxLc9h9E2XnfUdgBAMp+uAKlCsc3ARGEJzOfrrItlDZAAh4cwYX QoIHCQxgahq5T8Xi5h5IsKmyDdo2zpo77wjIYJq7ekzGB4CS7teW5HYVRK8cVDG0F3RCy3ajrYfM Pi8dYMD/KO9godwnA4G3zgfTZSOAASYph4V/CThjwCRcHbODgY0o5smF/RFAl1IG6XfeA2eB44EC NGGGISgQfKBOTu8wJQWJjnjmCQRIZaNAcv2ZERgCyaUGGTcfdPLgLgk0gJYxAH0F2EPQANlIALlg Y5NXu5w2Sin9ecIOBvBZFeYrE0CCVTbTEHDcL5WglCcDCejCEgYjeilDAB8WWsEDFRSq6IcZ8JCA AgREesc2r7n1wKIGZKRoBZMdlsGjFxBjwHG/YSXAqQIkwIAHBJx63ASonqpABxhI4JYUgEpQmhc0 6Doaab3S4BMPXkihaxdTbPKHrbXeko+wU9jwEQY3BDpC/4dOWUDHbyZA4tsHmiTrbU/ARRBBCR1w MK4I1JYQwW9u0dGBW8tqYisThPjx2y1U/PabJv6Su+8t1EJi8L/W8jDEwsIqLK9PBRkzgrk/dHDu MRuIcDEJDnywgbcMcNBToyJgAklzkIjssQgd8DXZCAWs9cHFQLE7c08fqKUyt2up1/HMkMzBFwLX 9WTtEsL6UcPA+VBBY2oBELBgbaeGWjJC2VVBEQU5DjDYBTEnsAABHStAQUwJUDDABQP8gwW4g1AQ szcfmHFkAx4McM0DFLBDAAUfgNEAJhe8IcDZAtzRjsgEDK5HBeMoIPkHBRRTxgcLmFGAer75++++ nnP76/8O2DqDSQGHU3THAiKInJyOFNH4QQOVYyA1uIEvIM3sAjRqZcmHB+4GADoOsvsHFBEwDhcO HEL8Bay/8bsACCxAO+sfEK/juRBQcMAI1nzgAPGoIy+CBxQA1ku7M6Cwg+cSwAdAjpB8//0AEqQC +GnfE4M81x/wxOzu86gGXGAQBaASAm5GpOz9I1UxAUQDevM9eSyiUdbASgEAh4r/LQABmcreAPYh ggOc62y9WNsEVsIACAjgAYP4HrgegIGfQWEH4YpSizIDicodsDc8gQQBCnAABRRAAQdAAAACUICI UGB/VRgb8iCwAMApgCd1A1yrpAMLCrSqT+Z7Ihbn8QD/GjUKFQWwQCokUQGxlbAdIqDAAnL0Dwjk qAAL6BPrKMIIe5AmcHBpQD44wBcALOASASQA6zhAPOKJoQtEZB2GBJDH07QqhXYs4QXGJ4/jPJJ4 12CG95CHADMQRJHGAABCBmOAHIFyARYYAAQsYMhxgO0CpoqIAQ6AR/tREFkJe4IQaIAgXRDAJ41B jAQ0wIUIJCICHOjDIviGnAdsYJmZs8AmEGCBDJyLm5Fw4SIKILIEcEECEUBABbyVxg1MQFtnC9tx JKAtD1jAAg5ggAMCgIAFUuACEyhlAxzwtwfMYRh9gAABLMCFimygABHwwj1G5IFMZeSicyCCCWq2 LqDUKqyP3+oJR/1Bgh8xYl0hLSkT+GKtg0UCpCr9SL8IEUxB2fSmOM2pTsUSAgA7 ------_=_NextPart_001_01C7A878.8C19FCEA-- -- http://www.freelists.org/webpage/oracle-l