在Oracle數據庫中，IN和NOT IN的查詢效率受多種因素影響，以下是關鍵點總結和優(yōu)化建議：

1.IN的效率

• 優(yōu)化方式：
  • IN 通常會被優(yōu)化為 OR條件 或 半連接（Semi-Join），如果子查詢關聯(lián)到外部表，可能轉為 EXISTS。
  • 若字段有索引，且優(yōu)化器選擇索引掃描（Index Scan），效率較高。
• 適用場景：
  • 靜態(tài)值列表較短時（例如 IN (1,2,3)）。
  • 子查詢結果集較小且能利用索引時。

2.NOT IN的潛在問題

• NULL 值陷阱：
  如果子查詢結果包含 NULL，NOT IN 會導致結果集為空（邏輯上等價于 != ALL）。需確保子查詢字段非空（如添加 WHERE col IS NOT NULL）。
• 效率問題：
  • 若子查詢結果集較大，NOT IN 可能需要全表掃描，效率較低。
  • 可能被優(yōu)化為 反連接（Anti-Join），但需索引支持。
• 替代方案：
  優(yōu)先使用 NOT EXISTS，避免 NULL 問題且通常更高效（尤其在子查詢能利用索引時）。

3. 優(yōu)化建議

• 使用 EXISTS/NOT EXISTS 替代：

  -- 優(yōu)于 NOT IN
  SELECT * FROM table1 t1 
  WHERE NOT EXISTS (
    SELECT 1 FROM table2 t2 WHERE t2.id = t1.id
  );
  

  • EXISTS 在找到匹配項后立即終止子查詢，減少計算量。
  • 對 NULL 安全，無需額外處理。

• 確保索引有效：

  • 為 IN/NOT IN 涉及的字段創(chuàng)建索引（尤其是主鍵或高選擇性字段）。
  • 子查詢的連接字段（如 t2.id）應建立索引。

• 處理長靜態(tài)列表：

  • 避免超過1000個元素的靜態(tài)列表（如 IN (1,2,...,1001)），可改用臨時表或拆分查詢。

• 檢查執(zhí)行計劃：
  使用 EXPLAIN PLAN 分析查詢是否走索引或優(yōu)化為高效的連接方式（如哈希反連接）。

4. 示例對比

場景：查詢在表B中不存在的記錄

• 低效寫法（可能受NULL影響）：

  SELECT * FROM tableA 
  WHERE id NOT IN (SELECT id FROM tableB);
  

• 高效改寫：

  SELECT * FROM tableA a 
  WHERE NOT EXISTS (
    SELECT 1 FROM tableB b WHERE b.id = a.id
  );
  

5. 關鍵總結

通過合理使用索引、避免 NULL 陷阱、改寫為 EXISTS/NOT EXISTS，并結合執(zhí)行計劃分析，可以顯著提升查詢效率。

附：Oracle中not in ()語法問題

在SQL查詢中使用isnotin子查詢時遇到的問題，即當子查詢返回值包含null時，條件始終無法匹配導致查詢失敗。作者通過排查發(fā)現并解釋了這一現象，指出isnotin與null值的交互會導致條件始終返回false。解決方案是在子查詢中過濾掉null值，通過添加'wherexxxisnotnull'來修復問題。

最近遇到一個SQL，where條件中用了is not in (子查詢）的語法來過濾數據，但整個SQL執(zhí)行時一直查不到東西

排查了下，最后定位到問題出在這個is not in ()條件中，于是將括號里面的子查詢執(zhí)行了下，發(fā)現他查詢的字段中有一條數據為null值

剛開始沒看出有啥問題，百度一波后發(fā)現，如果is not in ()子查詢返回值中有null值，那這個條件始終會返回false，導致整個SQL啥都查詢不到。

所以修復的方法就是在子查詢中過濾掉空值，子查詢后加上 "where xxx is not null"即可

到此這篇關于oracle中使用in和not in查詢效率總結和優(yōu)化建議的文章就介紹到這了,更多相關oracle in和not in查詢效率內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論