前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >小知识：IN和EXISTS的用法及效率验证

小知识：IN和EXISTS的用法及效率验证

作者头像
Alfred Zhao
发布于 2023-03-24 10:26:28
4540
发布于 2023-03-24 10:26:28
举报
文章被收录于专栏：从ORACLE起航，领略精彩的IT技术。从ORACLE起航，领略精彩的IT技术。

环境： Oracle 19.16 多租户架构 经常会在网上看到有人写exists和in的效率区别，其实在新版本的数据库中，是不存在这个问题的，优化器会自己判断选择最优的执行计划。

为了直观的说明，我在PDB中构造如下测试用例：

vi 1.sql

代码语言：javascript
复制
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。

代码语言：javascript
复制
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的查询效率会高。 验证下，是否事实真是如此？

代码语言：javascript
复制
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的写法：

代码语言：javascript
复制
--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:

代码语言：javascript
复制
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:

代码语言：javascript
复制
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:

代码语言：javascript
复制
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:

代码语言：javascript
复制
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。

所以，在新版本的数据库中，确实是不用再关注这个问题，优化器会帮助我们做好最优的查询转换。

本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2023-03-23，如有侵权请联系 cloudcommunity@tencent.com 删除
数据库
exists
架构
效率
优化

本文分享自 作者个人站点/博客 前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

数据库
exists
架构
效率
优化
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • SQL1:
  • SQL2:
  • SQL3:
  • SQL4:
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
产品介绍
精选特惠 用云无忧
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论