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

知识,in,exists,用法,效率,验证 · 浏览次数 : 278

小编点评

生成内容时需要带简单的排版，例如： 1. **SQL ID**: 在每个SQL语句前面加上其ID，方便排版。 2. ** SQL PLAN HASH VALUE**: 在每个SQL语句前面加上其HASH VALUE，方便排版。 3. ** SQL PLAN LINE ID**: 在每个SQL语句前面加上其LINE ID，方便排版。 4. ** COUNT(*)**: 在每个SQL语句后面加上其COUNT(*)，方便排版。 5. ** ORDER BY**: 在每个SQL语句后面加上其ORDER BY，方便排版。 6. ** WHERE clause**: 在每个WHERE clause后面加上其条件，方便排版。 7. ** GROUP BY**: 在每个GROUP BY clause后面加上其条件，方便排版。 8. ** ORDER BY**: 在每个ORDER BY clause后面加上其条件，方便排版。

正文

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

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