Oracle 判斷記錄是否存在的高效寫法：避免 COUNT(*) 全表掃描陷阱

反模式：COUNT(*) 存在性檢查

SELECT COUNT(*) INTO V_CNT FROM T WHERE CONDITION;
IF V_CNT > 0 THEN ...

缺陷：僅需布爾判斷卻強制全表/全索引掃描聚合，大表性能災難。

場景一：存在即讀取列值 / 決定 UPSERT

改寫 A：直接查詢 + NO_DATA_FOUND 異常捕獲

BEGIN
  SELECT COL1, COL2 INTO V1, V2 FROM T WHERE CONDITION;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- 不存在分支
    NULL;
  WHEN OTHERS THEN
    RAISE;
END;

• 存在時單次索引/表訪問即返回
• 不存在拋異常，開銷極低
• 適用於「大概率存在」場景

改寫 B：UPDATE + SQL%NOTFOUND 判斷

UPDATE T SET COL = VAL WHERE CONDITION;
IF SQL%NOTFOUND THEN
  INSERT INTO T ...;
END IF;

• 單語句完成存在性檢查+修改
• 不存在時轉 INSERT
• 適用於「大概率更新」場景

改寫 C：INSERT + DUP_VAL_ON_INDEX 異常捕獲

BEGIN
  INSERT INTO T ...;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UPDATE T SET ... WHERE CONDITION;
END;

• 依賴唯一約束/主鍵
• 適用於「大概率新增」場景
• 12c+ 推薦 MERGE 語句替代

場景二：純粹判斷存在性，不訪問列值

改寫：EXISTS 子查詢 + ROWNUM/常量

SELECT 1 INTO V_FLAG FROM DUAL
WHERE EXISTS (SELECT 1 FROM T WHERE CONDITION);
IF V_FLAG = 1 THEN ...

• EXISTS 語義：滿足第一行即停止，不聚合
• SELECT 1 避免列解析開銷
• FROM DUAL 保證單行返回
• 優化器通常轉為 FILTER 或 SEMI JOIN，極快

對比：COUNT(*) vs EXISTS 執行計劃

-- COUNT(*)
SELECT COUNT(*) FROM T WHERE ID = 100;
-- Plan: INDEX RANGE SCAN -> SORT AGGREGATE

-- EXISTS
SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM T WHERE ID = 100);
-- Plan: INDEX RANGE SCAN -> FILTER (STOPKEY)

特殊場景：極小表/單行表

若表僅幾行或單行（如參數表），直接 SELECT COUNT(*) 或 SELECT * 反而更快，因為優化器可能選擇全表掃描且無索引開銷。

PL/SQL 封裝函數模板

CREATE OR REPLACE FUNCTION RECORD_EXISTS(
  P_TABLE  VARCHAR2,
  P_WHERE  VARCHAR2
) RETURN BOOLEAN IS
  V_SQL  VARCHAR2(32767);
  V_DUMMY NUMBER;
BEGIN
  V_SQL := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || P_TABLE || ' WHERE ' || P_WHERE || ')';
  EXECUTE IMMEDIATE V_SQL INTO V_DUMMY;
  RETURN TRUE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RETURN FALSE;
END;
/
-- 調用
IF RECORD_EXISTS('EMP', 'DEPTNO=20 AND SAL>3000') THEN ...

總結：選型決策表

核心原則

「不要為了得到 TRUE/FALSE 而付出聚合全表的代價」 — 讓優化器在找到第一匹配行時立即停止。