資料清洗實戰筆記：主鍵 ID 字段生成兩大方案 (SYS_GUID vs 序列觸發器)

資料清洗實戰筆記：主鍵 ID 字段生成兩大方案 (SYS_GUID vs 序列觸發器)

在從源端抽取資料載入目標端後，常需為目標表補充主鍵 ID。本文對比 Oracle 環境下兩種主流生成方案：SYS_GUID() 全域唯一識別碼與 SEQUENCE + TRIGGER 自增數值，並給出 DDL 範例。

方案一：SYS_GUID() — 32 位十六進位全域唯一值

特點

• 無序、不可預測、跨資料庫/跨節點唯一
• 適合分散式系統、合併資料無衝突
• 索引分裂較嚴重 (隨機插入)，大表效能需測試
• 佔用 32 字元 (VARCHAR2(32) / RAW(16))

實作：欄位預設值

ALTER TABLE emp ADD id VARCHAR2(32) DEFAULT SYS_GUID();

後續 INSERT INTO emp (...) VALUES (...) 不指定 id 即可自動生成。

方案二：SEQUENCE + BEFORE INSERT TRIGGER — 有序數值

特點

• 單調遞增、長度固定、人類可讀、索引極其友好 (右側增長)
• 需維護序列物件、觸發器邏輯
• 跨庫合併需規劃序列範圍 (如 INCREMENT BY 1000 + START WITH 錯開)

實作步驟

-- 1. 建立序列 (自定義起始值、步長、快取、循環)
CREATE SEQUENCE seq_id_add
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- 2. 建立觸發器
CREATE OR REPLACE TRIGGER trg_emp_pk
BEFORE INSERT ON emp1
FOR EACH ROW
BEGIN
    SELECT seq_id_add.NEXTVAL INTO :NEW.id FROM dual;
END;
/
-- 3. 插入測試
INSERT INTO emp1 (ename, sal) VALUES ('TEST', 5000);
SELECT * FROM emp1;  -- id 自動填入 1, 2, 3...

選型決策表

進階補充：Oracle 12c+ Identity Column

CREATE TABLE emp2 (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ename VARCHAR2(50)
);
-- 內建序列+觸發器，語法標準、效能最優、無額外物件

根據系統架構 (單體 vs 分散式)、寫入頻率、合併需求、索引維護成本綜合選型。