Oracle資料庫表設計優化實戰：索引、表空間與反向鍵的最佳實踐

絕大多數 Oracle 資料庫效能瓶頸，根源其實不在記憶體參數調校、I/O 爭用或 CPU 資源搶佔，而是出在資料庫邏輯與物理設計的不合理上。除非面對已無法變更的既有系統，否則 DBA 與開發人員應優先檢視資料表結構、索引策略與儲存規劃，這才是掌控程式效能的關鍵。

合理資料庫設計的三大核心維度

1. 業務資料如何表達：例如員工擁有多個 Email，可採用多欄位（email_1、email_2…）、子表（T_EMAIL）或單一欄位逗號分隔等方式，各有優劣。
2. 資料如何實體儲存：大型表分區、表空間規劃、資料檔案分散於不同實體磁碟等，直接影響 I/O 效能。
3. 如何建立高效索引：Oracle 提供 B-Tree、反向鍵、壓縮、位圖、函式式、分區等多種索引類型，選型不當反而拖累效能。

本文以一個訂單主從表（T_ORDER、T_ORDER_ITEM）為例，透過 PowerDesigner 10 示範從邏輯模型到優化後的 DDL 腳本，逐步修正設計缺失。

初版設計存在的四大問題

1. 表與索引混放同一表空間
導致資料與索引 I/O 互搶，且維護時無法獨立操作（如僅匯出表資料、重建索引）。

2. 序列產生的主鍵未使用反向鍵索引
主鍵值嚴格遞增，易造成 B-Tree 索引最右邊葉塊熱點爭用（Right-hand Index Contention），改用 Reverse Key Index 可將鍵值位元組反轉，分散插入熱點。

3. 子表外鍵索引未啟用壓縮
T_ORDER_ITEM 含大量重複 ORDER_ID，建立 COMPRESS 1 的 B-Tree 索引可大幅縮減儲存空間並提升範圍掃描效能。

4. 複合索引欄位順序錯誤，導致部分查詢無法使用索引
需求查詢條件為 (CLIENT, ORDER_DATE, IS_SHIPPED) 與 (ORDER_DATE, IS_SHIPPED)，但索引定義為 (CLIENT, ORDER_DATE, IS_SHIPPED)。第二種查詢缺少前導欄 CLIENT，Oracle 無法使用該索引（除非使用 Index Skip Scan，但成本極高）。

優化後的 DDL 關鍵點

-- 建立獨立表空間
CREATE TABLESPACE APP_DATA DATAFILE '/u01/app_data01.dbf' SIZE 1G AUTOEXTEND ON;
CREATE TABLESPACE APP_IDX  DATAFILE '/u02/app_idx01.dbf'  SIZE 1G AUTOEXTEND ON;

-- 主表：主鍵改用 Reverse Key Index，複合索引調整欄位順序
CREATE TABLE T_ORDER (
  ORDER_ID NUMBER(10) NOT NULL,
  ADDRESS  VARCHAR2(100),
  CLIENT   VARCHAR2(60),
  ORDER_DATE DATE,          -- 建議用 DATE 型別，避免隱性轉換
  IS_SHIPPED CHAR(1),
  CONSTRAINT PK_T_ORDER PRIMARY KEY (ORDER_ID)
    USING INDEX TABLESPACE APP_IDX REVERSE
) TABLESPACE APP_DATA;

-- 針對 (ORDER_DATE, IS_SHIPPED) 建立索引，滿足第二類查詢
CREATE INDEX IDX_ORDER_DATE_SHIP ON T_ORDER (ORDER_DATE, IS_SHIPPED) TABLESPACE APP_IDX;
-- 針對 (CLIENT, ORDER_DATE, IS_SHIPPED) 建立索引，滿足第一類查詢
CREATE INDEX IDX_ORDER_CLIENT_DATE_SHIP ON T_ORDER (CLIENT, ORDER_DATE, IS_SHIPPED) TABLESPACE APP_IDX;

-- 子表：外鍵索引啟用壓縮
CREATE TABLE T_ORDER_ITEM (
  ITEM_ID  NUMBER(10) NOT NULL,
  ORDER_ID NUMBER(10),
  ITEM     VARCHAR2(20),
  CNT      NUMBER(10),
  CONSTRAINT PK_T_ORDER_ITEM PRIMARY KEY (ITEM_ID)
    USING INDEX TABLESPACE APP_IDX REVERSE
) TABLESPACE APP_DATA;

CREATE INDEX IDX_ORDER_ITEM_ORDER_ID ON T_ORDER_ITEM (ORDER_ID)
  TABLESPACE APP_IDX COMPRESS 1;

ALTER TABLE T_ORDER_ITEM ADD CONSTRAINT FK_T_ORDER_ITEM_ORDER
  FOREIGN KEY (ORDER_ID) REFERENCES T_ORDER (ORDER_ID);

PowerDesigner 實作技巧

• 在 Table Properties → Storage 分別指定 Data Tablespace 與 Index Tablespace。
• 索引屬性中勾選 Reverse 或設定 Compress = 1。
• 使用 Preview SQL 檢視產出的 DDL，確認 TABLESPACE 子句正確。

合理的庫表設計是效能調優的基石，善用工具輔助建模，能讓優化動作落實到每一行 DDL 中。