ANSI or Classic?

I’m an old dog and I have yet to embrace ANSI syntax for writing SQL.  There is coming a day that I will likely have to move to ANSI and it appears to be coming sooner rather than later.  So I’ve started on this path.  While look around on the web the other day I stumbled on to a post saying that the old syntax is “bad” and the ANSI syntax is better (http://www.orafaq.com/node/2618).  So just for fun I pulled the queries used to see what the difference was.  

There isn’t any.

The author had stated that the classic syntax “is generating a cartesian product, and then filtering the result set with a predicate.”

The reality is that both the classic and the ANSI syntax create exactly the same plan.  Running both I then looked at V$SQL to see the plan created:

SQL> SELECT *

  2  FROM   emp,

  3         dept

  4  WHERE  emp.deptno = dept.deptno

  5         AND dept.dname = 'SALES';

SQL> SELECT *

  2  FROM   emp

  3         join dept USING(deptno)

  4  WHERE  dname = 'SALES';

SQL> select sql_id, child_number,plan_hash_value, sql_text from v$sql where sql_text like 'SELECT * FROM   emp%'

SQL_ID           CHILD_NUMBER PLAN_HASH_VALUE

------------- --------------- ---------------

SQL_TEXT

----------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

4ug91aycb85jh               0      1688427947

SELECT * FROM   emp        join dept USING(deptno) WHERE  dname = 'SALES'

6snrnnz2qfwms               0      1688427947

SELECT * FROM   emp,        dept WHERE  emp.deptno = dept.deptno        AND dept.dname = 'SALES'

Notice they both have the exact same PLAN_HASH_VALUE, this means they both use the exact same plan:

SQL> SELECT PLAN_TABLE_OUTPUT

  2      FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

  3      ('4ug91aycb85jh',0,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

SQL_ID  4ug91aycb85jh, child number 0

-------------------------------------

SELECT * FROM   emp        join dept USING(deptno) WHERE  dname =

'SALES'

Plan hash value: 1688427947

-------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |      1 |        |      6 |00:00:00.01 |      12 |

|   1 |  NESTED LOOPS                |              |      1 |        |      6 |00:00:00.01 |      12 |

|   2 |   NESTED LOOPS               |              |      1 |      4 |      6 |00:00:00.01 |      10 |

|*  3 |    TABLE ACCESS FULL         | DEPT         |      1 |      1 |      1 |00:00:00.01 |       8 |

|*  4 |    INDEX RANGE SCAN          | EMP_DEPT_IDX |      1 |      5 |      6 |00:00:00.01 |       2 |

|   5 |   TABLE ACCESS BY INDEX ROWID| EMP          |      6 |      4 |      6 |00:00:00.01 |       2 |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("DEPT"."DNAME"='SALES')

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

       filter("EMP"."DEPTNO" IS NOT NULL)

SQL> SELECT PLAN_TABLE_OUTPUT

  2      FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

  3      ('6snrnnz2qfwms',0,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

SQL_ID  6snrnnz2qfwms, child number 0

-------------------------------------

SELECT * FROM   emp,        dept WHERE  emp.deptno = dept.deptno

AND dept.dname = 'SALES'

Plan hash value: 1688427947

-------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |      1 |        |      6 |00:00:00.01 |      12 |

|   1 |  NESTED LOOPS                |              |      1 |        |      6 |00:00:00.01 |      12 |

|   2 |   NESTED LOOPS               |              |      1 |      4 |      6 |00:00:00.01 |      10 |

|*  3 |    TABLE ACCESS FULL         | DEPT         |      1 |      1 |      1 |00:00:00.01 |       8 |

|*  4 |    INDEX RANGE SCAN          | EMP_DEPT_IDX |      1 |      5 |      6 |00:00:00.01 |       2 |

|   5 |   TABLE ACCESS BY INDEX ROWID| EMP          |      6 |      4 |      6 |00:00:00.01 |       2 |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("DEPT"."DNAME"='SALES')

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

       filter("EMP"."DEPTNO" IS NOT NULL)

Is this going to be true for all statement?  I certainly doubt it, there are always exceptions.  My inclination is that switching between the two syntax types is likely to produce the same plan more times than not.    Of course a test is worth 1000 opinions. 

Database used for this test was:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Running on Windows 7 Professional.