環(huán)境：?Oracle 19.16 多租戶架構(gòu)
經(jīng)常會在網(wǎng)上看到有人寫exists和in的效率區(qū)別，其實在新版本的數(shù)據(jù)庫中，是不存在這個問題的，優(yōu)化器會自己判斷選擇最優(yōu)的執(zhí)行計劃。

為了直觀的說明，我在PDB中構(gòu)造如下測試用例：

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;

構(gòu)造小表T1，大表T2。

SQL> set timing onSQL> @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

網(wǎng)上說，當T1數(shù)據(jù)量小，而T2數(shù)據(jù)量非常大時，使用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對應(yīng)的SQL Monitor Report，對比分析發(fā)現(xiàn)：
二者執(zhí)行計劃完全一樣，對應(yīng)Plan Hash Value 1713220790，都走的是Hash Join Semi，執(zhí)行時間也沒差別。
所以這個說法最起碼在Oracle 19c的版本中是不存在的，你想怎么寫都OK，優(yōu)化器會幫你做查詢轉(zhuǎn)換。

為了進一步驗證，構(gòu)造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的截圖就不貼了，直接給大家看下文本格式的執(zhí)行計劃，方便對比和檢索：

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.07SQL> @xplanPLAN_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.06SQL> @xplanPLAN_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.09SQL> @xplanPLAN_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.09SQL> @xplanPLAN_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
執(zhí)行計劃一樣，都是HASH JOIN SEMI。

對比后兩個SQL：
1588n6cc48yv0
99fkm9p94agcf
執(zhí)行計劃也一樣，都是HASH JOIN RIGHT SEMI。

所以，在新版本的數(shù)據(jù)庫中，確實是不用再關(guān)注這個問題，優(yōu)化器會幫助我們做好最優(yōu)的查詢轉(zhuǎn)換。