Oracle Sequence 完全指南：建立、使用、修改、刪除與效能優化

Sequence 核心概念

Oracle Sequence 是獨立於資料表的序號產生器，提供：

• 高並發安全：無序列化鎖爭用
• 跨表共享：單一 Sequence 供多表使用
• 可配置屬性：起始值、遞增值、最大/最小值、循環、快取

建立 Sequence (CREATE SEQUENCE)

完整語法

CREATE SEQUENCE sequence_name
[INCREMENT BY n]          -- 遞增值 (預設 1，負值為遞減)
[START WITH n]            -- 起始值 (預設 1)
[{MAXVALUE n | NOMAXVALUE}]  -- 最大值 (預設 NOMAXVALUE: 10^27)
[{MINVALUE n | NOMINVALUE}]  -- 最小值 (預設 NOMINVALUE: 1)
[{CYCLE | NOCYCLE}]       -- 達極值後是否循環 (預設 NOCYCLE)
[{CACHE n | NOCACHE}]     -- 記憶體預取數量 (預設 CACHE 20)
[ORDER | NOORDER];        -- RAC 環境下是否保證全域有序 (預設 NOORDER)

實戰範例

-- 標準主鍵序號 (最常用)
CREATE SEQUENCE seq_emp_id
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 100;  -- 高併發建議較大快取

-- 訂單號：年份前綴 + 序號 (需搭配觸發器或應用層組裝)
CREATE SEQUENCE seq_order_no
START WITH 1
INCREMENT BY 1
MAXVALUE 999999
CYCLE
NOCACHE;  -- 業務連號不可跳號時使用 NOCACHE

-- 遞減序號 (特殊需求)
CREATE SEQUENCE seq_reverse
START WITH 10000
INCREMENT BY -1
MINVALUE 1
NOCYCLE;

使用 Sequence (NEXTVAL / CURRVAL)

核心規則

常見用法

-- 1. 插入時直接使用
INSERT INTO employees (emp_id, name) VALUES (seq_emp_id.NEXTVAL, 'John');

-- 2. 先取值再多處使用 (同一陳述式多次 NEXTVAL 會產生不同值！)
DECLARE
  v_id NUMBER;
BEGIN
  v_id := seq_emp_id.NEXTVAL;
  INSERT INTO employees (emp_id, name) VALUES (v_id, 'John');
  INSERT INTO emp_audit (emp_id, action) VALUES (v_id, 'CREATE');
END;

-- 3. 查詢當前值 (不遞增)
SELECT seq_emp_id.CURRVAL FROM dual;

-- 4. 查詢 Sequence 定義與狀態
SELECT sequence_name, min_value, max_value, increment_by, 
       cycle_flag, cache_size, last_number
FROM user_sequences
WHERE sequence_name = 'SEQ_EMP_ID';

修改 Sequence (ALTER SEQUENCE)

可修改屬性

• INCREMENT BY
• MAXVALUE / NOMAXVALUE
• MINVALUE / NOMINVALUE
• CYCLE / NOCYCLE
• CACHE / NOCACHE
• ORDER / NOORDER

不可修改：START WITH

需 DROP SEQUENCE 後重建。

範例

-- 調整快取大小應對高併發
ALTER SEQUENCE seq_emp_id CACHE 1000;

-- 設定最大值並啟用循環
ALTER SEQUENCE seq_order_no MAXVALUE 999999 CYCLE;

-- 取消快取 (防跳號需求)
ALTER SEQUENCE seq_critical NOCACHE;

刪除 Sequence

DROP SEQUENCE seq_old;

快取機制深度解析與跳號問題

CACHE 工作原理

1. Instance 啟動時，將 CACHE 指定數量的序號載入 SGA
2. NEXTVAL 直接從記憶體取值，極快
3. 快取用盡時，背景進程批次補充下一組

跳號場景

• Instance Crash / SHUTDOWN ABORT：記憶體快取遺失
• RAC 多實例：各實例獨立快取，數值交錯非連續
• 手動 ALTER SEQUENCE：重設屬性可能導致間隙

防跳號策略

RAC 環境特殊考量

• ORDER 選項：強制跨實例全域有序 (透過實例間協調)，嚴重影響效能
• NOORDER (預設)：各實例獨立快取，數值不保證時間序
• 主鍵用途通常 不需要 ORDER，唯一性才是核心

進階技巧與最佳實踐

1. 命名規範：SEQ_<表名>_<用途>，如 SEQ_EMP_ID
2. 權限控制：GRANT SELECT ON seq_emp_id TO app_user; (僅需 SELECT)
3. 同義詞：CREATE PUBLIC SYNONYM seq_emp_id FOR schema.seq_emp_id;
4. 監控跳號：定期比對 LAST_NUMBER 與實際最大 ID
5. 12c+ Identity Column：id NUMBER GENERATED ALWAYS AS IDENTITY 內建 Sequence，語法更簡潔

常見錯誤與解決

結語：Sequence 是 Oracle 高效能主鍵生成的基石。理解快取機制、跳號本質、RAC 行為差異，才能在效能與業務連號需求間做出正確架構決策。