Oracle 建立分區表與索引語法全攻略

分區表是處理 TB 級資料的標準架構。本文提供包含範圍分區、索引組織表 (IOT)、LOB 儲存、平行度、儲存參數等完整的 CREATE TABLE 與 CREATE INDEX 實戰範本。

範圍分區表完整範本

CREATE TABLE sales_fact (
  sale_id       NUMBER(18) NOT NULL,
  cust_id       NUMBER(10) NOT NULL,
  prod_id       NUMBER(8)  NOT NULL,
  sale_dt       DATE       NOT NULL,
  amount        NUMBER(12,2),
  channel_cd    VARCHAR2(10),
  promotion_id  NUMBER(8),
  CONSTRAINT pk_sales_fact PRIMARY KEY (sale_id, sale_dt)  -- PK 必須含分區鍵
  USING INDEX
    TABLESPACE ts_idx
    STORAGE (INITIAL 64M NEXT 64M PCTINCREASE 0)
)
PCTFREE 10
TABLESPACE ts_data
STORAGE (INITIAL 64M NEXT 64M PCTINCREASE 0)
PARTITION BY RANGE (sale_dt)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))  -- 11g+ 間隔分區，自動擴展
(
  PARTITION p_202301 VALUES LESS THAN (TO_DATE('2023-02-01','YYYY-MM-DD')) TABLESPACE ts_data_2023,
  PARTITION p_202302 VALUES LESS THAN (TO_DATE('2023-03-01','YYYY-MM-DD')) TABLESPACE ts_data_2023,
  PARTITION p_202303 VALUES LESS THAN (TO_DATE('2023-04-01','YYYY-MM-DD')) TABLESPACE ts_data_2023
)
ENABLE ROW MOVEMENT;  -- 允許分區鍵更新導致跨分區移動

關鍵設計解析

• INTERVAL 分區 (11g+)：自動建立新月份分區，免去手工維護腳本。
• PK 含分區鍵：Local Unique 索引強制要求；若業務鍵不含日期，改用 Global Unique Index 或 PARTITION BY REFERENCE。
• ENABLE ROW MOVEMENT：啟用後 UPDATE sale_dt 可自動移動列至正確分區，否則報錯。
• TABLESPACE 規劃：歷史分區放低成本儲存 (HDD/冷資料層)，熱分區放 SSD/記憶體層。

本地分區索引 (Local Partitioned Index)

-- 按照表分區自動對齊
CREATE INDEX idx_sales_cust ON sales_fact(cust_id) LOCAL
  TABLESPACE ts_idx
  STORAGE (INITIAL 32M NEXT 32M PCTINCREASE 0)
  COMPRESS 1;  -- 11g+ 前綴壓縮，節省空間加速掃描

-- 指定個別分區屬性 (如不同壓縮等級)
CREATE INDEX idx_sales_prod ON sales_fact(prod_id) LOCAL
(
  PARTITION p_202301 TABLESPACE ts_idx_2023 COMPRESS 1,
  PARTITION p_202302 TABLESPACE ts_idx_2023 COMPRESS 1,
  PARTITION p_202303 TABLESPACE ts_idx_2023 COMPRESS ADVANCED LOW
);

全域分區索引 (Global Partitioned Index)

-- 用於不含分區鍵的唯一性約束或高頻查詢欄位
CREATE INDEX idx_sales_channel ON sales_fact(channel_cd)
GLOBAL PARTITION BY RANGE (channel_cd)
(
  PARTITION g_web VALUES LESS THAN ('W') TABLESPACE ts_idx,
  PARTITION g_mob VALUES LESS THAN ('M') TABLESPACE ts_idx,
  PARTITION g_oth VALUES LESS THAN (MAXVALUE) TABLESPACE ts_idx
);

維護提醒：表分區 DROP/TRUNCATE 時必須 UPDATE GLOBAL INDEXES。

索引組織表 (IOT) 範本

CREATE TABLE iot_session (
  session_id    RAW(16) PRIMARY KEY,
  user_id       NUMBER(10),
  start_time    TIMESTAMP(3),
  last_access   TIMESTAMP(3),
  payload       BLOB
)
ORGANIZATION INDEX
  PCTTHRESHOLD 20          -- 超過 20% 區塊移至溢出段
  INCLUDING last_access    -- 此欄前包含在索引區塊
  OVERFLOW TABLESPACE ts_lob
  STORAGE (INITIAL 16M NEXT 16M PCTINCREASE 0);

IOT 以 PK 排序儲存，適合「單筆 PK 存取、範圍掃描」的高併發場景 (Session、Queue、Log)。

LOB 儲存最佳化

CREATE TABLE doc_repo (
  doc_id    NUMBER PRIMARY KEY,
  content   CLOB,
  metadata  XMLTYPE
)
LOB (content) STORE AS SECUREFILE (
  TABLESPACE ts_lob
  COMPRESS HIGH
  DEDUPLICATE
  CACHE READS
  ENCRYPT USING 'AES256'
);

11g+ SecureFiles 強制要求 ASSM 表空間，提供壓縮、去重、加密、快取三大企業級特性。