Oracle 資料庫效能長青秘訣：分區、索引重建、段碎片整理與自由空間合併四大支柱

一、分區策略

• 大表分區：依業務鍵 (如 DeptNo) 將資料分散至不同表空間
• 物理分離利於維護、備份、恢復、查詢
• 建立視圖整合分區，邏輯呈現單一表

範例：範圍分區表與本地索引

CREATE TABLE Employee (EmpNo VARCHAR2(10) PK, 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
);

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
);

局部索引與分區一一對應，易管理；需跨分區唯一性時用全域索引。

二、定期重建索引

• 頻繁 DELETE/INSERT 導致索引空洞，空間增大但不釋放
• ALTER INDEX ... REBUILD 回收空間
• 建立批次作業，離峰時段重建高活躍表索引

三、段碎片整理

• 段由多個 Extent 組成，Extent 非連續 → 碎片
• 查詢 DBA_SEGMENTS 找出 Extents ≥ 10 的段
• 解法：(1) CTAS 重建表 (2) EXP/IMP (COMPRESS=Y 修正儲存參數)

EXP/IMP 範例

EXP system/mgr FILE=exp.dmp COMPRESS=Y GRANTS=Y INDEXES=Y TABLES=(T1,T2)
-- 刪除舊表
IMP system/mgr FILE=exp.dmp COMMIT=Y BUFFER=64000 FULL=Y

四、自由 Extent 碎片合併

• 相鄰自由 Extent 不自動合併（除非 PCTINCREASE ≠ 0，SMON 定期合併）
• PCTINCREASE=0 時，手動 ALTER TABLESPACE ... COALESCE 強制合併
• 未合併導致大範圍請求失敗，空間分配惡化
• 理想狀態：每物件單一 Extent，所有自由空間聚合為一大連續 Extent