环境： Oracle 19.16 多租户架构
经常会在网上看到有人写exists和in的效率区别，其实在新版本的数据库中，是不存在这个问题的，优化器会自己判断选择最优的执行计划。
vi 1.sql
select count(* from v$active_session_history;
select count(* from dba_hist_active_sess_history;
create table T1 as select * from v$active_session_history;
create table T2 as select * from dba_hist_active_sess_history;
构造小表T1，大表T2。
SQL> set timing on
SQL> @1
COUNT(*
----------
383
Elapsed: 00:00:00.05
COUNT(*
----------
215636
Elapsed: 00:00:00.95
Table created.
Elapsed: 00:00:00.20
Table created.
Elapsed: 00:00:07.90
网上说，当T1数据量小，而T2数据量非常大时，使用exists的查询效率会高。
验证下，是否事实真是如此？
select /*+ monitor */ * from T1 where exists(select 1 from T2 where T1.sql_id = T2.sql_id ;
select /*+ monitor */ * from T1 where T1.sql_id in (select T2.sql_id from T2 ;
SQL> select sql_id, sql_text from v$sql where sql_text like '%T2.sql_id%'
SQL_ID SQL_TEXT
------------- ------------------------------------------------------------------------------------------
4xu586p9h0qcq select /*+ monitor */ * from T1 where T1.sql_id in (select T2.sql_id from T2
3qgrm97t5jgwj select /*+ monitor */ * from T1 where exists(select 1 from T2 where T1.sql_id = T2.sql_id
使用sqlmon取到两个SQL对应的SQL Monitor Report，对比分析发现：
二者执行计划完全一样，对应Plan Hash Value 1713220790，都走的是Hash Join Semi，执行时间也没差别。
所以这个说法最起码在Oracle 19c的版本中是不存在的，你想怎么写都OK，优化器会帮你做查询转换。
--SQL1:
select /*+ monitor */ SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, count(*
from T1
where T1.sql_id in (select T2.sql_id from T2
group by SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID
order by 1;
--SQL2:
select /*+ monitor */ SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, count(*
from T1
where exists (select 1 from T2 where T2.sql_id = T1.sql_id
group by SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID
order by 1;
--SQL3:
select /*+ monitor */ SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, count(*
from T2
where T2.sql_id in (select T1.sql_id from T1
group by SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID
order by 1;
--SQL4:
select /*+ monitor */ SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, count(*
from T2
where exists (select 1 from T1 where T1.sql_id = T2.sql_id
group by SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID
order by 1;
SQL Monitor的截图就不贴了，直接给大家看下文本格式的执行计划，方便对比和检索：
SQL1:
SQL> select /*+ monitor */ SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, count(*
2 from T1
3 where T1.sql_id in (select T2.sql_id from T2
4 group by SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID
5 order by 1;
SQL_ID SQL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID COUNT(*
------------- ------------------- ---------------- ----------
3dbzmtf9ahvzt 3238164414 1 1
3kqrku32p6sfn 2977818336 14 1
3zbvwad7h2pgt 2360206614 1 2
3zbvwad7h2pgt 2360206614 6
87gaftwrm2h68 0 1
9wncfacx0nj9h 0 2
9wncfacx0nj9h 3312548573 9
avf5k3k0x0cxn 3746835944 1 1
b13g21mgg8y98 212733457 9 1
b13g21mgg8y98 212733457 12 2
ggh55rhz95kyj 3124993369 8
gug127tbfzjcs 3645025857 0 1
12 rows selected.
Elapsed: 00:00:00.07
SQL> @xplan
PLAN_T