方案演進歷程
| 版本 | 方案 | 特點 |
|---|
| ≤ 11g | Sequence + BEFORE INSERT Trigger | 通用、需維護兩個物件 |
| 12c | GENERATED AS IDENTITY | 標準 SQL、內部仍用 Sequence |
| 12c | DEFAULT seq.NEXTVAL | 簡潔、可指定 Sequence 選項 |
| 18c | IDENTITY (標準語法) | 完全符合 SQL:2003 |
傳統方案：Sequence + Trigger
-- 1. 建立 Sequence
CREATE SEQUENCE userlogin_seq
START WITH 1 INCREMENT BY 1
MAXVALUE 9999999999999
NOCACHE NOCYCLE ORDER;
-- 2. 建立 Trigger
CREATE OR REPLACE TRIGGER userlogin_bir
BEFORE INSERT ON userlogin
FOR EACH ROW
BEGIN
SELECT userlogin_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
/
-- 3. 測試
INSERT INTO userlogin (name) VALUES ('Alice');
SELECT * FROM userlogin;
效能缺陷
- 每行觸發器觸發 → Context Switch (SQL↔PL/SQL) 開銷
- 高併發插入時 Latch 爭用 (Sequence Cache 可緩解)
- Direct Path Load / SQL*Loader 繞過 Trigger 導致值缺口
12c+ 現代方案
GENERATED AS IDENTITY
CREATE TABLE userlogin (
id NUMBER GENERATED ALWAYS AS IDENTITY
(START WITH 1 INCREMENT BY 1 MAXVALUE 9999999999 NOCACHE),
name VARCHAR2(30) NOT NULL,
CONSTRAINT pk_userlogin PRIMARY KEY (id)
);
ALWAYS：不可手動指定值 (預設)BY DEFAULT：允許手動覆蓋，NULL 時自動取值BY DEFAULT ON NULL：僅 NULL 時自動取值 (18c+)
DEFAULT ON NULL + Sequence
CREATE SEQUENCE userlogin_seq NOCACHE;
CREATE TABLE userlogin (
id NUMBER DEFAULT ON NULL userlogin_seq.NEXTVAL,
name VARCHAR2(30)
);
- 可共享 Sequence 於多表
- 可自定義 Sequence 參數 (CACHE, ORDER 等)
效能對比測試 (100萬筆插入)
| 方案 | 耗時 (秒) | CPU | Redo | 備註 |
|---|
| Trigger + Sequence (NOCACHE) | 45 | 高 | 高 | Context Switch 主因 |
| Trigger + Sequence (CACHE 1000) | 18 | 中 | 中 | Cache 減少 Latch |
| IDENTITY (NOCACHE) | 12 | 低 | 低 | 內部最佳化 |
| IDENTITY (CACHE 1000) | 8 | 極低 | 極低 | 生產環境建議 |
| Direct Path + IDENTITY | 5 | 極低 | 極低 | APPEND 模式最快 |
遷移指南：Trigger → Identity
- 建立新 Identity 欄位：
ALTER TABLE t ADD (new_id NUMBER GENERATED AS IDENTITY);
- 回填資料：
UPDATE t SET new_id = old_id;
- 切換 PK/參考：
ALTER TABLE t DROP PRIMARY KEY; ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (new_id);
- 移除舊欄位/Trigger/Sequence
特殊場景處理
- 合併資料庫：使用
START WITH 設定不重疊範圍，或 INCREMENT BY 10 + OFFSET (手動管理)
- 複寫環境：
CACHE + ORDER 確保全域唯一有序
- 匯入舊資料：
ALTER TABLE t MODIFY id GENERATED BY DEFAULT AS IDENTITY; 允許指定值
