小知识：IN和EXISTS的用法及效率验证
2023-03-23 00:36 AlfredZhao 阅读(399) 评论(0) 编辑 收藏 举报环境： 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。
所以，在新版本的数据库中，确实是不用再关注这个问题，优化器会帮助我们做好最优的查询转换。
AlfredZhao©版权所有「从Oracle起航，领略精彩的IT技术。」
