萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 複合主鍵去重查詢技巧:多欄位串聯比對與 NVL 處理 NULL 陷阱

跨表匯入資料時需比對多欄位組合是否重複(複合主鍵場景)。直接 GROUP BY HAVING COUNT(*)>1 僅能找出重複,無法直接過濾「來源表有、目標表無」的差集資料。本文介紹多欄位串聯比對法,並解決 NULL 導致串聯失效的經典陷阱。

問題場景

來源表 t1、目標表 t2,依 (col1, col2, col3) 判斷唯一性。需找出 t1 中「在 t2 不存在」的組合並插入。

核心技巧:串聯 + NVL 處理 NULL

Oracle 中 NULL || 'anything' = NULL,若任一欄位為 NULL,整串串聯結果變 NULL,導致比對失效。解法:每欄位先用 NVL(col, '') 轉為空字串再串聯。

INSERT INTO t2 (col1, col2, col3, ...)
SELECT col1, col2, col3, ...
FROM t1
WHERE NVL(col1,'') || '|' || NVL(col2,'') || '|' || NVL(col3,'')
      NOT IN (
        SELECT NVL(col1,'') || '|' || NVL(col2,'') || '|' || NVL(col3,'')
        FROM t2
      );

關鍵細節

  • 使用分隔符(如 '|'、'#')避免「1+23」與「12+3」串聯衝突。
  • NVL(col, '') 僅適用字串型欄位;數值/日期型需 NVL(TO_CHAR(col), '')。
  • 大量資料時 NOT IN 子查詢可能效能不佳,改用 NOT EXISTS 或 LEFT JOIN ... WHERE t2.pk IS NULL。

進階:MERGE 寫法(10g+ 推薦)

MERGE INTO t2 target
USING (
  SELECT col1, col2, col3, other_cols
  FROM t1
) source
ON (NVL(target.col1,'') = NVL(source.col1,'')
    AND NVL(target.col2,'') = NVL(source.col2,'')
    AND NVL(target.col3,'') = NVL(source.col3,''))
WHEN NOT MATCHED THEN
  INSERT (col1, col2, col3, other_cols)
  VALUES (source.col1, source.col2, source.col3, source.other_cols);
  • MERGE 單陳述式完成「查找-插入」,原子性更強、計畫更穩定。
  • 可加入 WHEN MATCHED THEN UPDATE 同步更新異動欄位。

效能建議

  1. 在 t2(col1, col2, col3) 建立複合唯一索引,加速查找並強制唯一性。
  2. 若欄位含長字串,考慮 ORA_HASH 或 DBMS_CRYPTO.HASH 產生指紋欄位索引,比對指紋而非原文。
  3. 定期 ANALYZE / DBMS_STATS 確保統計資訊準確。

多欄位串聯比對是處理複合鍵去重的經典技巧,關鍵在於統一分隔符與NULL 轉空字串,配合 MERGE 與複合索引可構建高效、正確的資料同步管線。

copyright © 萬盛學電腦網 all rights reserved