Oracle 自增主鍵實作指南：Sequence + Trigger 完整範例

前言：Oracle 無 Identity 欄位，如何實現自增主鍵？

Oracle 12c 前無 GENERATED AS IDENTITY 語法，傳統做法是 Sequence（序列）+ Trigger（觸發器） 組合。即使 12c+ 支援 Identity，Sequence+Trigger 仍因靈活性（跨表共用序列、自定義快取、手動控制）被廣泛使用。本文提供生產級完整範例，涵蓋建表、建序列、建觸發器、效能調優、常見坑點。

一、建立業務表

CREATE TABLE users (
  id        NUMBER(18) NOT NULL,
  username  VARCHAR2(50) NOT NULL,
  password  VARCHAR2(100) NOT NULL,
  email     VARCHAR2(100),
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT pk_users PRIMARY KEY (id)
) TABLESPACE users_data
PCTFREE 10
STORAGE (INITIAL 64M NEXT 64M MAXEXTENTS UNLIMITED);

二、建立序列

CREATE SEQUENCE seq_users_id
  START WITH 1
  INCREMENT BY 1
  NOCACHE          -- 或 CACHE 1000（高併發建議）
  NOMAXVALUE
  NOCYCLE
  NOORDER;         -- RAC 環境建議 ORDER 保證全域有序

參數決策指南

三、建立 BEFORE INSERT 觸發器

CREATE OR REPLACE TRIGGER trg_users_bir
BEFORE INSERT ON users
FOR EACH ROW
WHEN (NEW.id IS NULL)  -- 僅在未指定 ID 時自動填充，支援手動指定 ID（如資料遷移）
BEGIN
  SELECT seq_users_id.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/
ALTER TRIGGER trg_users_bir ENABLE;

關鍵設計點

1. WHEN 子句：避免不必要的 Sequence 呼叫，支援「手動指定 ID」場景（資料遷移、種子資料）。
2. FOR EACH ROW：列級觸發，確保每筆 INSERT 取一個序列值。
3. 無例外處理：Sequence 耗盡極罕見，若發生讓錯誤上拋給應用層處理。

四、Oracle 12c+ Identity Column 寫法（現代替代方案）

CREATE TABLE users_new (
  id NUMBER(18) GENERATED ALWAYS AS IDENTITY
      (START WITH 1 INCREMENT BY 1 CACHE 1000 NOORDER),
  username VARCHAR2(50) NOT NULL,
  ...
);

• GENERATED ALWAYS：不可手動指定 ID（遷移時需 OVERRIDING SYSTEM VALUE）。
• GENERATED BY DEFAULT ON NULL：NULL 時自動填充，非 NULL 時接受手動值（最接近 Trigger 行為）。
• 效能優於 Trigger（無 Context Switch），但靈活性稍低（難以跨表共用序列）。

五、效能壓測與調優

高併發插入測試腳本

-- 10 個 Session 各插 10,000 筆
DECLARE
  v_start NUMBER := DBMS_UTILITY.GET_TIME;
BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO users (username, password, email)
    VALUES ('user_'||i, 'pwd', 'a@b.com');
  END LOOP;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Elapsed cs: ' || (DBMS_UTILITY.GET_TIME - v_start));
END;
/

觀察指標

• Sequence Cache 爭用：V$ENQUEUE_STAT WHERE EQ_TYPE = 'SQ'。
• Redo Log 生成量：V$SYSSTAT 'redo size'。
• Trigger Context Switch 開銷：V$SYSSTAT 'recursive cpu usage'。

調優建議

• Sequence CACHE 10000 + NOORDER（非 RAC）可將 Sequence 待機時間降至微秒級。
• 批次插入改用 FORALL + INSERT /*+ APPEND */ 繞過 Trigger（需手動取序列值填入集合）。
• 極高併發（>5000 TPS）考慮 SHARDING 或應用層 ID 生成（Snowflake/UUID）。

六、常見坑點與解法

七、資料遷移時的 Sequence 校準

-- 遷移歷史資料後，將 Sequence 推進至 MAX(ID) 以上
SELECT 'ALTER SEQUENCE seq_users_id INCREMENT BY ' || (max_id - curr_val + 1000) || ';'
FROM (SELECT MAX(id) max_id FROM users),
     (SELECT seq_users_id.CURRVAL curr_val FROM dual);

-- 執行上述動態 SQL，再改回 INCREMENT BY 1
ALTER SEQUENCE seq_users_id INCREMENT BY 1;

結語

Sequence + Trigger 是 Oracle 自增主鍵的經典模式，關鍵在於：CACHE 參數對效能的決定性影響、WHEN 子句對遷移相容性的支援、RAC 環境下 ORDER/NOORDER 的架構決策。12c+ 專案可優先評估 Identity Column，但掌握傳統模式調優細節，對維護既有系統、跨版本遷移仍具核心價值。