Ansi SQL vs Oracle SQL

Today I was wondering about whether we should be using Ansi SQL instead of the 'normal' Oracle SQL. Ansi SQL has been supported in Oracle since version 9i, but since we are still developing applications on Oracle 8i we haven't really given it much thought until now. We are migrating our development database to Oracle9i (No 10G yet, because our DBA's don't support it yet) and now is the time to consider writing Ansi SQL instead of the 'normal' Oracle SQL.

What are the pros and cons of Ansi SQL? One of the things that pops to mind is to make our application less 'Oracle locked in'. But will we really achieve that just by using Ansi SQL? Our application(s) rely heavily on the Oracle database and all of it's features. Of course we can use Ansi SQL all the way, but a lot of functions and procedures are deliberately put in backend code (PL/SQL) because of their speed and their need for database interactivity. Sure, we can rebuild these in say SQL Server or even MySQL, but at this moment we don't use any of those for this application and frankly, I don't see this happening any time soon.

Another thing is that it may make the queries more readable. All the joins between tables are done in the FROM clause instead of in the WHERE clause. Now they can be grouped together, although I am used to writing the joins first in my WHERE clauses. But it doesn't mean everybody writes in such a way.

A problem with our current queries can definitely be the outer-joins used. In Oracle SQL you use the plus sign (+) to tell the SQL Engine that it's an outer join. In SQL Server you should use the asterisk (*) operator to do the same. In Ansi SQL you tell the query to LEFT OUTER JOIN or RIGHT OUTER JOIN one table to another. This is the same in both engines.

I don't know which syntax I like more. The Oracle SQL version:

FROM emp, dept
WHERE emp.deptno = dept.deptno;

Or the Ansi SQL version:

FROM emp JOIN dept ON emp.deptno = dept.deptno;

I think it just takes some getting used to. Of course there are possibilities in Ansi SQL that are easier to use than doing the same in 'normal' SQL. There's for instance the FULL OUTER JOIN. Show all rows from one table that may or may not have a row in the other table and vice versa. Sure this can be achieved in Oracle SQL creating a UNION based query, but that would mean that all the other clauses in the query would have to be written twice (or more), which is totally against our Single Point Of Definition principle. I know Steven Feuerstein wrote about this in one of his blogs on ToadWorld, but I think we were using this principle since the last century (somewhere in the 90's).

I am not sure what to use. Maybe it's better to start getting used to Ansi SQL because it's a better preparation for future versions of the (Oracle) database. And we can be more prepared to move to different databases, although that will include learning a new language as well as a new way to create code in the database. I know I am not a supporter of a 'dumb database' or 'datastore'. I want to use the features in the database as they are available.

3 thoughts on “Ansi SQL vs Oracle SQL

  1. At my last place I worked the development team had wrote some of the application code using ANSI sql, lots of left/right outer join syntax. When I migrated the database from 9.0 to these queries suddenly starting taking much longer to run, from seconds to minutes. Passed it on to Oracle support who ended up re-writing these code segments to use Oracle syntax (+) rather than ANSI for the joins, the queries started to then out perform the 9.0 release.

    I found this very strange as Oracle do say they support ANSI code so you would think it would work the same. One to look out for anyway.

  2. Lee, did this have to do with the structure of the tables? Were they exceptionally big or was there another reason given for this behavior? If it’s a problem with any size and type of table, do you know if it has been fixed in 10G or maybe even 11???

  3. The tables were not that big, I remember uploading an export of the tables in question to metalink pretty quickly. The result sets were small too. The only reason given by Oracle was that it was a change in the optimizer behaviour from 9.0.1 to causing the problem. The only work around was to use Oracle SQL rather than ANSI. I never got to test the queries at 10G so can’t answer that one sorry.

Leave a Reply

Your email address will not be published. Required fields are marked *