Oracle 判斷記錄是否存在的高效寫法：EXISTS vs COUNT vs ROWNUM 實測對比

業務場景與核心需求

高併發 OLTP 系統中，頻繁需要判斷「某記錄是否存在」 (如主鍵/唯一鍵查詢、業務幂等性校驗、防重複提交)。**目標：最小化邏輯讀、物理讀、CPU、鎖開銷，獲得最優執行計劃。**

三種經典寫法對比測試

測試環境與數據準備

-- 測試表：42,667 行，item 列有唯一索引 (UK)
CREATE TABLE item_test AS SELECT * FROM item WHERE rownum <= 42667;
CREATE UNIQUE INDEX uk_item_test_item ON item_test(item);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ITEM_TEST');

-- 目標值：存在
VARIABLE target VARCHAR2(20);
EXEC :target := '1B241371X0021';

寫法一：ROWNUM < 2 (停止鍵優化)

SELECT * FROM item_test WHERE item = :target AND ROWNUM < 2;
-- 測試結果：1 row selected in 0.047 seconds
-- 執行計劃關鍵：COUNT STOPKEY -> INDEX UNIQUE SCAN (UK_ITEM_TEST_ITEM)
-- 優化器識別 STOPKEY，索引唯一掃描獲取 1 行即停止，不訪問表塊 (若只選擇索引列)
-- 若 SELECT * 需訪問表，則 INDEX UNIQUE SCAN -> TABLE ACCESS BY INDEX ROWID (1 row)

寫法二：COUNT(*) 聚合

SELECT COUNT(*) FROM item_test WHERE item = :target;
-- 測試結果：1 row selected in 0.016 seconds
-- 執行計劃：SORT AGGREGATE -> INDEX UNIQUE SCAN (UK_ITEM_TEST_ITEM)
-- 僅掃描索引，不訪問表，**邏輯讀最少** (通常 1-2 consistent gets)
-- 返回數值 0 或 1，應用層判斷 >0 即存在

寫法三：EXISTS 子查詢 (標準 SQL 存在性檢查)

SELECT COUNT(*) FROM dual WHERE EXISTS (SELECT 1 FROM item_test WHERE item = :target);
-- 測試結果：1 row selected in 0.015 seconds
-- 執行計劃：FILTER -> INDEX UNIQUE SCAN (UK_ITEM_TEST_ITEM) -> FAST DUAL
-- 優化器將 EXISTS 轉為半連接，索引唯一掃描命中即返回，**不聚合、不排序、不訪問表**
-- 邏輯讀同 COUNT(*)，但語義更清晰，可擴展為相關子查詢

深度原理解析：為何 COUNT(*) 與 EXISTS 更快？

擴展場景：非唯一索引 / 無索引 / 範圍查詢

場景 A：非唯一索引 (如狀態碼、類型碼)

-- 目標：判斷是否存在 status='ACTIVE' 的記錄
-- 非唯一索引 idx_item_test_status

-- COUNT(*) 最優：INDEX RANGE SCAN 統計行數，優化器可提前停止 (FIRST_ROWS 模式)
SELECT COUNT(*) FROM item_test WHERE status = 'ACTIVE';

-- EXISTS 同等：半連接 NESTED LOOPS SEMI，命中第一行即返回
SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM item_test WHERE status = 'ACTIVE');

-- ROWNUM < 2 同等：STOPKEY，命中第一行即停止
SELECT * FROM item_test WHERE status = 'ACTIVE' AND ROWNUM < 2;

場景 B：無索引 / 全表掃描 (FTS)

-- 表大、無索引、需判斷是否存在某條件
-- 並行查詢加速 (Enterprise Edition + Partitioning/Parallel 選項)
SELECT /*+ PARALLEL(4) */ COUNT(*) FROM large_table WHERE condition = :val;

-- EXISTS 同樣受益並行
SELECT 1 FROM dual WHERE EXISTS (SELECT /*+ PARALLEL(4) */ 1 FROM large_table WHERE condition = :val);

場景 C：PL/SQL 邏輯中判斷存在性 (最高性能模式)

-- 1. 靜態 SQL 隱式游標 (最簡潔，編譯期綁定計劃)
DECLARE
    l_exists BOOLEAN;
BEGIN
    SELECT 1 INTO l_exists FROM dual WHERE EXISTS (SELECT 1 FROM item_test WHERE item = :target);
    -- l_exists 為 TRUE/FALSE
    IF l_exists THEN ... END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN -- 不存在
        NULL;
END;

-- 2. 顯式游標 + LIMIT 1 (可控獲取)
DECLARE
    CURSOR c IS SELECT 1 FROM item_test WHERE item = :target;
    l_dummy NUMBER;
BEGIN
    OPEN c;
    FETCH c INTO l_dummy;
    IF c%FOUND THEN ... END IF;
    CLOSE c;
END;

-- 3. 避免：SELECT COUNT(*) INTO l_cnt ... IF l_cnt > 0 (多一次聚合運算)

實戰最佳實踐總結

1. 純存在性判斷 (SQL 層)：首選 `SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM table WHERE condition)` 或 `SELECT COUNT(*) FROM table WHERE condition`。**語義清晰、優化器友好、邏輯讀最少、網絡開銷最小**。
2. 需同時取回數據列：`SELECT col1, col2 FROM table WHERE pk = :val AND ROWNUM < 2` (主鍵/唯一鍵) 或 `SELECT col1, col2 FROM table WHERE condition AND ROWNUM < 2` (非唯一，**注意結果非確定性**)。
3. PL/SQL 邏輯判斷：`SELECT 1 INTO l_bool FROM dual WHERE EXISTS (...)` 捕獲 `NO_DATA_FOUND`，或顯式游標 `FETCH ... LIMIT 1`。
4. 避免反模式：
   • `SELECT * FROM table WHERE condition` 然後應用層判斷 `ResultSet.next()` — **全量傳輸數據、邏輯讀最大**。
   • `SELECT COUNT(*) INTO :cnt FROM table WHERE condition` 然後 `IF :cnt > 0` — **多一次聚合**，不如 EXISTS 直觀。
   • 在 `IF` 條件中直接寫 `EXISTS (...)` — **PL/SQL 不支持**，需 `SELECT ... INTO`。
5. 統計信息準確性**：確保表/索引統計信息最新 (`DBMS_STATS.GATHER_TABLE_STATS`)，優化器才能正確選擇 INDEX UNIQUE/RANGE SCAN 而非 FTS。
6. 綁定變量使用**：所有條件值**必須**使用綁定變量 (`:target`)，實現游標共享，避免硬解析風暴。

補充：大數據量分區表存在性判斷

-- 分區表按日期分區，查詢特定分區
SELECT 1 FROM dual WHERE EXISTS (
    SELECT 1 FROM sales PARTITION (p_2024_01) WHERE cust_id = :cid
);
-- 分區剪枝 + 索引唯一掃描，極致性能