Re: ANSI Joins

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_at_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                                                                                                      
                      <mgogala_at_allegients        To:       "'Oracle-L (E-mail)'" <oracle-l_at_freelists.org>                                
                      ystems.com>                cc:                                                                                     
                      Sent by:                   Subject:  ANSI Joins                                                                    
                      oracle-l-bounce_at_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 Received on Tue Jan 18 2005 - 23:49:15 CST