Oracle B-Tree 索引結構與區塊大小效能關係：32K 區塊減半 Consistent Gets 實測

Oracle B-Tree 索引結構受區塊大小直接影響：更大區塊容納更多索引鍵值，降低樹高、減少一致性讀取。本文解析索引內部結構、區塊大小對樹高的影響、實測 8K→16K 區塊將 Consistent Gets 減半的完整過程，並探討索引重建爭議與 10g 自動維護任務（AMT）。

B-Tree 索引內部結構

• 分支區塊：儲存 (索引鍵值, 子區塊指標)，導航至下一層。
• 葉區塊：儲存 (索引鍵值, ROWID)，指向表實際資料列。
• 每個索引區塊 = 樹中的一個節點；區塊滿時分裂，產生新層級。

區塊大小對樹高與效能的影響

公式：每區塊可容納索引項目數 ≈ (區塊大小 - 區塊頭) / (鍵值長度 + ROWID 長度 + 開銷)

• 8K 區塊 → 約 200-300 項/區塊（視鍵值長度）
• 16K 區塊 → 約 400-600 項/區塊
• 32K 區塊 → 約 800-1200 項/區塊

項目數倍增 → 樹高降低 1-2 層 → 索引範圍掃描所需 Consistent Gets 大幅下降。

實測：8K → 16K 區塊重建索引效能對比

環境

• Oracle 9i，預設 8K 區塊，額外建立 16K 表空間 INDX_16K、16K 快取。
• 測試表 ERADMIN.ADMISSION，索引 ADMISSION_PATIENT_ID（非唯一）。
• 查詢：SELECT COUNT(*) FROM eradmin.admission WHERE patient_id BETWEEN 1 AND 40000;

8K 索引統計

Statistics
----------------------------------------------------------
  421 consistent gets
  0 physical reads

重建至 16K 表空間後

ALTER INDEX eradmin.admission_patient_id REBUILD NOLOGGING NOREVERSE TABLESPACE indx_16k;

16K 索引統計

Statistics
----------------------------------------------------------
  211 consistent gets   -- 減半！
  0 physical reads

邏輯讀減半直接轉化為 CPU、閂鎖爭用降低，高併發下效能增益顯著。

多區塊大小架構部署建議

啟用方式：ALTER SYSTEM SET DB_16K_CACHE_SIZE=200M SCOPE=BOTH; 需重啟實例生效（9i/10g），11g+ 可動態調整。

索引重建爭議：定期重建 vs. 自動維護

• 支持派：實測重建後索引高度降低、Clustering Factor 改善、查詢加速。
• 反對派（Oracle 內部專家）：B-Tree 空間重用極佳，極少需重建；邏輯讀減少需可量化，否則為安慰劑。
• 10g AMT 自動維護任務：基於 AWR 分析，自動偵測並重建「潛在最佳化索引」，結束人工爭論。

實務判斷索引是否需重建

-- 檢查索引高度
SELECT index_name, blevel, leaf_blocks, distinct_keys, clustering_factor
FROM   user_indexes
WHERE  table_name = 'ADMISSION';

-- BLEVEL >= 3 且 CLUSTERING_FACTOR 接近 NUM_ROWS 時，考慮重建/分區
-- 結合 AWR 報告 "Segments by Logical Reads" 找出高邏輯讀索引

結論

1. 大區塊（16K/32K）確實能降低索引樹高、減少 Consistent Gets，適合大型索引、DSS 工作負載。
2. 多區塊大小架構需配合專用快取，管理複雜度上升，建議僅於核心大表/索引採用。
3. 索引重建不宜盲目定期執行，應基於 AWR/ASH 數據、BLEVEL、CLUSTERING_FACTOR 判斷，或交由 10g+ AMT 自動處理。

掌握區塊大小與索引結構的物理關係，能從存儲層面根治索引存取瓶頸，是資深 DBA 進階調校的關鍵技能。