RE: Outer Joins are Evil?
- From: "Rudy Zung" <Rudy.Zung@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 31 Aug 2004 12:03:53 -0400
A lack of understanding of outer-joins is what usually drives
people to think that all outer-joins are bad.
Outer-joins can degenerate into an FTS if the outer-join
columns can skip over leading columns of an index:
Table A and B both have columns C1 and C2; both are
indexed on C1 and C2. This is OK:
select A.*,
B.*
from A,
B
where A.C1 =3D B.C1 and
A.C2 =3D B.C2(+);
The query still can use the C1 of the index to narrow the
list of records before deciding if there is a matchin
B.C2(+).
This is bad:
select A.*,
B.*
from A,
B
where A.C1 =3D B.C1(+) and
A.C2 =3D B.C2(+);
What if you have a B where A.C2 =3D B.C2, also where there=20
are no records where A.C1 =3D B.C1? For Oracle to find the
matching A.C2 =3D B.C2, Oracle would need to FTS.
Classically, this is the question of find me all the words
in the dictionary that begin with the letter "O" versus=20
find me all the words in the dictionary where the second
letter in the word is "O".
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Karen Morton
Sent: Tuesday, August 31, 2004 11:54 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Outer Joins are Evil?
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
=20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Barr, Stephen
Sent: Tuesday, August 31, 2004 8:04 AM
To: Oracle-L@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
