萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 自增欄位實作全攻略:Sequence + Trigger 模式、Identity Column (12c+)、效能比較

Oracle 無原生 Auto Increment (12c 前),兩大主流實作

方案一:Sequence + BEFORE INSERT Trigger (經典、通用)

-- 1. 建表
CREATE TABLE userlogin (
  id   NUMBER(6) PRIMARY KEY,
  name VARCHAR2(30) NOT NULL
);

-- 2. 建 Sequence
CREATE SEQUENCE userlogin_seq
  INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 9999999999999 NOCACHE ORDER;

-- 3. 建 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;
/

-- 4. 測試
INSERT INTO userlogin (name) VALUES ('Alice');
INSERT INTO userlogin (name) VALUES ('Bob');
SELECT * FROM userlogin;
-- ID 自動產生 1, 2...

方案二:Identity Column (Oracle 12c Release 1+ 原生支援)

CREATE TABLE userlogin (
  id   NUMBER(6) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR2(30) NOT NULL
);
-- 或允許手動指定 ID
CREATE TABLE userlogin (
  id   NUMBER(6) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(30) NOT NULL
);
  • `ALWAYS`:強制由資料庫產生,手動指定會報錯
  • `BY DEFAULT`:手動給值則用手動值,否則自動產生 (相容舊應用)
  • 內建 Sequence:`USER_TAB_IDENTITY_COLS` 可查詢對應 Sequence 名稱

Sequence 進階選項詳解

效能比較 (100 萬筆 INSERT 測試)

選項說明建議
INCREMENT BY n每次遞增值通常 1;RAC 可設大於節點數避免衝突
START WITH n起始值1 或遷移資料後的最大值+1
MINVALUE / MAXVALUE循環邊界NOMAXVALUE 避免溢位
CYCLE / NOCYCLE到達極值是否循環NOCYCLE (主鍵不可重複)
CACHE n / NOCACHE記憶體預取數量高併發建議 CACHE 20~100;NOCACHE 保證無間隙但效能差
ORDER / NOORDERRAC 環境下是否保證全序ORDER 保證全序 (較慢);NOORDER 各節點各取各的 (高效)

Identity 省去 Trigger 呼叫開銷,效能約提升 10-15%。

遷移建議:舊表改用 Identity

-- 1. 找出現有 Sequence
SELECT sequence_name FROM user_sequences WHERE sequence_name LIKE '%_SEQ';

-- 2. 刪除 Trigger、Sequence
DROP TRIGGER userlogin_bir;
DROP SEQUENCE userlogin_seq;

-- 3. 修改欄位為 Identity (需 12c+)
ALTER TABLE userlogin MODIFY (id GENERATED ALWAYS AS IDENTITY);

常見坑點

  • 忘記 `NOCACHE` 導致重啟後 Sequence 跳號:主鍵允許間隙通常可接受,若業務要求連號請用 `NOCACHE`。
  • Trigger 中用 `SELECT seq.NEXTVAL INTO :NEW.id FROM DUAL` 而非直接 `:NEW.id := seq.NEXTVAL`:11g+ 支援直接賦值,效能略優。
  • 手動 INSERT 指定 ID 導致 Sequence 落後:定期 `SELECT seq.NEXTVAL FROM DUAL` 推進或 `ALTER SEQUENCE ... RESTART START WITH ...` 重設。

結語

**12c+ 直接用 Identity Column**,語法標準、效能最佳、零維護。**11g 及以下**乖乖用 `Sequence + BEFORE INSERT Trigger`,注意 `CACHE/NOCACHE`、`ORDER/NOORDER` 選項對 RAC/高併發的影響。統一團隊規範:所有主鍵表**必須**有自增機制,禁止手工算 MAX(id)+1。

copyright © 萬盛學電腦網 all rights reserved
方案耗時Redo 量間隙風險適用版本
Sequence + Trigger1.0x (基準)1.0xCACHE 失效時有間隙所有版本
Identity (ALWAYS)0.85x0.9x同 Sequence12c+
Identity (BY DEFAULT)0.87x0.9x同 Sequence12c+