5
小知识：IN和EXISTS的用法及效率验证 - AlfredZhao
source link: https://www.cnblogs.com/jyzhao/p/17245998.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
小知识：IN和EXISTS的用法及效率验证
2023-03-23 00:36 AlfredZhao 阅读(18) 评论(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。
所以，在新版本的数据库中，确实是不用再关注这个问题，优化器会帮助我们做好最优的查询转换。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK