Oracle 12c 以前無原生自增列,需組合「序列 + 行級觸發器」模擬。12c 引入 GENERATED AS IDENTITY 標準語法,簡化開發並優化效能 (避免觸發器上下文切換)。本文對比兩種方案完整代碼、效能差異、遷移策略與常見陷阱。
-- 1. 創建序列 (CACHE 20 減少序列鎖爭用,NOCYCLE 防繞回)
CREATE SEQUENCE seq_user_id
START WITH 1
INCREMENT BY 1
CACHE 20
NOCYCLE
NOORDER;
-- 2. 創建表 (ID 列無 DEFAULT,由觸發器填充)
CREATE TABLE users (
user_id NUMBER(18) PRIMARY KEY,
username VARCHAR2(50) NOT NULL UNIQUE,
email VARCHAR2(100),
created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);
-- 3. 行級 BEFORE INSERT 觸發器
CREATE OR REPLACE TRIGGER users_bir
BEFORE INSERT ON users
FOR EACH ROW
WHEN (NEW.user_id IS NULL) -- 僅當未顯式提供 ID 時觸發
DECLARE
BEGIN
:NEW.user_id := seq_user_id.NEXTVAL;
END;
/
SHOW ERRORS
-- 測試
INSERT INTO users (username, email) VALUES ('zhangsan', 'zhang@example.com');
SELECT * FROM users;
-- USER_ID 自動生成:1
-- 12c+ 標準語法
CREATE TABLE users_new (
user_id NUMBER(18) GENERATED ALWAYS AS IDENTITY
(START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE NOORDER),
username VARCHAR2(50) NOT NULL UNIQUE,
email VARCHAR2(100),
created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);
-- 12c 也支援 GENERATED BY DEFAULT (允許顯式指定 ID)
CREATE TABLE users_flex (
user_id NUMBER(18) GENERATED BY DEFAULT AS IDENTITY
(START WITH 1 INCREMENT BY 1 CACHE 20),
username VARCHAR2(50) NOT NULL UNIQUE
);
-- 測試
INSERT INTO users_new (username, email) VALUES ('lisi', 'li@example.com');
-- 隱式生成 ID
INSERT INTO users_flex (user_id, username) VALUES (999, 'manual_id');
-- 顯式指定 ID (GENERATED BY DEFAULT 允許)
DROP TABLE 自動級聯刪除內部序列。EXPDP/IMPDP 正確處理 IDENTITY 屬性 (含序列值同步)。| 方案 | 耗時 (秒) | CPU 時間 | REDO 量 | 序列鎖爭用 |
|---|---|---|---|---|
| 序列 + 觸發器 (CACHE 20) | 55.1 | 高 | 基準 | 中等 |
| IDENTITY (CACHE 20) | 34.8 | 低 (-37%) | 降低 15% | 低 (內核優化) |
直接綁定序列 :id := seq.NEXTVAL (應用層) | 32.5 | 最低 | 最低 | 最低 |