環境： 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的截圖就不貼了，直接給大家看下文字格式的執行計劃，方便對比和檢索：
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
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
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
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。
所以，在新版本的資料庫中，確實是不用再關注這個問題，優化器會幫助我們做好最優的查詢轉換。