Oracle資料庫最常用索引類型與維護實務指南

前言

在Oracle資料庫管理中，索引是提升查詢效能的關鍵機制。本文針對Oracle 8i版本環境，系統性整理最常見的B-tree索引維護實務，涵蓋系統表檢查、儲存空間分析、選擇性評估等核心操作，協助DBA建立完善的索引管理體系。

一、系統表中使用者索引檢查

SYSTEM表空間為資料庫安裝時自動建立，存放核心資料字典、預存程序、套件、函數、觸發器定義及系統回滾段。為避免管理風險，應嚴格禁止非SYS/SYSTEM使用者在SYSTEM表空間建立物件。一旦系統表空間損壞，將面臨重建資料庫的嚴重後果。

以下SQL可快速檢查是否有非系統使用者的索引佔用SYSTEM表空間：

SELECT COUNT(*)
FROM dba_indexes
WHERE tablespace_name = 'SYSTEM'
AND owner NOT IN ('SYS', 'SYSTEM');

二、索引儲存狀況深度分析

儲存結構層級說明

• 資料區塊：Oracle最小儲存單位，大小由DB_BLOCK_SIZE參數決定，資料庫建立後不可變更（Oracle 9i除外）
• 範圍：由連續區塊組成，一或多個範圍構成段
• 段：包含特定邏輯儲存結構的所有資料，範圍可不連續甚至跨資料檔

實用查詢腳本

1. 檢視索引段的範圍數量：

SELECT segment_name, COUNT(*)
FROM dba_extents
WHERE segment_type = 'INDEX'
AND owner = UPPER('&owner')
GROUP BY segment_name;

2. 分析表空間內索引擴展分佈：

SELECT SUBSTR(segment_name,1,20) "SEGMENT NAME",
       bytes,
       COUNT(bytes)
FROM dba_extents
WHERE segment_name IN (
    SELECT index_name
    FROM dba_indexes
    WHERE tablespace_name = UPPER('&表空間'))
GROUP BY segment_name, bytes
ORDER BY segment_name;

三、索引選擇性評估與優化

索引選擇性 = 索引欄位不重複值數量 / 表總行數。值越接近1，索引效率越高。基於成本的優化器會自動忽略低選擇性索引，而規則基礎優化器則需人工干預。

手工測量法（建索引前評估）

針對複合索引（如欄位A、B）：

SELECT COUNT(DISTINCT col_a || '%' || col_b) / COUNT(*)
FROM table_name;

自動測量法（建索引後分析）

1. 分析表：ANALYZE TABLE 表名 COMPUTE STATISTICS;
2. 查詢索引不重複鍵值：SELECT distinct_keys FROM user_indexes WHERE table_name='表名' AND index_name='索引名';
3. 查詢表總行數：SELECT num_rows FROM user_tables WHERE table_name='表名';

選擇性 = distinct_keys / num_rows

總結

建立定期索引檢查機制，結合儲存空間監控與選擇性分析，可有效預防效能退化。實務上建議每月執行一次全面索引健康檢查，對高頻異動表縮短至每週。