Oracle 數據庫性能優化訣竅：分區、索引重建、段碎片整理

前言

Oracle 以高可靠性、安全性、兼容性獲企業青睞。本文從分區、索引維護、段碎片整理三大維度，分享保持 Oracle 永久優良性能的實戰訣竅。

一、分區：大表管理的利器

核心價值

• 少量大表佔用絕大多數空間
• 動態將記錄分離到不同表空間，**物理分割**便於維護、備份、恢復、事務隔離、查詢裁剪
• 邏輯上建立視圖連接所有分區，應用層無感

1. 範圍分區表範例

CREATE TABLE Employee (
    EmpNo   VARCHAR2(10) PRIMARY KEY,
    Name    VARCHAR2(30),
    DeptNo  NUMBER(2)
)
PARTITION BY RANGE (DeptNo) (
    PARTITION PART1 VALUES LESS THAN (11) TABLESPACE PART1_TS,
    PARTITION PART2 VALUES LESS THAN (21) TABLESPACE PART2_TS,
    PARTITION PART3 VALUES LESS THAN (31) TABLESPACE PART3_TS,
    PARTITION PART4 VALUES LESS THAN (MAXVALUE) TABLESPACE PART4_TS
);

按 DeptNo 範圍分區，每區獨立表空間。

2. 分區索引：局部 vs 全局

局部索引（推薦多數場景）： 索引分區與表分區一一對應，易管理。

CREATE INDEX Employee_DeptNo ON Employee(DeptNo)
LOCAL (
    PARTITION PART1 TABLESPACE PART1_NDX_TS,
    PARTITION PART2 TABLESPACE PART2_NDX_TS,
    PARTITION PART3 TABLESPACE PART3_NDX_TS,
    PARTITION PART4 TABLESPACE PART4_NDX_TS
);

全局索引： 跨分區強制唯一性時使用，範圍可不同於表分區。

CREATE INDEX Employee_DeptNo ON Employee(DeptNo)
GLOBAL PARTITION BY RANGE (DeptNo) (
    PARTITION PART1 VALUES LESS THAN (11) TABLESPACE PART1_NDX_TS,
    ...
);

3. 分區維護操作

ALTER TABLE 支持：ADD、DROP、EXCHANGE、MOVE、MODIFY、RENAME、SPLIT、TRUNCATE 分區。

二、索引重建：回收碎片空間

問題本質

頻繁 DELETE/INSERT 導致索引結構中「已刪除條目空間無法重用」，索引段持續膨脹，**即使表行數不變，索引大小卻持續增長**。

解決方案

ALTER INDEX index_name REBUILD;

• 建立定期批處理作業，針對高活躍度表重建索引
• 在業務閒置窗口運行，避免與用戶衝突
• 堅持執行 → 及時回收無效空間 → 提升空間利用率與掃描性能

現代補充（Oracle 10g+）

• ALTER INDEX ... REBUILD ONLINE：無鎖重建，業務零中斷
• DBMS_STATS.GATHER_INDEX_STATS 同步更新統計信息
• 監控 INDEX_STATS.HEIGHT、LF_BLKS、DEL_LF_ROWS 判斷重建時機

三、段碎片整理：追求單一擴展

碎片形成機制

• 段由多個**擴展**組成，擴展由連續 Oracle 塊組成
• 現有擴展滿時申請新擴展，**不要求新舊擴展相鄰**
• 反覆擴展 → 段在表空間中分散非連續 → **碎片化**

危害

• 全表掃描/快速全索引掃描需跨越離散擴展，I/O 效率降低
• 表空間管理複雜化，易發生「無法分配擴展」錯誤（即使總空閒足夠）

最佳狀態：單一擴展

• 預估表最終大小，創建時指定 INITIAL/NEXT/MINEXTENTS 使單擴展滿足
• 或使用 **LMT (Locally Managed Tablespace) + AUTOALLOCATE**，Oracle 自動管理擴展大小，減少碎片
• 現有碎片段：ALTER TABLE ... MOVE TABLESPACE ... 重組到新表空間（需鎖表），或 DBMS_REDEFINITION 職線重組（11g+）

總結

1. 分區解決大表管理與查詢裁剪
2. 索引重建解決索引膨脹與碎片
3. 段整理解決存儲離散與空間管理

三者結合，構建 Oracle 性能穩態的基石。**預防勝於治療：設計階段規劃分區策略、預分配空間、建立定期維護作業**，遠勝事後補救。