From oracle-l-bounce@freelists.org Tue Aug 31 10:01:50 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i7VF1oE29609 for ; Tue, 31 Aug 2004 10:01:50 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i7VF1nI29584 for ; Tue, 31 Aug 2004 10:01:49 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 256AC72E08A; Tue, 31 Aug 2004 10:03:13 -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 25028-76; Tue, 31 Aug 2004 10:03:13 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5D1CB72E0A2; Tue, 31 Aug 2004 10:03:12 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 31 Aug 2004 10:01:32 -0500 (EST) X-Original-To: Oracle-L@freelists.org Delivered-To: Oracle-L@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B896F72D178 for ; Tue, 31 Aug 2004 10:01:31 -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 24699-60 for ; Tue, 31 Aug 2004 10:01:31 -0500 (EST) Received: from ns2.sky.com (ns2.sky.com [193.114.117.60]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BE23972D10B for ; Tue, 31 Aug 2004 10:01:30 -0500 (EST) Received: from ost-msw-01.bskyb.com ([192.168.177.248]) by ns2.sky.com (8.9.3/8.9.3) with ESMTP id QAA28556 for ; Tue, 31 Aug 2004 16:04:23 +0100 (BST) Received: from ost_exch_bhs02.bskyb.com (unverified) by ost-msw-01.bskyb.com (Content Technologies SMTPRS 4.3.14) with ESMTP id for ; Tue, 31 Aug 2004 16:01:58 +0100 Received: by bridgeheads.bskyb.com with Internet Mail Service (5.5.2657.72) id ; Tue, 31 Aug 2004 16:04:22 +0100 Message-ID: From: "Barr, Stephen" To: Oracle-L@freelists.org Subject: Outer Joins are Evil? Date: Tue, 31 Aug 2004 16:04:10 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-type: text/plain X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 8712 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Stephen.Barr@bskyb.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I'm currently working on a datawarehouse project (~5 Tb) where the decision has been made to avoid performing outer joins. The reasons given for this seem to be - 1. Simplifies user navigation of the structures - i.e. avoids outer joins. 2. Outer joins are slow and should be avoided at all costs. 3. If an FK is missing it is populated with a default value which will relate to an actual row in the target table, hence no rows will ever be dropped - again, supposedly this is to simply SQL and avoid outer joins. What they actually do is populate each table in the structure with three default rows with an SK of 0, 1 & 2. Any FK's which are missing, not applicable or invalid will point to one of these rows. Now....my question is....what is so inherently evil about outer joins that we go to this extreme to avoid them? AND...has anyone else seen something like this deployed in other places? Thanks, Steve. ----------------------------------------------------------------------- Information in this email may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks. ----------------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------