oracle 11G 实验 in 和 exists 区别

最新推荐文章于 2021-04-15 19:50:47 发布

原创

最新推荐文章于 2021-04-15 19:50:47 发布 · 801 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#in #exists #oracle

本文通过在Oracle 11G环境下进行实验，探讨了在大数据量下IN和EXISTS操作的区别。实验涉及不同场景，包括RBO优化器、主外键关系、无索引及有索引的情况。初步结论指出，在CBO优化器下，IN与EXISTS的执行计划差异不大；而在RBO优化器中，两者的表现可能因应用场景而异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

遇到一个关于优化问题，大数据量的数据 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