Oracle 數據類型、約束、索引、叢集、哈希叢集深度解析與實戰模板

Oracle 數據類型、約束、索引、叢集、哈希叢集深度解析與實戰模板

本文系統整理 Oracle 核心邏輯結構的定義語法、選型原理、存儲細節與最佳實務，適合 DBA 設計表結構、開發人員編寫 DDL、架構師做容量規劃時查閱。

一、內建數據類型全景

字串類型

關鍵：NLS_LENGTH_SEMANTICS 預設 BYTE，**DDL 中顯式寫 CHAR**；VARCHAR 別用，**永遠用 VARCHAR2**；空字串 '' 在 VARCHAR2/NVARCHAR2 中等同 NULL。

數值類型

日期時間類型

二進制/LOB/ROWID

• RAW(n) / LONG RAW：2000/2GB 二進制，不做字元集轉換。
• BLOB：二進制大對象 (圖片、聲音、視頻)。
• BFILE：外部檔案指標，唯讀，最大 4GB。
• ROWID：物理行地址 (堆表)；UROWID：通用行地址 (IOT、外部表)。

二、約束完整模板

CREATE TABLE demo (
  id           NUMBER(10) PRIMARY KEY,
  code         VARCHAR2(20 CHAR) NOT NULL UNIQUE,
  name         VARCHAR2(100 CHAR) NOT NULL,
  status       CHAR(1 BYTE) DEFAULT 'A' CHECK (status IN ('A','I','D')),
  dept_id      NUMBER(10),
  created_by   VARCHAR2(30) DEFAULT USER,
  created_dt   DATE DEFAULT SYSDATE,
  CONSTRAINT fk_demo_dept FOREIGN KEY (dept_id) REFERENCES dept(id) ON DELETE SET NULL,
  CONSTRAINT ck_name_len CHECK (LENGTH(name) >= 2)
) TABLESPACE users
PCTFREE 10 PCTUSED 80
STORAGE (INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED);

約束狀態控制速查

ALTER TABLE demo ENABLE VALIDATE CONSTRAINT fk_demo_dept;   -- 驗證現有+未來
ALTER TABLE demo ENABLE NOVALIDATE CONSTRAINT fk_demo_dept; -- 僅未來
ALTER TABLE demo DISABLE CONSTRAINT fk_demo_dept;           -- 停用
ALTER TABLE demo DROP CONSTRAINT fk_demo_dept CASCADE;      -- 刪除含級聯 FK

三、索引策略與語法

四、叢集表

索引叢集 (關聯表共享塊)

CREATE CLUSTER hr_cluster (dept_id NUMBER)
PCTUSED 80 PCTFREE 5 SIZE 500 TABLESPACE users
STORAGE (INITIAL 200K NEXT 300K MINEXTENTS 2 MAXEXTENTS UNLIMITED PCTINCREASE 33);

CREATE TABLE dept_info (dept_id NUMBER PRIMARY KEY, dept_name VARCHAR2(100))
CLUSTER hr_cluster(dept_id);

CREATE TABLE emp_info (emp_id NUMBER PRIMARY KEY, emp_name VARCHAR2(50), dept_id NUMBER)
CLUSTER hr_cluster(dept_id);

-- 必須建叢集索引
CREATE INDEX idx_hr_cluster ON CLUSTER hr_cluster TABLESPACE users;

管理叢集

ALTER CLUSTER hr_cluster PCTUSED 60 PCTFREE 30;  -- 僅影響新塊
DROP CLUSTER hr_cluster INCLUDING TABLES CASCADE CONSTRAINTS;  -- 連帶刪表、FK

五、哈希叢集

CREATE CLUSTER hash_cluster (id NUMBER) SIZE 512 SINGLE TABLE HASHKEYS 500;
-- HASH IS id  -- 可指定哈希函數列，預設內部哈希
CREATE TABLE area (area_id NUMBER PRIMARY KEY, area_name VARCHAR2(200))
CLUSTER hash_cluster(area_id);

限制：不可 ALTER SIZE/HASHKEYS/HASH IS；不需/不可建叢集索引；單表哈希叢集適合全表掃描少、等值查詢多、鍵值唯一的場景。

六、存儲參數詳解

LMT (Locally Managed Tablespace) + ASSM (Auto Segment Space Management) 已自動管理 PCTFREE/PCTUSED/EXTENTS，**建議拋棄手動存儲子句，改用預設**。

總結

Oracle 邏輯結構設計的核心決策樹：

1. 字串 → VARCHAR2(n CHAR) (AL32UTF8) / 固定代碼 → CHAR / 跨平台字元語義 → NVARCHAR2。
2. 數值 → NUMBER(p,s) 精確 / 科學計算 → BINARY_DOUBLE。
3. 日期 → TIMESTAMP(n) 需小數秒 / DATE 兼容舊碼。
4. 約束 → PK/FK/UK/CK/NOT NULL 五件套缺一不可，DDL 時建立，狀態用 ENABLE VALIDATE。
5. 索引 → 高選擇性 B*Tree / 低基數位圖 / 函數索引匹配 WHERE 條件 / 反向索引解熱塊。
6. 叢集 → 父子表頻繁 JOIN 且存取模式固定 → 索引叢集；單表等值鍵唯一高頻 → 哈希叢集。
7. 存儲 → 使用 LMT+ASSM，拋棄手動參數，專注 PCTFREE/INITRANS 微調。

將上述模板納入團隊 DDL 規範，配合版本控管 (Flyway/Liquibase) 與 Code Review，即可實現結構變更的標準化、可追溯、可回滾。