Oracle 高效匹配：A 表 ID 前綴關聯 B 表編號的 SQL 寫法與優化

需求：表 A（10 萬筆）與表 B（4 千筆），當 A.A_ID 的開頭部分等於 B.A_NUM 時，將 B.VALUE 關聯至 A。本文提供標準 SQL、解析執行計畫、並給出大數據量下的優化策略。

標準解法：使用 INSTR 或 SUBSTR

-- 寫法 1：INSTR (字串搜尋，返回位置，1 表示從開頭匹配)
SELECT a.*, b.value
FROM   a, b
WHERE  INSTR(a.a_id, b.a_num) = 1;

-- 寫法 2：SUBSTR + 長度比對 (語意更清晰，利於索引)
SELECT a.*, b.value
FROM   a
JOIN   b
  ON   SUBSTR(a.a_id, 1, LENGTH(b.a_num)) = b.a_num;

原理解析：

• INSTR(a.a_id, b.a_num) = 1：檢查 b.a_num 是否出現在 a.a_id 的第 1 字元位置，即前綴匹配。
• SUBSTR(a.a_id, 1, LENGTH(b.a_num))：截取 A_ID 前 N 碼（N = B.A_NUM 長度）直接比對。

大數據量 (10萬 vs 4千) 優化關鍵

1. 為關聯欄位建立索引

-- B 表較小，A_NUM 應建索引
CREATE INDEX idx_b_a_num ON b(a_num);

-- A 表若經常做前綴查詢，可考慮函數索引 (Oracle 11g+)
CREATE INDEX idx_a_a_id_prefix ON a(SUBSTR(a_id, 1, 20)); -- 20 為最大 A_NUM 長度

2. 改寫為 EXISTS 或 IN 利用半連接

若只需 B 表的 VALUE 且 B 表 A_NUM 唯一：

SELECT a.*, 
       (SELECT b.value FROM b WHERE INSTR(a.a_id, b.a_num) = 1) AS b_value
FROM   a
WHERE  EXISTS (SELECT 1 FROM b WHERE INSTR(a.a_id, b.a_num) = 1);

Oracle 優化器對 EXISTS 往往能產生更優的 HASH JOIN SEMI 或 NESTED LOOPS SEMI 計畫，避免笛卡兒積再過濾。

3. 若 A_NUM 長度固定 (如均為 6 碼)，直接用定長截取

SELECT a.*, b.value
FROM   a
JOIN   b
  ON   SUBSTR(a.a_id, 1, 6) = b.a_num; -- 固定長度，可直接走函數索引

4. 統計資訊收集

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'A', cascade=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'B', cascade=>TRUE);

確保優化器有準確基數估算，選擇正確連接順序（小表驅動大表）。

執行計畫觀察重點

• Access Path：是否出現 INDEX RANGE SCAN 或 INDEX UNIQUE SCAN（B 表）。
• Join Method：HASH JOIN 適合大表關聯；NESTED LOOPS 適合小表驅動大表且有索引。
• Filter Predicate：若見 FILTER (INSTR(...)=1) 在大表全掃描後，說明索引未生效，需建函數索引或改寫。

替代方案：應用層預處理 (極大數據量)

若 A 表達百萬級以上，SQL 關聯仍慢，可考慮：

1. 將 B 表 A_NUM 載入應用記憶體 (HashSet/Trie)。
2. 分批讀取 A 表，應用層做前綴匹配，批次更新回寫。
3. 或使用 Oracle GLOBAL TEMPORARY TABLE 暫存 B 表關鍵字，再做集合運算。

總結

核心 SQL：WHERE INSTR(a.a_id, b.a_num) = 1 或 SUBSTR(a.a_id, 1, LENGTH(b.a_num)) = b.a_num。
大數據量下，為 B.A_NUM 建索引、為 A.A_ID 前綴建函數索引、收集統計資訊、改寫為 EXISTS/半連接 是關鍵優化手段。先跑 EXPLAIN PLAN 確認走索引，再視情況調整。