遇到一个关于优化问题,大数据量的数据 in 和 exists 的区别
特地在自己机器上实验了一把
1.数据库版本
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
2.表
SCOTT.EMP
SCOTT.DEPT
--SCOTT.DEPT.DEPTNO是SCOTT.EMP.DEPTNO的外键
CREATE TABLE SCOTT.EMP1 AS SELECT * FROM SCOTT.EMP
CREATE TABLE SCOTT.DEPT1 AS SELECT * FROM SCOTT.DEPT
3.执行计划统计
select * from table(dbms_xplan.display_cursor('sql_id'))
--------------------------------------------------------------------------------------------------------------
1.RBO+主外键关系的执行计划
select /*+ rule */* from emp where emp.deptno in (select deptno from dept) --6sk9v0wuwzbct
-=====================================================
SQL_ID 6sk9v0wuwzbct, child number 0
-------------------------------------
select /*+ rule */* from emp where emp.deptno in (select deptno from
dept)
Plan hash value: 3956160932
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| EMP |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMP"."DEPTNO" IS NOT NULL)
Note
-----
- rule based optimizer used (consider using cbo)
/*
基于规则的优化器如果存在主外键的时候会忽略掉dept表的扫描
*/
2.主外键 + RBO 的主外键exists操作
select /*+ rule */* from emp where exists (select 1 from dept where dept.deptno=emp.deptno) --1b9t1auw0zpnc
--===============================================================
SQL_ID 1b9t1auw0zpnc, child number 0
-------------------------------------
select /*+ rule */* from emp where exists (select 1 from dept where
dept.deptno=emp.deptno)
Plan hash value: 1783302997
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | TABLE ACCESS FULL| EMP |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT |
--------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - access("DEPT"."DEPTNO"=:B1)
Note
-----
- rule based optimizer used (consider using cbo)
/*
可以看到比in写法多了一次扫描
*/
3.无索引+RBO in操作的执行计划
select /*+ rule */* from emp1 where deptno in (select deptno from dept1) --fwtdq5hj1w5v7
--===================================================
SQL_ID fwtdq5hj1w5v7, child number 0
-------------------------------------
select /*+ rule */* from emp1 where deptno in (select deptno from dept1)
Pla