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

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

转载作者：我是一只小鸟更新时间：2023-03-23 06:31:09

环境： 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.

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

最后此篇关于小知识：IN和EXISTS的用法及效率验证的文章就讲到这里了,如果你想了解更多关于小知识：IN和EXISTS的用法及效率验证的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

文章推荐： XCode汇编调试

文章推荐：云原生K8S精选的分布式可靠的键值存储etcd原理和实践

文章推荐： DDD架构中的领域是什么?

jQuery .on 效率
第一个 .on 函数比第二个更有效吗？ $( "div.container" ).on( "click", "p", function(){ }); $( "body" ).on( "click",
JavaScript 效率
已关闭。此问题不符合Stack Overflow guidelines 。目前不接受答案。这个问题似乎与 help center 中定义的范围内的编程无关。 . 已关闭 7 年前。 Improve
jQuery 效率
我有这样的查询: $('#tabContainer li'); JetBrains WebStorm IDE 将其突出显示为低效查询。它建议我改用这个: $('#tabContainer').find
Ruby 效率
我刚刚在 coursera ( https://www.coursera.org/saas/) 上听了一个讲座，教授说 Ruby 中的一切都是对象，每个方法调用都是在对象上调用发送方法，将一些参数传递
excel - 函数输入数据类型是否影响循环速度/效率
这可能是用户“不喜欢”的另一个问题，因为它更多的是与建议相关而不是与问题相关。我有一个在保存和工作簿打开时触发的代码。它在 f(白天与夜晚，日期与实际日期)中选择正确的工作表。周一到周三我的情况
performance - 效率:递归与循环
这只是我的好奇心，但是更有效的是递归还是循环？给定两个功能（使用通用lisp）： (defun factorial_recursion (x) (if (> x 0) (*
loops - For循环或While循环-效率
这可能是一个愚蠢的问题，但是while循环的效率与for循环的效率相比如何？我一直被教导，如果可以使用for循环，那我应该这样做。但是，实际上之间的区别是什么: $i = 0; while($i <
elasticsearch - elasticsearch排序与得分-效率？
我有一个Elasticsearch索引，其中包含几百万条记录。 (基于时间戳的日志记录) 我需要首先显示最新记录(即，按时间戳降序排列的记录) 在时间戳上排序desc是否比使用时间戳的函数计分功能更有
Java 效率 - 点与坐标
使用Point2D而不是double x和y值时，效率有很大差异吗？我正在开发一个程序，该程序有许多圆圈在屏幕上移动。他们各自从一个点出发，并越来越接近目的地(最后，他们停下来)。使用 .getC
Java ArrayList 效率
我正在编写一个游戏，并且有一个名为 GameObject 的抽象类和三个扩展它的类(Player、Wall 和 Enemy)。我有一个定义为包含游戏中所有对象的列表。 List objects; 当
javascript - 链接与创建新集合 - 效率
我是 Backbone 的初学者，想知道两者中哪一个更有效以及预期的做事方式。 A 型:创建一个新集合，接受先前操作的结果并从新集合中提取 key result = new Backbone.Coll
SQL 通配符搜索 - 效率？
最近，关于使用 LIKE 和通配符搜索 MS SQL 数据库的最有效方法存在争论。我们正在使用 %abc%、%abc 和 abc% 进行比较。有人说过，术语末尾应该始终有通配符 (abc%)。因此，根
java - Scala 效率
关闭。这个问题是opinion-based 。目前不接受答案。想要改进这个问题吗？更新问题，以便 editing this post 可以用事实和引文来回答它。 . 已关闭 8 年前。 Improv
java - setVisible() 效率
我想知道，这样做会更有效率吗: setVisible(false) // if the component is invisible 或者像这样: if(isVisible()){
c# - 很多insertQuerys - 效率
我有一个静态方法可以打开到 SQL Server 的连接、写入日志消息并关闭连接。我在整个代码中多次调用此方法(平均每 2 秒一次)。问题是 - 它有效率吗？我想也许积累一些日志并用一个连接插入它们
javascript - PHP生成jQuery——效率
这个问题在这里已经有了答案: Best practice to avoid memory or performance issues related to binding a large numbe
java - 石头剪刀布游戏 - 效率？
我为我的 CS 课(高中四年级)制作了一个石头剪刀布游戏，我的老师给我的 shell 文件指出我必须将 do while 循环放入运行者中，但我不明白为什么？我的代码可以工作，但她说最好把它写在运行者
java - 自定义数组包装类类型转换安全/效率
我正在编写一个需要通用列表的 Java 应用程序。该列表需要能够经常动态地调整大小，对此的明显答案是通用的Linkedlist。不幸的是，它还需要像通过调用索引添加/删除值一样频繁地获取/设置值。 A
php - MYSQL查询的问题(效率)
我的 Mysql 语句遇到了真正的问题，我需要将几个表连接在一起，查询它们并按另一个表中值的平均值进行排序。这就是我所拥有的... SELECT ROUND(avg(re.rating
java - 效率？这些代码之间有什么区别吗？
这个问题在这里已经有了答案: 关闭 10 年前。 Possible Duplicate: Is there a difference between i==0 and 0==i? 以下编码风格有什么

我是一只小鸟

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城