ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle LEFT JOIN中ON条件与WHERE条件的区别

Oracle LEFT JOIN中ON条件与WHERE条件的区别

原创 Linux操作系统 作者:dzq0371 时间:2013-06-07 09:42:48 0 删除 编辑
1、创建测试数据:
SQL> create table t1 as select * from scott.emp;

Table created.

SQL> create table t2 as select * from scott.dept;

Table created.

SQL> 
2、以下为使用where的查询结果与执行计划
SQL> select t1.*,t2.dname from t1 left join t2 on t1.deptno=t2.deptno where t1.job='CLERK';

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- --------------
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10 ACCOUNTING
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20 RESEARCH
      7369 SMITH      CLERK           7902 17-12月-80            800                    20 RESEARCH
      7900 JAMES      CLERK           7698 03-12月-81            950                    30 SALES

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1823443478

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   436 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     4 |   436 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |     4 |   348 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     4 |    88 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO"(+))
   2 - filter("T1"."JOB"='CLERK')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       1223  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL>
3、以下为使用on条件的查询结果与执行计划
SQL> select t1.*,t2.dname from t1 left join t2 on t1.deptno=t2.deptno and t1.job='CLERK';

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- --------------
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10 ACCOUNTING
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20 RESEARCH
      7369 SMITH      CLERK           7902 17-12月-80            800                    20 RESEARCH
      7900 JAMES      CLERK           7698 03-12月-81            950                    30 SALES
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- --------------
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
      7839 KING       PRESIDENT            17-11月-81           5000                    10
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30

14 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1823443478

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    19 |  2071 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |    19 |  2071 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |    14 |  1218 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     4 |    88 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO"(+) AND "T1"."JOB"=CASE  WHEN
              ("T2"."DEPTNO"(+) IS NOT NULL) THEN 'CLERK' ELSE 'CLERK' END )

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1590  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL> 

oracle 对谓词and t1.job='CLERK'(on 后面的),where t1.job='CLERK'的解析是不一样的。
使用where t1.job='CLERK':
1 - access("T1"."DEPTNO"="T2"."DEPTNO"(+))
2 - filter("T1"."JOB"='CLERK')
Oracle 先根据"T1"."JOB"='CLERK'对T1表进行过滤,然后与T2表进行左外连接

Oracle解析的谓词and t1.job='CLERK'(on 后面的)为:
1 - access("T1"."DEPTNO"="T2"."DEPTNO"(+) AND "T1"."JOB"=CASE  WHEN
           ("T2"."DEPTNO"(+) IS NOT NULL) THEN 'CLERK' ELSE 'CLERK' END )
Oracle 对t1,t2进行全表扫描,之后进行左外连接(也可能是在扫描过程中进行连接),而and t1.job='CLERK'对连接之后的记录总数没有影响,只是对不符合and t1.job='CLERK'的记录中的部门名称置为空

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23009281/viewspace-763320/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-07-14

  • 博文量
    52
  • 访问量
    182121