What is the impact of ANSI SQL Format?

Follow / 26 Dec 2011 at 11:54pm

I have a client that prefers SQL be written in ANSI SQL format (JOIN table On ...) but when I use the Optimizer it rewrites the code in standard format.  So does the format make a performance difference or is it just style?  If it is not a major performance impact, is there any option to have Optimizer maintain the ANSI format?

Replies

  • Follow / 28 Dec 2011 at 8:56am

    Hi,

    The ANSI SQL format is more than just a style. It affects how Oracle interprets your SQL as well. Since the way information expressed in an ANSI SQL format and a non-ANSI SQL format is different, they can have different inferences to the database engine. And when it comes to outer-join, the ANSI SQL format allows joining conditions that are not allowed in non-ANSI format (for example, an In-List condition is allowed for ANSI outer-join but not in non-ANSI format). So I always see the ANSI SQL format more than just the style.

    Regarding enforcing an ANSI SQL format rewrite, SQL Optimizer does have this option available in the Options window under Optimize SQL | Optimizer | Optimization | Join Tables. Note that in order to change this option, you will need to set the Intelligence setting to "Custom Settings" first.

    Thanks,
    Alex