Oracle 數據庫設計規範：六大核心字段模式最佳實踐 (WHO、狀態、邏輯刪除、自增、彈性、拆分)

前言：字段設計決定系統壽命

表結構是系統演進的骨架。糟糕的字段設計導致：審計無據、業務邏輯硬編碼、DDL 變更頻發引發鎖爭用、查詢性能退化。本文提煉六大通用字段模式，覆蓋審計、生命週期、軟刪除、主鍵策略、擴展性、大表優化，助力構建可演進的企業級數據模型。

一、 WHO 審計字段：每行數據的「身份證」

實施要點

• 應用用戶 ≠ 數據庫用戶： 現代系統多用連接池 (單 DB 用戶)，審計 ID 必須來自應用會話 (如 Spring Security `Principal`、JWT `sub` claim)。
• 觸發器兜底： 防止繞過應用層直連 DB 修改數據。

  CREATE OR REPLACE TRIGGER trg_table_who
  BEFORE INSERT OR UPDATE ON my_table
  FOR EACH ROW
  BEGIN
    IF INSERTING THEN
      :NEW.creation_date := SYSTIMESTAMP;
      :NEW.created_by := NVL(SYS_CONTEXT('APP_CTX','USER_ID'), -1); -- 從上下文獲取
    ELSIF UPDATING THEN
      :NEW.last_update_date := SYSTIMESTAMP;
      :NEW.last_updated_by := NVL(SYS_CONTEXT('APP_CTX','USER_ID'), -1);
    END IF;
  END;

• 時區統一： 強制使用 `TIMESTAMP WITH TIME ZONE` 或統一存儲 UTC (`SYSTIMESTAMP AT TIME ZONE 'UTC'`)，避免跨地域顯示錯誤。

二、 狀態與有效期字段：業務生命週期的「開關」

查詢最佳實踐：視圖封裝有效性邏輯

CREATE OR REPLACE VIEW v_active_articles AS
SELECT * FROM articles
WHERE status = 1
  AND (start_date IS NULL OR start_date <= SYSDATE)
  AND (end_date   IS NULL OR end_date   >= SYSDATE);
-- 所有業務查詢走視圖，保證一致性，避免 WHERE 子句漏寫

三、 邏輯刪除：數據永不真正消失

關鍵區別：邏輯刪除 ≠ 狀態失效

• 狀態失效 (STATUS=0)： 業務暫時下架，**可逆**，管理後台可一鍵恢復 (STATUS=1)，數據對用戶可見性由業務邏輯控制。
• 邏輯刪除 (DELETED=1)： 業務意義上「不存在」，**不可逆** (或極嚴格審批恢復)，**所有常規查詢物理隔離**，僅審計/合規/數據恢復場景可見。

唯一約束陷阱與解決

邏輯刪除後，業務鍵 (如 `USER_NAME`, `EMAIL`) 可能需重用。唯一索引會阻擋。

-- 方案：部分索引 (11g+ 不直接支援，變通)
-- 1. 組合唯一索引包含 DELETED
CREATE UNIQUE INDEX uk_user_email ON users (email, deleted);
-- 正常數據 (DELETED=0) 唯一；刪除數據 (DELETED=1) 允許重複 email
-- 注意：NULL 值在唯一索引中不參與唯一性檢查，若 DELETED 可為 NULL 需調整

-- 2. 虛擬列 + 唯一索引 (12c+ 推薦)
ALTER TABLE users ADD (email_active VARCHAR2(255) GENERATED ALWAYS AS 
  (CASE WHEN deleted=0 THEN email END) VIRTUAL);
CREATE UNIQUE INDEX uk_user_email_active ON users (email_active);
-- 僅正常數據的 email 參與唯一性

四、 自增主鍵：Sequence 的正統地位

Oracle 無 MySQL `AUTO_INCREMENT` / SQL Server `IDENTITY` (12c 前)，**Sequence 是唯一標準方案**。

現代最佳實踐 (12c+)

-- 1. 原生 Identity (首選)
CREATE TABLE orders (
  order_id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 CACHE 1000 ORDER),
  ...
);

-- 2. 11g/12c 兼容：Sequence + 默認值 (無觸發器開銷)
CREATE SEQUENCE order_seq CACHE 1000 ORDER;
CREATE TABLE orders (
  order_id NUMBER DEFAULT order_seq.NEXTVAL PRIMARY KEY,
  ...
);
-- 12c+ DEFAULT ON NULL 允許顯式插入 NULL 觸發序列

為什麼 Sequence 優於「最大值+1」/「單表序列表」？

• 高併發無鎖： Sequence 緩存 (CACHE) 在 SGA 中，`NEXTVAL` 只需內存取數，**無行鎖、無索引爭用**。
• 跨表共享： 訂單主表 + 明細表共用一個 Sequence，ID 全局唯一，便於分庫分表、數據整合。
• 間隙可接受： 實例重啟丟失緩存值導致間隙，**不影響唯一性**，業務勿依賴連續性。

五、 彈性字段：預留演進空間，避免 DDL 風險

大型系統上線後，**加列 (DDL) 風險極高**：排他鎖阻塞寫入、導致視圖/包/觸發器失效需重編譯、統計信息丟失。

預留模式

ALTER TABLE core_business ADD (
  attr_number_01 NUMBER, attr_number_02 NUMBER, ..., attr_number_10 NUMBER,
  attr_string_01 VARCHAR2(4000), ..., attr_string_10 VARCHAR2(4000),
  attr_date_01   TIMESTAMP, ..., attr_date_05 TIMESTAMP,
  attr_clob_01   CLOB, attr_clob_02 CLOB
);

啟用流程 (無 DDL 風險)

1. 應用層映射：`attr_string_01` ↔ `customer_loyalty_tier`
2. 視圖別名：`CREATE VIEW v_customer AS SELECT ..., attr_string_01 AS loyalty_tier FROM core_business;`
3. 索引按需：`CREATE INDEX idx_loyalty ON core_business (attr_string_01) WHERE attr_string_01 IS NOT NULL;` (部分索引/函數索引)

存儲真相：NULL 不佔空間

Oracle 行存儲格式：**尾部連續 NULL 列零字節存儲**；中間 NULL 僅佔 1 字節長度標誌。大量預留 NULL 列**幾乎零存儲成本**。

六、 垂直拆分：大表性能的「減肥手術」

適用場景

• 寬表 (> 100 列)，但核心高頻列 < 20 列 (ID、狀態、時間、關鍵外鍵)。
• 低頻大字段 (CLOB/BLOB/長 VARCHAR2) 導致行長度超大，Full Table Scan I/O 放大。
• 不同列訪問頻率/權限/生命週期差異巨大。

拆分模式：主表 + 擴展表 (1:1)

-- 主表：高頻、小長度、核心索引
CREATE TABLE users_core (
  user_id      NUMBER PRIMARY KEY,
  login_name   VARCHAR2(64) UNIQUE,
  email        VARCHAR2(128),
  status       NUMBER(1),
  created_date TIMESTAMP,
  -- 10-15 核心列
);

-- 擴展表：低頻、大字段、無索引或少索引
CREATE TABLE users_ext (
  user_id      NUMBER PRIMARY KEY, -- 同主鍵，外鍵關聯核心表
  profile_json CLOB,
  avatar_blob  BLOB,
  preferences  VARCHAR2(4000),
  -- 50+ 低頻列
);

-- 查詢聯合 (主鍵關聯 = 高效 Nested Loops)
SELECT c.login_name, e.profile_json
FROM users_core c JOIN users_ext e ON c.user_id = e.user_id
WHERE c.login_name = 'john';

拆分收益量化