Oracle中exists和in的效能差異

chuimber發表於2024-08-12
Oracle

關於exists和in

exists關鍵字和in關鍵字都能實現外表查詢後的結果過濾功能。在SQL語句效能最佳化方面，建議exists代替in進行子查詢，實際上二者分場景進行使用。

低效
SELECT *
FROM EMP (基礎表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = 'MELB')
高效
SELECT *
FROM EMP (基礎表)
WHERE EMPNO > 0
AND EXISTS (SELECT 'X'
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = 'MELB')
  • exists不關注子表查詢之後的結果資料，只關心是否有符合條件後的資料記錄的產生，這意味著最佳化器可以更早的停止執行子查詢
  • in會查詢所有子表中資料，其次外表進行查詢時，會將每一行資料進行與子結果集比對

由此看來，exists是要比in效能更好，速度更快

二者內部執行規則

實際上，二者在執行查詢方面存在不同。

  • 對於in，一般會先執行子查詢，因為in會關注子查詢的結果資料，所以會將子查詢的結果快取，然後執行外表查詢，將每一行的資料與結果集比對，過濾結果，子查詢的實際執行次數取決於子錶行數。
  • 對於exists，因為不會關注子查詢的結果資料本身，一般並不會快取子查詢結果，所以執行順序為外表驅動內表，查詢外表的每一行之後再執行exists子句，但是基於上述所說的只要子查詢有記錄產生，便條件為真，即可返回結果，它不需要實際獲取子查詢的所有結果。子查詢的實際執行次數取決於外表行數。

這樣，存在兩種不同的應用場景，當外表資料量遠大於子表時，推薦使用in關鍵字，當內表資料遠大於子表時，推薦使用exists關鍵字。

時間複雜度的粗略比較

可以對兩者的時間複雜度進行粗略計算和比較，首先假設表中不存在任何欄位的索引，因為對於有索引欄位的查詢，時間複雜度可以從O(n)最佳化到O(logn)，為了對比，現在不考慮索引的情況，且外表m行，內表n行。

  • in

    • 先快取子查詢結果，子查詢O(n)，再做外表查詢O(m)，再做笛卡爾積比對運算，O(m*n)

  • exists

    • 對外表查詢O(m),內表非全查詢，理想情況查詢第一條就符合條件直接終止子查詢返回結果，最壞情況最後一條符合條件，子表介於O(1)到O(n)，總O(m)~O(m*n)

粗略的比對，可以看出exist效率較高。

CBO最佳化器

對於Oracle 11g 版本後，引入了 Cost-Based Optimizer (CBO) ，CBO 最佳化器會根據統計資訊來決定查詢的最佳執行路徑。不必過於糾結in和exists

相關文章