Well, some people say Oracle join is better and some say ANSI is better. However why not try to check the statements execution plan.
I have 2 statements that result the same output using ANSI and Oracle
Code:
SELECT a.empno,
a.ename,
b.deptno,
b.dname
FROM emp a, dept b
WHERE a.deptno = b.deptno;
SELECT empno,
ename,
deptno,
dname
FROM emp join dept
using(deptno);
Now lets generate the Explain Plan
Code:
SQL> explain plan
2 set statement_id='Oracle_Join'
3 for
4 SELECT a.empno,
5 a.ename,
6 b.deptno,
7 b.dname
8 FROM emp a, dept b
9 WHERE a.deptno = b.deptno;
Explained.
SQL> explain plan
2 set statement_id='ANSI_Join'
3 for
4 SELECT empno,
5 ename,
6 deptno,
7 dname
8 FROM emp join dept
9 using(deptno);
Explained
Code:
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','Oracle_Join','ALL'));
PLAN_TABLE_OUTPUT
Code:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 364 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS CLUSTER| EMP | 4 | 52 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | IDX_PERSONNEL | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','ANSI_Join'));
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 364 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS CLUSTER| EMP | 4 | 52 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | IDX_PERSONNEL | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------
I cannot see any difference in the Execution Plan..
