环境： Oracle 19.16 多租户架构
经常会在网上看到有人写exists和in的效率区别，其实在新版本的数据库中，是不存在这个问题的，优化器会自己判断选择最优的执行计划。
为了直观的说明，我在PDB中构造如下测试用例：
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，优化器会帮你做查询转换。
为了进一步验证，构造4个典型SQL，分别使用in和exists的写法：
--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 3zbvwad7h1pgt 2360206614 1 2 3zbvwad7h1pgt 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_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 250w6cua1mfa0, child number 2 ------------------------------------- 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 Plan hash value: 910330555 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 12 |00:00:00.07 | 16132 | | | | | 1 | SORT GROUP BY | | 1 | 228 | 12 |00:00:00.07 | 16132 | 2048 | 2048 | 2048 (0)| |* 2 | HASH JOIN SEMI | | 1 | 228 | 35 |00:00:00.07 | 16132 | 1376K| 1376K| 1604K (0)| |* 3 | TABLE ACCESS FULL| T1 | 1 | 228 | 228 |00:00:00.01 | 26 | | | | |* 4 | TABLE ACCESS FULL| T2 | 1 | 177K| 177K|00:00:00.06 | 16106 | | | | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."SQL_ID"="T2"."SQL_ID") 3 - filter("T1"."SQL_ID" IS NOT NULL) 4 - filter("T2"."SQL_ID" IS NOT NULL) 25 rows selected. Elapsed: 00:00:00.04 SQL2:
SQL> select /*+ monitor */ SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, count(*) 2 from T1 3 where exists (select 1 from T2 where T2.sql_id = T1.sql_id) 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 3zbvwad7h1pgt 2360206614 1 2 3zbvwad7h1pgt 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.06 SQL> @xplan PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID cxn8artthq7p8, child number 0 ------------------------------------- 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 Plan hash value: 910330555 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 12 |00:00:00.06 | 16132 | | | | | 1 | SORT GROUP BY | | 1 | 228 | 12 |00:00:00.06 | 16132 | 2048 | 2048 | 2048 (0)| |* 2 | HASH JOIN SEMI | | 1 | 228 | 35 |00:00:00.06 | 16132 | 1376K| 1376K| 1611K (0)| |* 3 | TABLE ACCESS FULL| T1 | 1 | 228 | 228 |00:00:00.01 | 26 | | | | |* 4 | TABLE ACCESS FULL| T2 | 1 | 177K| 177K|00:00:00.05 | 16106 | | | | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T2"."SQL_ID"="T1"."SQL_ID") 3 - filter("T1"."SQL_ID" IS NOT NULL) 4 - filter("T2"."SQL_ID" IS NOT NULL) 26 rows selected. Elapsed: 00:00:00.03 SQL3:
SQL> select /*+ monitor */ SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, count(*) 2 from T2 3 where T2.sql_id in (select T1.sql_id from T1) 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 1774581179 20 2 3kqrku32p6sfn 1774581179 23 2 3kqrku32p6sfn 2977818336 14 2 3zbvwad7h1pgt 2360206614 1 87gaftwrm2h68 1072382624 2 2 9wncfacx0nj9h 3312548573 2 avf5k3k0x0cxn 3746835944 1 1 b13g21mgg8y98 212733457 9 1 b13g21mgg8y98 2612542848 1 2 ggh55rhz95kyj 3124993369 4 gug127tbfzjcs 3645025857 1 12 rows selected. Elapsed: 00:00:00.09 SQL> @xplan PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 1588n6cc48yv0, child number 0 ------------------------------------- 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 Plan hash value: 3152222881 ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 12 |00:00:00.08 | 16132 | | | | | 1 | SORT GROUP BY | | 1 | 3684 | 12 |00:00:00.08 | 16132 | 2048 | 2048 | 2048 (0)| |* 2 | HASH JOIN RIGHT SEMI| | 1 | 3684 | 21 |00:00:00.08 | 16132 | 1651K| 1651K| 1520K (0)| |* 3 | TABLE ACCESS FULL | T1 | 1 | 228 | 228 |00:00:00.01 | 26 | | | | |* 4 | TABLE ACCESS FULL | T2 | 1 | 177K| 177K|00:00:00.08 | 16106 | | | | ------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T2"."SQL_ID"="T1"."SQL_ID") 3 - filter("T1"."SQL_ID" IS NOT NULL) 4 - filter("T2"."SQL_ID" IS NOT NULL) 25 rows selected. Elapsed: 00:00:00.03 SQL4:
SQL> select /*+ monitor */ SQL_ID, SQL_PLAN_HASH_VALUE, SQL_PLAN_LINE_ID, count(*) 2 from T2 3 where exists (select 1 from T1 where T1.sql_id = T2.sql_id) 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 1774581179 20 2 3kqrku32p6sfn 1774581179 23 2 3kqrku32p6sfn 2977818336 14 2 3zbvwad7h1pgt 2360206614 1 87gaftwrm2h68 1072382624 2 2 9wncfacx0nj9h 3312548573 2 avf5k3k0x0cxn 3746835944 1 1 b13g21mgg8y98 212733457 9 1 b13g21mgg8y98 2612542848 1 2 ggh55rhz95kyj 3124993369 4 gug127tbfzjcs 3645025857 1 12 rows selected. Elapsed: 00:00:00.09 SQL> @xplan PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 99fkm9p94agcf, child number 0 ------------------------------------- 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 Plan hash value: 3152222881 ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 12 |00:00:00.09 | 16132 | | | | | 1 | SORT GROUP BY | | 1 | 3684 | 12 |00:00:00.09 | 16132 | 2048 | 2048 | 2048 (0)| |* 2 | HASH JOIN RIGHT SEMI| | 1 | 3684 | 21 |00:00:00.09 | 16132 | 1651K| 1651K| 1520K (0)| |* 3 | TABLE ACCESS FULL | T1 | 1 | 228 | 228 |00:00:00.01 | 26 | | | | |* 4 | TABLE ACCESS FULL | T2 | 1 | 177K| 177K|00:00:00.08 | 16106 | | | | ------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."SQL_ID"="T2"."SQL_ID") 3 - filter("T1"."SQL_ID" IS NOT NULL) 4 - filter("T2"."SQL_ID" IS NOT NULL) 26 rows selected. Elapsed: 00:00:00.03 可以看到对比前2个SQL：
250w6cua1mfa0
cxn8artthq7p8
执行计划一样，都是HASH JOIN SEMI。
对比后两个SQL：
1588n6cc48yv0
99fkm9p94agcf
执行计划也一样，都是HASH JOIN RIGHT SEMI。
所以，在新版本的数据库中，确实是不用再关注这个问题，优化器会帮助我们做好最优的查询转换。
