外键消除表连接

很简单的一个语句

select * from emp where exists ( select 1 from dept where emp.deptno=dept.deptno);
Execution Plan
----------------------------------------------------------
Plan hash value: 3274513678

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    14 |   574 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |         |    14 |   574 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     4 |    12 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

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

在查询过程中用的nested loop semi表连接

通过对表结构更改添加外键再查看其执行计划

SQL> alter table emp add constraint fk_edeptno_dept foreign key (deptno) references dept(deptno);


SQL> select * from emp where exists ( select 1 from dept where emp.deptno=dept.deptno);
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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

对比

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

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

执行计划改变，减少表连接损耗！