Oracle ORA-01795 錯誤解析：IN 條件超過 1000 個運算式限制

問題背景

週末本該放鬆，卻因 Production 環境拋出 SQLException: ORA-01795: maximum number of expressions in a list is 1000 而被迫介入排查。這是 Oracle 資料庫的一項硬性限制：IN 條件清單中運算式數量不能超過 1000 個。

錯誤重現場景

典型觸發場景如下：

UPDATE MERCHANT_ID 
SET STATUS = 'A' 
WHERE MERCHANT_ID IN (1,2,3,4,5,6... -- 超過 1000 個值)

當 IN 子句中的值超過 1000 個時，Oracle 即拋出 ORA-01795 錯誤。

解決方案

方案一：分批處理（推薦）

將大清單拆分為多個小於 1000 的批次，搭配 OR 條件或 UNION ALL：

-- 範例：分批次各 900 條
WHERE MERCHANT_ID IN (前900個值)
   OR MERCHANT_ID IN (後900個值)
   OR MERCHANT_ID IN (剩餘值)

方案二：使用暫存表 / 全域暫存表

1. 建立暫存表 GLOBAL TEMPORARY TABLE temp_ids (id NUMBER)
2. 批次插入所有需查詢的 ID
3. 使用 JOIN 或 EXISTS 替代 IN：

SELECT * FROM MERCHANT_ID m
WHERE EXISTS (SELECT 1 FROM temp_ids t WHERE t.id = m.MERCHANT_ID)

方案三：使用 PL/SQL 集合類型

CREATE OR REPLACE TYPE num_array IS TABLE OF NUMBER;

DECLARE
    v_ids num_array := num_array(1,2,3...); -- 可超過 1000
BEGIN
    FORALL i IN v_ids.FIRST..v_ids.LAST
        UPDATE MERCHANT_ID SET STATUS = 'A' WHERE MERCHANT_ID = v_ids(i);
END;

方案四：使用子查詢

若 ID 來源於另一張表，直接用子查詢：

UPDATE MERCHANT_ID 
SET STATUS = 'A' 
WHERE MERCHANT_ID IN (SELECT id FROM source_table WHERE condition)

最佳實踐建議

• 設計階段：避免單一 IN 清單超過 1000 個值
• 程式碼層面：在資料存取層自動分批處理大清單
• 效能考量：暫存表 + JOIN 通常比巨大 IN 清單效能更好
• 監控告警：建立資料庫錯誤日誌監控，及時發現此類問題

總結

ORA-01795 是 Oracle 為防止 SQL 解析過度複雜而設的保護機制。透過暫存表、集合類型或分批處理，均能優雅繞過此限制。建議在架構層面統一處理，避免業務邏輯散落各處。