不同於 MySQL AUTO_INCREMENT、SQL Server IDENTITY、PostgreSQL SERIAL，Oracle 歷史上透過 **Sequence（序列）+ Trigger（觸發器）** 實現自增，12c 起引入標準 **IDENTITY 列**。兩種模式並存，各有優勢。
CREATE SEQUENCE seq_test
MINVALUE 1
MAXVALUE 9999999999999999999999999999
START WITH 1
INCREMENT BY 1
NOCACHE -- 不緩存：避免實例重啟/故障導致間隙 (效能較差)
-- CACHE 20 -- 生產建議：緩存 20-100，極大提升高併發效能
NOORDER; -- RAC 環境不保證全局有序 (單機可 ORDER)CACHE 參數關鍵權衡：
NOCACHE：每次NEXTVAL寫入磁碟，**零間隙**，但高併發嚴重爭用 Sequence 緩存鎖CACHE 100：記憶體預取 100 個值，**效能提升 10-100 倍**，但實例崩潰/重啟會丟失未用值 → **主鍵間隙** (業務通常可接受)
CREATE OR REPLACE TRIGGER trg_test_auto_id
BEFORE INSERT ON test
FOR EACH ROW
DECLARE
v_next_id NUMBER;
BEGIN
-- 僅當 ID 為 NULL 或 0 時自動賦值 (支援業務手動指定 ID)
IF :NEW.id IS NULL OR :NEW.id = 0 THEN
SELECT seq_test.NEXTVAL INTO v_next_id FROM DUAL;
:NEW.id := v_next_id;
END IF;
END;
/
SHOW ERRORS:NEW 與 :OLD 關鍵字：
:NEW：INSERT/UPDATE 時的**新值**；INSERT 時原值為 NULL:OLD：UPDATE/DELETE 時的**舊值**；DELETE 時新值為 NULL- Trigger 只能在
BEFORE階段修改:NEW，AFTER已寫入不可改
INSERT INTO test (name, location, value) VALUES ('A', 'Loc1', 10);
INSERT INTO test (name, location, value) VALUES ('B', 'Loc2', 20);
INSERT INTO test (id, name, location, value) VALUES (100, 'C', 'Loc3', 30); -- 手動指定 ID
SELECT * FROM test ORDER BY id;
-- 結果：
-- ID NAME LOCATION VALUE
-- 1 A Loc1 10
-- 2 B Loc2 20
-- 100 C Loc3 30 (手動指定，Sequence 繼續從 3 開始)-- 12c+ 標準語法
CREATE TABLE test_modern (
id NUMBER GENERATED ALWAYS AS IDENTITY, -- 或 GENERATED BY DEFAULT
name VARCHAR2(20),
location VARCHAR2(20),
value NUMBER
);
-- 進階選項
CREATE TABLE test_advanced (
id NUMBER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1 INCREMENT BY 1 CACHE 20 NOORDER),
name VARCHAR2(20)
);| 關鍵字 | 行為 | 適用場景 |
|---|---|---|
GENERATED ALWAYS AS IDENTITY | **強制自增**，INSERT 時不可指定 ID，指定會報錯 | 純代理鍵、嚴格防範手動干擾 |
GENERATED BY DEFAULT AS IDENTITY | **預設自增**，允許 INSERT 手動指定 ID (覆蓋自增) | 遷移舊資料、合併資料、測試需手動控制 ID |
GENERATED BY DEFAULT ON NULL AS IDENTITY | 18c+ 新增：僅當值為 NULL 才自增 (等同 Trigger 邏輯) | 最靈活，兼容手動指定與自動賦值 |
| 維度 | Sequence + Trigger | IDENTITY 列 (12c+) |
|---|---|---|
| 效能 | Trigger 開銷：每行額外 PL/SQL 呼叫、Context Switch | **內核級實現**，零 PL/SQL 開銷，效能提升 30-50% |
| 遷移相容性 | 所有版本通用 | 僅 12c+，跨版本遷移需改寫 |
| 靈活度 | 可在 Trigger 實現複雜邏輯 (條件賦值、日誌、驗證) | 語法固定，複雜邏輯仍需 Trigger 輔助 |
| 工具支援 | 所有工具識別 | 現代工具 (SQLcl, SQL Developer, ORDS) 完美識別 |
| 間隙控制 | Sequence 參數完全控制 | 內建 Sequence 參數同樣控制 |
| DDL 管理 | 兩個物件 (Sequence, Trigger) 獨立管理 | 單一欄位定義，DROP TABLE 自動清理 |
| 併發級別 | 建議 CACHE | 預估間隙風險 | 備註 |
|---|---|---|---|
| 低 (< 10 TPS) | NOCACHE 或 CACHE 10 | 極低 | 開發/測試環境 |
| 中 (10-1000 TPS) | CACHE 100 | 低 (重啟丟失 < 100) | 大多數生產 OLTP |
| 高 (> 1000 TPS) | CACHE 1000-10000 | 中 (需監控) | 核心交易系統，配合 RAC ORDER |
-- 單機/非嚴格有序需求
CREATE SEQUENCE seq_ord NOORDER; -- 預設，各實例獨立緩存，效能最佳
-- 業務要求全局嚴格遞增 (罕見)
CREATE SEQUENCE seq_ord ORDER; -- 實例間協調，有效能代價-- 慢：逐行 INSERT 觸發 Trigger/Sequence
FOR i IN 1..10000 LOOP
INSERT INTO test (name) VALUES ('Batch_'||i);
END LOOP;
-- 快：FORALL + 批量綁定 (繞過 Trigger 逐行開銷)
DECLARE
TYPE name_arr IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
l_names name_arr;
BEGIN
FOR i IN 1..10000 LOOP l_names(i) := 'Batch_'||i; END LOOP;
FORALL i IN 1..10000
INSERT INTO test (name) VALUES (l_names(i));
END;
/
-- IDENTITY 列下 FORALL 直接由內核批量分配 Sequence 值，極快ALTER TABLE test MODIFY id GENERATED BY DEFAULT AS IDENTITY (12c+ 支援)GENERATED BY DEFAULT ON NULL AS IDENTITY (18c+) 或維持原狀**12c 以前**：Sequence + Trigger 是唯一標準方案，**關鍵優化在於 Sequence CACHE 設定**。**12c 起**：**IDENTITY 列為首選**，效能優越、語法標準、工具友好。遷移時評估 Trigger 附加邏輯，**「能用 IDENTITY 就用 IDENTITY，複雜邏輯輔以 Trigger」**。無論哪種模式，**主鍵間隙是正常現象，業務層切勿依賴連續性**。
