外键消除表连接

来源：互联网发布：mysql大小写敏感开关编辑：程序博客网时间：2024/06/10 19:24

很简单的一个语句

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)

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