Well said, Karen! I am dealing with few views that have multiple 'union all' branches with 'not in' clauses all around the code. I didn't quite understand the business logic behind these various branches and so talked to the developers, understood their sole reasoning: outerjoin is evil. They have written these complicated views just to avoid outerjoins!!!. Aaargh.. I have converted two of them already to use outerjoins and the optimizer is doing excellent job pushing the predicates correctly and optimizing them efficiently. Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Karen Morton Sent: Tuesday, August 31, 2004 10: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 -----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. 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@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 ----------------------------------------------------------------- -- Attached file included as plaintext by Ecartis -- -- Desc: Signature The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. ---------------------------------------------------------------- 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 -----------------------------------------------------------------