Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: Outer Joins are Evil?

Re: RE: Outer Joins are Evil?

From: <tim_at_sagelogix.com>
Date: Tue, 31 Aug 2004 10:32:53 -0600 (MDT)
Message-ID: <7892502.1093969973650.JavaMail.oracle@ocs.sagelogix.com>


Ryan,
Always design for reality. The person that Steve described wishes to alter reality to fit a theory. As Cary, Karen, and several others have mentioned, outer-joins are not a design choice. They are an implementation mechanism, one of several available to resolve the situation of childless parent entities. It happens to be a good solution (perhaps the best) for that particular situation.

Pay attention to the rules of relational design and, in the case of data warehouses, pay special attention to minimize complexity by designing a dimensional data model. Data warehouses simply have no need to present every nuance and attribute, the full "richness", of the data which is necessary for the purpose of enforcing business rules, as operational system do. They only need to present the data, accurately and consistently, for optimal retrieval. Nowhere in the design of either type of system should implementation mechanisms like "outer joins" be considered, ideally.

In short, first design the "what" and then implement the "how", and don't confuse the order.

Hope this helps...

-Tim

Return-Path: <oracle-l-bounce_at_freelists.org> Received: from mail.sagelogix.com by ocs.sagelogix.com

        with ESMTP id 35179701093968607; Tue, 31 Aug 2004 10:10:07 -0600 Received: by mail.sagelogix.com (Postfix, from userid 16)

        id 19D3EA8470; Tue, 31 Aug 2004 10:01:22 -0600 (MDT) Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180])

	by mail.sagelogix.com (Postfix) with ESMTP id 4236EA8436
	for <tim_at_sagelogix.com>; Tue, 31 Aug 2004 09:59:52 -0600 (MDT)
Received: from localhost (localhost [127.0.0.1])
	by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP
	id BECA572D0D2; Tue, 31 Aug 2004 11:04:33 -0500 (EST)
Received: from turing.freelists.org ([127.0.0.1])  by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP  id 05976-18; Tue, 31 Aug 2004 11:04:33 -0500 (EST) Received: from turing (localhost [127.0.0.1])
	by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP
	id E384072CE2C; Tue, 31 Aug 2004 11:04:32 -0500 (EST)
Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 31 Aug 2004 11:03:05 -0500 (EST) X-Original-To: oracle-l_at_freelists.org
Delivered-To: oracle-l_at_freelists.org
Received: from localhost (localhost [127.0.0.1])
	by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7597E72E740
	for <oracle-l_at_freelists.org>; Tue, 31 Aug 2004 11:03:03 -0500 (EST)
Received: from turing.freelists.org ([127.0.0.1])  by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP  id 05276-87 for <oracle-l_at_freelists.org>;  Tue, 31 Aug 2004 11:03:03 -0500 (EST)
Received: from rwcrmhc11.comcast.net (rwcrmhc11.comcast.net [204.127.198.35])
	by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DF5F972E878
	for <oracle-l_at_freelists.org>; Tue, 31 Aug 2004 11:02:58 -0500 (EST)
Received: from 204.127.197.117 ([204.127.197.117])
          by comcast.net (rwcrmhc11) with SMTP
          id <2004083116055201300hjqpte>; Tue, 31 Aug 2004 16:05:52 +0000
Received: from [192.35.84.5] by 204.127.197.117;
	Tue, 31 Aug 2004 16:05:51 +0000

From: ryan_gaffuri_at_comcast.net
To: oracle-l_at_freelists.org
Subject: RE: Outer Joins are Evil?
Date: Tue, 31 Aug 2004 16:05:51 +0000
Message-Id: <083120041605.19234.4134A1DF000782B400004B222200734076079D9A00000E09A1020E979D_a.net> X-Mailer: AT&T Message Center Version 1 (Jul 16 2004) X-Authenticated-Sender: cnlhbl9nYWZmdXJpQGNvbWNhc3QubmV0 MIME-Version: 1.0
Content-type: text/plain
X-Virus-Scanned: by amavisd-new at freelists.org
Content-Transfer-Encoding: 8bit

X-archive-position: 8733
X-ecartis-version: Ecartis v1.0.0
Sender: oracle-l-bounce_at_freelists.org
Errors-To: oracle-l-bounce_at_freelists.org X-original-sender: ryan_gaffuri_at_comcast.net Precedence: normal
Reply-To: oracle-l_at_freelists.org
X-list: oracle-l
X-Virus-Scanned: by amavisd-new at freelists.org
X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on mail.sagelogix.com
X-Spam-Status: No, hits=0.5 required=3.0 tests=NO_REAL_NAME autolearn=no 
	version=2.63

X-Spam-Level:

when you design an oltp system do you take outer joins inter consideration and attempt to minimize them at the design level? tim gorman mentioned that he does not for datawarehouses. -------------- Original message --------------

> Sounds like excuses...not reasons. What evidence do they provide to
> support their conclusion that outer joins are bad? It looks a bit like
> an attempt to disguise a fear of doing full table scans or something
> like that.
>
> Outer joins, like pretty much everything else, are not "inherently
> evil". They are another option/tool to be used appropriately when and
> where needed. While I don't disagree with using default values in FK
> columns and the like, doing it only with the justification of avoiding
> outer joins is a bit near-sighted. Eliminating any one thing out of
> fear of what "it" may do seems to me to be more a fear of poorly written
> code as a result of misusing the feature. If it's really the fear of
> bad code, then teach people how to properly use the tool and do not take
> the tool out of the box entirely instead.
>
>
> Karen Morton
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> Upcoming events at http://www.hotsos.com/education/schedule.html
>



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Aug 31 2004 - 11:30:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US