IN和EXISTS的用法及效率验证

最新推荐文章于 2024-03-14 07:45:00 发布

supeerzdj

最新推荐文章于 2024-03-14 07:45:00 发布

阅读量190

点赞数

文章标签：前端

本文链接：https://blog.csdn.net/supeerzdj/article/details/131632738

版权

在Oracle19.16的多租户环境中，通过测试发现exists和in在新版本数据库中的查询效率差异不大，因为优化器会自动选择最优执行计划。测试用例显示，无论使用exists还是in，执行计划均为HashJoinSemi，且执行时间无显著差别。这表明在最新版本的Oracle中，无需过于关注exists和in的选择问题，优化器会进行有效的查询转换。

摘要由CSDN通过智能技术生成

环境： 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(*)
------------- --