Re: Outer Joins are Evil?

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Tue, 31 Aug 2004 23:43:36 +0200
Message-ID: <01d101c48fa3$9314ed80$3c02a8c0@JARAWIN>

> to avoid performing outer joins.

I guess - at least in data warehouse environment - the real question is not the use of outer or inner join BUT the use of surrogate or natural keys.

If you design your schema with surrogate keys the result of outer and inner joins are the same so you have no problems - you may choose the better (if there is really a difference). You will get no inconstant result and all tools deliver the same result.

Using natural keys, there are plenty of strategies how to save costs at the risk of facing some later disastrous scenarios (see Kimball for examples).

> Outer joins are slow

I agree with D. Tow the this is a myth. I see the rationale of this in the behaviour that outer join on the same data returns greater result set as an inner join, thus consume more resources.

> should be avoided at all costs.

If you have a choice between feature A (cost X) and B (cost Y) and X > Y, you may prefer B but not "at all cost". The cost of the switch should be less then X - Y (decreased by the cost of the migration and quality assurance).

Jaromir D.B. Nemec

http://www.db-nemec.com

Original Message ----- From: "Barr, Stephen" <Stephen.Barr_at_bskyb.com> To: <Oracle-L_at_freelists.org> Sent: Tuesday, August 31, 2004 5:04 PM Subject: Outer Joins are Evil?

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 -

Simplifies user navigation of the structures - i.e. avoids outer joins.
Outer joins are slow and should be avoided at all costs.
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_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 - 16:41:14 CDT