From oracle-l-bounce@freelists.org Tue Jan 18 23:49:15 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j0J5nEqH017491 for ; Tue, 18 Jan 2005 23:49:14 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j0J5nEaD017487 for ; Tue, 18 Jan 2005 23:49:14 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DAC9E72C9F0; Tue, 18 Jan 2005 16:07:32 -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 32456-44; Tue, 18 Jan 2005 16:07:32 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E304472C9F8; Tue, 18 Jan 2005 16:05:29 -0500 (EST) Subject: Re: ANSI Joins To: mgogala@allegientsystems.com Cc: "'Oracle-L (E-mail)'" , oracle-l-bounce@freelists.org Message-ID: From: alan.aschenbrenner@ihs.com Date: Tue, 18 Jan 2005 14:03:02 -0700 X-MIMETrack: Serialize by Router on Domino/IHS/US(Release 6.5|September 26, 2003) at 01/18/2005 02:08:38 PM MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit X-archive-position: 14906 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: alan.aschenbrenner@ihs.com Precedence: normal Reply-To: alan.aschenbrenner@ihs.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.3 required=5.0 tests=NO_REAL_NAME autolearn=no version=2.60 X-Spam-Level: Mladen, I also dislike ANSI syntax. Besides it being difficult to read, the optimizer sometimes does crazy things with ANSI syntax (especially when involving dblinks). I have rewritten a few selects into non-ANSI syntax, and the optimizer has chosen a MUCH better execution plan. I have seen queries run for minutes in ANSI syntax, but take only seconds when rewritten in non-ANSI syntax. Maybe the ANSI syntax query was written poorly, but not being proficient in it, I couldn't make any suggestions... (these observations were made with 9i databases) Has anyone else had this experience? If it is an optimizer problem, maybe this is fixed in the 10g optimizer. Anyone? My 2 cents... Alan Alan Aschenbrenner Oracle DBA Information Handling Services alan.aschenbrenner@ihs.com ------------------------------------------------------------------------------------------------------ Confidentiality Notice: The information in this e-mail may be confidential and / or privileged. This e-mail is intended to be reviewed by only the individual or organization named in the e-mail address. If you are not the intended recipient, you are hereby notified that any review, dissemination or copying of this e-mail and attachments, if any, or the information contained herein, is strictly prohibited. Mladen Gogala ystems.com> cc: Sent by: Subject: ANSI Joins oracle-l-bounce@fre elists.org 01/18/2005 11:45 AM Please respond to mgogala My developers are starting to use ANSI joins in vain hope that they will make their apps portable across databases. I have a positive attitude toward ANSI joins: I hate those verbose extensions that make SQL statements lengthy and unreadable. What is the opinion of other people about ANSI joins? What is the @#$%! allure of those things? Where did they learn it from? Is there any readable document that explains ANSI joins for dummies? -- Mladen Gogala Oracle DBA Ext. 121 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l