您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 兩表資料比對實戰：手動導出法 vs SQL 集合運算法，大數據量場景選型指南

編輯：Oracle 資料庫基礎

業務場景

需驗證兩表 (A、B) 資料是否完全一致。結構相同，含可為 NULL 的欄位。兩表可能在同資料庫或跨資料庫 (DBLINK)。

方法一：手動導出比對法 (適合小表、一次性驗證)

操作流程

兩表依相同規則排序 (如 PK 欄位)
PL/SQL Developer / SQL*Plus 導出為 XLS/CSV
轉換為 TXT (批次工具或另存新檔)
Beyond Compare / WinMerge / Diff 工具比對

優缺點

優點	缺點
差異可視化極佳 (逐行高亮)	耗時：導出、轉檔、載入比對工具
無需寫 SQL，業務人員可操作	受限於客戶端記憶體與匯出上限 (百萬級困難)
適合臨時、小規模、需人工審閱差異	無法自動化納入 CI/CD

方法二：SQL 集合運算法 (適合大表、自動化、生產核對)

核心邏輯：對稱差集 = (A - B) ∪ (B - A)

建表範例：

CREATE TABLE formula (
  id              VARCHAR2(50) PRIMARY KEY,
  formula_name    VARCHAR2(50),
  formula_content VARCHAR2(2000),
  formula_type    VARCHAR2(20)
);

除 PK 外皆可為 NULL。

關鍵難點：NULL 比較

Oracle 中 NULL = NULL 為 UNKNOWN，非 TRUE。直接 A.* = B.* 無法識別雙方同為 NULL 的行。

解決方案：NVL/COALESCE 統一轉換或集合運算

方案 1：MINUS 集合運算 (推薦，簡潔高效)

-- A 有、B 無 (含 NULL 語意正確)
SELECT * FROM A
MINUS
SELECT * FROM B;

-- B 有、A 無
SELECT * FROM B
MINUS
SELECT * FROM A;

MINUS 自動處理 NULL 相等語意：兩行所有欄位對應值相等 (含同為 NULL) 則視為相同並排除。兩個查詢皆回傳空集 → 兩表完全一致。

方案 2：FULL OUTER JOIN + 異常過濾

SELECT A.id, B.id,
       CASE WHEN A.id IS NULL THEN 'ONLY_IN_B'
            WHEN B.id IS NULL THEN 'ONLY_IN_A'
            ELSE 'MISMATCH' END AS diff_type
FROM A
FULL OUTER JOIN B ON A.id = B.id
WHERE (A.formula_name != B.formula_name OR (A.formula_name IS NULL AND B.formula_name IS NOT NULL) OR (A.formula_name IS NOT NULL AND B.formula_name IS NULL))
   OR (A.formula_content != B.formula_content OR ...)
   OR (A.formula_type != B.formula_type OR ...);

冗長但可輸出差異類型與具體欄位值。

方案 3：HASH 值比對 (超大表快速預檢)

-- 計算整行雜湊
SELECT id, ORA_HASH(formula_name || '|' || formula_content || '|' || formula_type) AS row_hash
FROM A;

-- 比對雜湊值分佈
SELECT row_hash, COUNT(*) FROM A GROUP BY row_hash
MINUS
SELECT row_hash, COUNT(*) FROM B GROUP BY row_hash;

快速定位差異分區，再針對性逐行比對。

跨資料庫比對：DBLINK

CREATE DATABASE LINK remote_db CONNECT TO user IDENTIFIED BY pwd USING 'tns_alias';

SELECT * FROM A@remote_db MINUS SELECT * FROM B;

效能對比與選型決策樹

場景	推薦方法	理由
< 10 萬行、臨時驗證、需人工審閱差異	手動導出 + Beyond Compare	可視化最強、零 SQL 門檻
10 萬 - 1 億行、同庫、定期自動化核對	MINUS 集合運算 + 排程	SQL 原生、NULL 語意正確、可索引加速
> 1 億行、跨庫、極高效能要求	HASH 分桶比對 + 並行查詢	減少網路傳輸、利用並行度
需輸出差異欄位級詳細報表	FULL OUTER JOIN + CASE	可精確定位哪欄位不同

優化技巧

確保比對欄位 (尤其是 PK) 有索引
大表加 PARALLEL hint：SELECT /*+ PARALLEL(8) */ * FROM A MINUS SELECT * FROM B
分區表按分區比對，減少單次掃描量
定期收集統計資訊確保優化器選擇 HASH JOIN (MINUS 內部常用)

自動化核對排程範本

CREATE OR REPLACE PROCEDURE compare_tables_daily AS
  v_diff_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_diff_count FROM (
    SELECT * FROM table_a MINUS SELECT * FROM table_b
  );
  v_diff_count := v_diff_count + (
    SELECT COUNT(*) FROM (
      SELECT * FROM table_b MINUS SELECT * FROM table_a
    )
  );
  
  IF v_diff_count > 0 THEN
    -- 發送告警郵件/寫入審計表
    send_alert('TABLE_SYNC_MISMATCH', 'table_a vs table_b', v_diff_count);
  END IF;
END;
/

-- 排程每日 03:00 執行
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'DAILY_TABLE_COMPARE',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'COMPARE_TABLES_DAILY',
    start_date      => TRUNC(SYSDATE+1) + 3/24,
    repeat_interval => 'FREQ=DAILY; BYHOUR=3; BYMINUTE=0',
    enabled         => TRUE
  );
END;
/

資料比對的本質是「集合對稱差集」計算。MINUS 以 SQL 原生語意完美處理 NULL 相等，是中大型表自動化核對的首選。手工導出法保留給「需人眼確認差異細節」的臨時場景。工具選型以「可自動化、可擴展、語意正確」為最高原則。