left/right join 和+的区别

 

今天下午在看到群里面讨论一个话题，left/right join 和+在oracle中有没有区别，下面我查找一下相关的资料：

The Oracle9i database offers join syntax that is SQL: 1999 compliant. Prior to the 9i release, the join syntax was different from the ANSI standards. The new SQL: 1999 compliantjoin syntax does not offer any performance benefits over the Oracle proprietary join syntax that existed in prior releases.

大概意思就是说在9i之前，两种写法有区别，会存在性能问题，但是9i之后，oracle公司消除了这种区别，也就是说9i之后两种写法是没有区别的。

下面实例如下：
有两个表T1和T2，两个表除了主键索引外均无其他索引，这两个表由T1.F1(主键)，T2.F2(主键)进行左连

接，SQL语句有两种写法：
1. SELECT * FROM T1,T2 WHERE T1.F1=T2.F2(+)
2. SELECT * FROM T1 LEFT JOIN T2 ON T1.F1=T2.F2

当查看1的执行计划时发现T1为全表扫描，T2为索引扫描。
当查看2的执行计划时发现两个表均为全表扫描。
这里没有实验环境，所以借用一下别人的实例。

前者选择的优化器是RULE，而后者选择的优化器是CBO的ALL ROWS。

SQL> show user
User is "scott"
 
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
 
SQL> select * from emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
 
14 rows selected

SQL> explain plan for SELECT * FROM DEPT DP,EMP EP WHERE DP.DEPTNO(+)=EP.DEPTNO;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1301846388
--------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   798 |     4   (0)| 00
|   1 |  NESTED LOOPS OUTER          |         |    14 |   798 |     4   (0)| 00
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   518 |     3   (0)| 00
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("DP"."DEPTNO"(+)="EP"."DEPTNO")
 
16 rows selected
 
SQL> explain plan for SELECT * FROM emp ep left join dept dp on ep.deptno=dp.deptno;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1301846388
--------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   798 |     4   (0)| 00
|   1 |  NESTED LOOPS OUTER          |         |    14 |   798 |     4   (0)| 00
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   518 |     3   (0)| 00
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("EP"."DEPTNO"="DP"."DEPTNO"(+))
 
16 rows selected

在10g里面两者采用了同样的执行计划，证实了文档上面所说。