Oracle 性能優化四大支柱：分區表、索引重建、段碎片整理與自由區塊合併

一、分區表與分區索引

範圍分區表示例

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

優勢：數據物理分離，備份/恢復/查詢可針對單分區，分區剪枝 大幅減少掃描量。

局部索引

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

局部索引與表分區一一對應，維護簡單（ALTER INDEX ... REBUILD PARTITION），推薦優先使用。全局索引僅在需跨分區唯一性約束時使用。

分區管理

ALTER TABLE 支援：ADD、DROP、EXCHANGE、MOVE、MERGE、SPLIT、TRUNCATE 分區。

二、定期重建索引回收空間

頻繁 DML 導致索引內部碎片，空間不自動回收。批處理作業：

ALTER INDEX idx_name REBUILD [ONLINE] [TABLESPACE idx_ts] [PARALLEL n];

• ONLINE：DML 期間重建，生產環境首選。
• 排程在業務低峰執行，避免鎖衝突。

三、段碎片整理

段由多個不連續區塊組成，區塊過多（>10）影響全表掃描與空間分配。檢測：

SELECT owner, segment_name, segment_type, extents
FROM dba_segments
WHERE extents > 10
ORDER BY extents DESC;

解決兩法：

1. CREATE TABLE new_t AS SELECT * FROM old_t → DROP old_t → RENAME new_t TO old_t（需重建索引/權限/同義詞）。
2. EXP/IMP 配合 COMPRESS=Y 重組存儲參數：

   EXP system/manager FILE=exp.dmp COMPRESS=Y GRANTS=Y INDEXES=Y TABLES=(T1,T2)
   IMP system/manager FILE=exp.dmp COMMIT=Y BUFFER=64000 FULL=Y

四、自由區塊合併

• PCTINCREASE != 0 時，SMON 定期自動合併相鄰自由區塊。
• PCTINCREASE = 0 時，需手動：ALTER TABLESPACE ts_name COALESCE;
• 理想狀態：每個對象單區塊，所有自由空間聚合為一大區塊，分配高效、無外部碎片。