RE: Outer Joins are Evil?

From: Barr, Stephen <Stephen.Barr_at_bskyb.com>
Date: Tue, 31 Aug 2004 17:19:32 +0100
Message-ID: <B116C5C7C606D311B54D0008C75B99201576A989@sssl_exch_usr3.sssl.bskyb.com>

There is however another issue here when partitioning is brought into the equation - the default rows will be present in only one partition.

If the query excludes the partition where the default rows are located, then it will drop rows anyway!

In fact, even for non-partitioned tables - if the where clause contains a predicate which excludes the default rows, then AGAIN they become useless.

Even at a logical level, I don't think they could work.

Which brings me back to my initial question - has anyone seen an implementation like this before?!

Remember though that I am talking about default ROWS, not defaults values. In this implementation the default values for non-mandatory FK's will actually reference a physical row in the target table.

Steve.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Cary Millsap
Sent: 31 August 2004 16:23
To: oracle-l_at_freelists.org
Subject: RE: Outer Joins are Evil?

I don't think the SQL developer gets a choice. It's like telling a C programmer to avoid multiplication and division because they're slower = than
addition and subtraction! Once the data design is set, if the developer = has
to use outer joins to get the correct answer, then it's a choice of = either
(a)=A0use outer joins, or (b)=A0get the wrong answer.

>From the data /designer's/ perspective, I think I agree with the points you're listing below. It's probably a Very Good Idea to create data = designs
that allow your business functions to take place without outer joins.

I'll defer to Lex and others for a more definitive response.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26 Toronto
- SQL Optimization 101: 8/16 Minneapolis, 9/20 Hartford, 10/18 New = Orleans
- Hotsos Symposium 2005: March 6-10 Dallas - Visit www.hotsos.com for schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Barr, Stephen
Sent: Tuesday, August 31, 2004 10:04 AM
To: Oracle-L_at_freelists.org
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.
=20

The reasons given for this seem to be -
=20

1. Simplifies user navigation of the structures - i.e. avoids outer joins.=20

2.	Outer joins are slow and should be avoided at all costs.=20
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. =

=20

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.
=20

Now....my question is....what is so inherently evil about outer joins = that
we go to this extreme to avoid them?
=20

AND...has anyone else seen something like this deployed in other places?
=20

Thanks,
=20

Steve.
=20
=20
=20

Information in this email may be privileged, confidential and is=20 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,=20 distribute, store, retransmit, use or disclose its contents to anyone.
=20

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
-----------------------------------------------------------------


.
----------------------------------------------------------------
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:15:27 CDT