Oracle 自增列 (Identity Column) 實現演進：從 Sequence+Trigger 到 12c 原生 Identity 的最佳實踐

一、 歷史方案：Sequence + Trigger (11g 及之前通用模式)

1. 三步構建標準流程

-- 步驟 1: 創建序列 (緩存 20 提升性能，ORDER 保證 RAC 環境全局有序)
CREATE SEQUENCE hard_seq
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 21
  INCREMENT BY 1
  CACHE 20
  ORDER;  -- RAC 必須，單機可選

-- 步驟 2: 創建表 (主鍵約束)
CREATE TABLE ly.hard (
  hard_id   NUMBER PRIMARY KEY,
  col1      VARCHAR2(100),
  col2      DATE
);

-- 步驟 3: 創建 BEFORE INSERT 行級觸發器
CREATE OR REPLACE TRIGGER hard_id_trigger
BEFORE INSERT ON ly.hard
FOR EACH ROW
WHEN (NEW.hard_id IS NULL)  -- 允許顯式指定 ID 覆蓋
BEGIN
  SELECT hard_seq.NEXTVAL INTO :NEW.hard_id FROM dual;
END;
/

2. 關鍵細節與避坑

• WHEN 子句： `WHEN (NEW.hard_id IS NULL)` 允許批量導入時顯式指定 ID (如數據遷移)，避免觸發器覆蓋。
• SELECT ... INTO vs 直接賦值： 11g+ 支持 `:NEW.hard_id := hard_seq.NEXTVAL;` (PL/SQL 引擎內部優化)，無需 `SELECT FROM dual` 上下文切換，**推薦直接賦值**。
• OEM 創建觸發器 Bug： 圖形化工具偶發「索引中丟失 IN/OUT 參數」錯誤，**堅持使用腳本部署**。
• 緩存失效風險： 實例崩潰/關閉導致序列緩存值丟失 (間隙)，但**不影響唯一性**，僅不連續。業務勿依賴連續性。

二、 12c+ 原生 Identity Column：語法簡潔、性能更優、遷移無感

1. 標準語法 (符合 ANSI SQL 標準)

-- 新建表
CREATE TABLE ly.hard_new (
  hard_id   NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 21 INCREMENT BY 1 CACHE 20 ORDER),
  col1      VARCHAR2(100),
  col2      DATE,
  CONSTRAINT pk_hard_new PRIMARY KEY (hard_id)
);

-- 現有表添加 Identity 列 (12.1+)
ALTER TABLE ly.hard ADD (hard_id NUMBER GENERATED ALWAYS AS IDENTITY);

-- 允許顯式插入值覆蓋 (12.1+)
CREATE TABLE t (id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY);
-- INSERT INTO t(id) VALUES (100); -- 允許
-- INSERT INTO t(id) VALUES (NULL); -- 自動生成

2. Identity vs Sequence+Trigger 核心優勢對比

3. 內部實現原理：Identity 仍基於 Sequence

• 創建 Identity 列時，系統隱式創建系統序列 (`SYS_ISEQ_$` 開頭)，可通過 `USER_TAB_IDENTITY_COLS` 查看。
• 序列屬性 (CACHE, ORDER, CYCLE) 直接映射 Identity 選項。
• 插入時內核直接調用序列 `NEXTVAL` 賦值，**繞過 PL/SQL 引擎**，故性能更優。

三、 遷移指南：從舊模式平滑遷移至 Identity

-- 1. 確認現有表結構
SELECT table_name, column_name, data_default
FROM user_tab_cols
WHERE data_default LIKE '%NEXTVAL%'; -- 找到靠觸發器賦值的列

-- 2. 禁用/刪除觸發器
ALTER TRIGGER hard_id_trigger DISABLE;
-- DROP TRIGGER hard_id_trigger; -- 確認無問題後刪除

-- 3. 修改列為 Identity (保留現有數據與序列當前值)
-- 12.1 需重建表或使用 DBMS_REDEFINITION 在線重定義
-- 18c+ 支持直接 MODIFY (視版本而定)
-- 最安全通用方案：創建新表 + 數據遷移 + 重命名
CREATE TABLE ly.hard_new AS SELECT * FROM ly.hard WHERE 1=0; -- 只複製結構
-- 手動添加 Identity 定義 (見上文語法)
-- 遷移數據
INSERT INTO ly.hard_new SELECT * FROM ly.hard;
-- 重建索引/約束/權限/同義詞
-- 重命名切換
RENAME ly.hard TO ly.hard_old;
RENAME ly.hard_new TO ly.hard;

-- 4. 可選：保留舊序列作為備用或刪除
-- DROP SEQUENCE hard_seq;

四、 進階場景：分區表、全局臨時表、分片表

• 分區表： 完全支援，Identity 列可作為分區鍵一部分。
• 全局臨時表 (GTT)： 12.1+ 支持 Identity 列，序列值跨會話共享 (全局序列)，會話私有數據插入 ID 連續增長。
• Sharding (分片)： Identity 列需配合 `SHARDING KEY` 使用，序列生成在分片協調器層協調。

五、 運維檢查腳本

-- 檢查所有 Identity 列及其序列屬性
SELECT t.table_name, t.column_name, t.generation_type, t.identity_options
FROM user_tab_identity_cols t
JOIN user_sequences s ON s.sequence_name = t.sequence_name
ORDER BY t.table_name;

-- 監控序列緩存使用情況 (間隙分析)
SELECT sequence_name, cache_size, last_number
FROM user_sequences
WHERE sequence_name LIKE 'ISEQ$$_%' OR sequence_name IN (SELECT sequence_name FROM user_tab_identity_cols);