Oracle Index 維護方式

Oracle 11g

1. 清查統計資訊：確認資料的「新鮮度」

在 11g，如果 LAST_ANALYZED 太久遠，BLEVEL 的參考價值會大打折扣。

實務作法： 使用以下 SQL 找出深度異常（>=3）且統計資訊超過 7 天未更新的索引：

SELECT 
    owner, index_name, table_name, blevel, 
    leaf_blocks, num_rows, last_analyzed
FROM dba_indexes
WHERE blevel >= 3 
  AND last_analyzed < SYSDATE - 7
  AND owner NOT IN ('SYS', 'SYSTEM');

2. 確認使用情況：找出「殭屍索引」

在 11g 中，這一步最耗時但也最省資源。

• 開啟監控 (Monitoring)： 11g 必須針對每個索引手動開啟監控。

  ALTER INDEX schema_name.index_name MONITORING USAGE;
  

• 檢查結果： 觀察一兩個完整業務週期（例如一週或一個月）後查詢：

  SELECT index_name, used, start_monitoring, end_monitoring 
  FROM v$object_usage;
  

  大師筆記： v$object_usage 只能看到當前使用者的監控。若要看全庫，需以 SYS 權限查詢底層表 sys.object_usage。

3. 評估該刪除：實施「安全緩衝區」

11g 引入了 Invisible Index (不可見索引)，這是業界公認最安全的刪除前置作業。

• 操作步驟：
  1. 將疑似沒用的索引設為 INVISIBLE。
  2. 觀察 AWR 報告或應用程式有無報錯。
  3. 若確定無誤，再執行 DROP INDEX。

  ALTER INDEX index_name INVISIBLE;
  

4. 評估該優化：深度的科學判斷

針對你提到的 TIP，在 11g 實務中，我們判斷「是否值得 Rebuild」通常會動用 VALIDATE STRUCTURE 命令。

深度診斷步驟：

1. 收集索引內部碎片資訊：

   ANALYZE INDEX schema_name.index_name VALIDATE STRUCTURE;
   

2. 查詢 INDEX_STATS 視圖： 這是 11g DBA 的黃金準則，檢查這兩個指標：
   • HEIGHT (即 BLEVEL+1)： 是否大於等於 4。
   • DEL_LF_ROWS_LEN / LF_ROWS_LEN： 刪除的列所佔空間是否超過 20%。

   SELECT name, height, del_lf_rows_len, lf_rows_len,
          (del_lf_rows_len / decode(lf_rows_len, 0, 1, lf_rows_len) * 100) AS pct_deleted
   FROM index_stats;
   

   警語： 在 11g 中，ANALYZE INDEX ... VALIDATE STRUCTURE 會鎖表 (Table Lock)。請務必在離峰時段操作。

5. 執行優化：11g 的最佳實踐指令

若決定要 Rebuild，請遵循以下步驟以最小化對生產環境的衝擊：

1. Online Rebuild： 避免鎖定 DML 操作。
2. Parallel： 加快速度（需確認 CPU 負載）。
3. Reset Degree： 完成後務必改回 NOPARALLEL，否則 Optimizer 會誤判執行成本。

-- 1. 線上重建並啟動平行處理
ALTER INDEX schema_name.index_name REBUILD ONLINE PARALLEL 4;

-- 2. 恢復索引的平行度設定 (非常重要！)
ALTER INDEX schema_name.index_name NOPARALLEL;

-- 3. 重新收集統計資訊
EXEC DBMS_STATS.GATHER_INDEX_STATS('SCHEMA_NAME', 'INDEX_NAME');

總結大師建議：

在 11g 實務中，「不要為了 Rebuild 而 Rebuild」。 如果你發現一個索引深度為 4，但它是幾億筆資料且 key 是長字串（如 URL 或 GUID），這是正常現象。 真正需要優化的是那些：資料量沒變、Key 很短，但深度卻莫名其妙增加的索引。

Oracle 19c

1. 清查統計資訊：利用「即時統計資訊」

在 19c 中，Oracle 引入了 Real-time Statistics。當你在進行大量資料導入（Bulk Load）時，資料庫會自動更新統計資訊，不再像舊版本那樣非得等晚上排程。

實務作法： 直接查詢 DBA_INDEXES，但 19c 我們更看重 BLEVEL 與 CLUSTERING_FACTOR 的比例。

SELECT 
    owner, index_name, table_name, blevel, 
    leaf_blocks, num_rows, last_analyzed,
    (SELECT blocks FROM dba_tables t WHERE t.table_name = i.table_name AND t.owner = i.owner) as table_blocks,
    clustering_factor
FROM dba_indexes i
WHERE blevel >= 3 
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY blevel DESC;

2. 最後使用時間與次數：19c 的自動監控

在 11g 必須手動 ALTER INDEX ... MONITORING USAGE，但在 19c，資料庫預設會自動收集索引使用狀況。

最佳實踐： 直接查詢 DBA_INDEX_USAGE_INFO。它不僅告訴你索引有沒有被使用過，還能告訴你使用了幾次、最後一次使用的時間。

-- 19c 內建功能，直接看索引使用頻率與最後使用時間
SELECT 
    u.owner, u.index_name, u.table_name,
    u.total_access_count,
    u.last_used
FROM dba_index_usage_info u
JOIN dba_indexes i ON u.index_name = i.index_name AND u.owner = i.owner
WHERE i.blevel >= 3;

3. 評估該刪除：自動索引與不可見測試

19c Enterprise Edition 有一個殺手級功能：Automatic Indexing。它會自動評估並建立索引，也會自動標記不使用的索引。

實務作法： 對於你懷疑深度過高且不常使用的索引，先執行 Invisible。

1. 設為不可見： ALTER INDEX index_name INVISIBLE;
2. 觀察期： 19c 的 Automatic Workload Repository (AWR) 會幫你監控是否有 SQL 因為少了這個索引而效能衰退。

4. 評估該優化：19c 的智慧化建議

針對你提到的那個 TIP（深度大於 3 或 4），19c 提供了一個更直覺的工具：Segment Advisor。

實務作法： 不要再手動算 BLEVEL 了，直接請 Oracle 算給你聽：

-- 使用 DBMS_ADVISOR 建立一個 Segment Advisor 任務
-- 它可以精確告訴你索引「浪費了多少空間」，並直接建議是否該 REBUILD
SELECT 
    segment_name, segment_type, 
    alloc_mb, used_mb, reclaimable_mb, 
    recommendations
FROM TABLE(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));

5. 執行優化：19c 的無感重建

在 19c 重建索引時，建議使用以下組合拳：

• ONLINE： 必備，19c 的 Online Rebuild 幾乎不影響併發。
• PARALLEL： 善用 CPU 多核優化。
• SHRINK SPACE (僅限特定情況)： 對於某些索引，如果你不想重建整個結構，可以使用 COALESCE（合併），它不會像 Rebuild 那樣佔用大量暫存空間。

-- 19c 推薦的重建方式
ALTER INDEX schema_name.index_name REBUILD ONLINE PARALLEL 8;
ALTER INDEX schema_name.index_name NOPARALLEL;

大師的總結建議：

在 19c 中，你不需要再像 11g 那樣戰戰兢兢地手動監控。

1. 利用 DBA_INDEX_USAGE_INFO 排除掉沒人在用的索引（這佔了深度問題的 50%）。
2. 利用 Segment Advisor 獲取官方的空間回收建議。
3. 確認 BLEVEL 的增長是否為常態： 如果是大量 DELETE 導致的（例如 Log 表），建議在 19c 中將該索引建立為 Global Hash Partitioned Index（如果底層是分區表），這能有效控制 B*Tree 的深度。

Index 類型評估

你好！我是 Oracle Database 大師。這是一個非常經典且關乎系統成敗的問題。選錯索引類型，輕則浪費空間，重則導致嚴重的鎖定（Locking）衝突與效能崩潰。

在 Oracle 19c 的環境下，選擇索引的邏輯已經非常明確。我為你整理了一份決策指南，幫助你精準選型。

1. 核心差異與選擇邏輯

這兩種索引的底層結構完全不同，因此適用的場景也截然不同。

B*Tree Index (預設索引)

• 結構： 樹狀結構（Root -> Branch -> Leaf）。
• 適用對象： 高基數（High Cardinality） 的欄位。也就是說，該欄位的值重複率很低，例如 EMPLOYEE_ID、SSN、ORDER_NUMBER。
• 適用操作： 精確查詢（=）、範圍查詢（>、<、BETWEEN）。
• 併發性： 優秀。多個使用者同時更新（DML）不同列時，不太會互相鎖定。

Bitmap Index (點陣圖索引)

• 結構： 使用位元（0 與 1）的對應表來儲存資料與列的關係。
• 適用對象： 低基數（Low Cardinality） 的欄位。例如 GENDER (男/女)、STATUS (有效/無效)、REGION (北/中/南/東)。
• 適用操作： 多個欄位組合的 AND / OR 複雜查詢。
• 併發性： 極差。更新一筆資料會鎖定整個「點陣圖段（Bitmap Segment）」，容易造成嚴重的 TX - Row Lock Contention。

2. 實戰決策流程圖

當你準備新增 Index 時，請問自己以下三個問題：

第一步：這是資料倉儲 (DW) 還是 線上交易系統 (OLTP)？

• OLTP (頻繁增刪改)： 幾乎一律使用 B*Tree。
• DW/BI (大量查詢，批次更新)： 可以考慮 Bitmap。

第二步：欄位的「基數」高嗎？

• 高基數 (如 ID, 日期時間)： 使用 B*Tree。
• 低基數 (如 類別, 狀態碼)： * 如果是 OLTP，仍然建議 B*Tree。
  • 如果是 DW，且查詢常包含多種條件組合，使用 Bitmap。

第三步：是否會頻繁進行 DML (Insert/Update/Delete)？

• 是： 絕對不要用 Bitmap。在 Bitmap 索引上的任何一筆更新都可能鎖定成千上萬行資料。
• 否 (多為唯讀或深夜批次轉檔)： Bitmap 是首選，因為它的壓縮率極高，掃描速度極快。

3. 業界公認的最佳實踐 (Best Practices)

4. 大師的特別提醒：Bitmap Join Index

在 19c 中，如果你在做資料倉儲優化，還有一個進階武器叫 Bitmap Join Index (BJI)。它可以預先將 Fact Table (事實表) 與 Dimension Table (維度表) 的關聯結果做成索引。這在處理「星狀架構 (Star Schema)」查詢時，效能簡直是神級的提升。

總結你的選擇建議：

1. 如果你不確定，選 B*Tree 永遠是安全牌。
2. 只有在「資料幾乎不變」且「欄位值很少（低基數）」的統計報表需求下，才考慮 Bitmap。
3. 千萬不要在會被頻繁 Update 的欄位上建立 Bitmap Index。

如何安全的建立 Index

1. 建立 B*Tree Index (預設索引)

在 Oracle 中，如果你在 CREATE 後面直接接 INDEX，預設就是建立 B*Tree 索引。

基本語法：

CREATE INDEX idx_emp_id ON employees(employee_id);

業界推薦的進階下法 (Best Practice)：

在生產環境，我們通常會加上一些參數來確保效能與空間管理：

CREATE INDEX idx_orders_date 
ON orders(order_date) 
TABLESPACE users_idx_tbs     -- 指定獨立的索引表空間 (建議與 Data 分開)
ONLINE                       -- [重要] 建立期間不鎖表，前端程式可繼續寫入資料
PARALLEL 4                   -- 使用 4 個 CPU 核心同步建立，速度極快
NOLOGGING;                   -- 減少產生的 Redo Log，加速大型索引建立 (注意：建立後需補備份)

-- 建立完後，務必將平行度改回 1，避免往後 SQL 執行計畫誤判成本
ALTER INDEX idx_orders_date NOPARALLEL;

2. 建立 Bitmap Index (點陣圖索引)

要建立 Bitmap 索引，你必須顯式地加上 BITMAP 關鍵字。

基本語法：

CREATE BITMAP INDEX idx_emp_gender ON employees(gender);

業界推薦的進階下法：

由於 Bitmap 索引主要用於資料倉儲（DW），通常資料量極大，平行處理與路徑管理非常重要：

CREATE BITMAP INDEX idx_cust_region 
ON customers(region_code) 
TABLESPACE dw_idx_tbs        -- 通常放在專門給 DW 使用的表空間
PCTFREE 0                    -- [技巧] Bitmap 索引很少更新，可將預留空間設為 0 以節省硬碟
PARALLEL 8                   -- 大型 DW 表建議加大平行度
NOLOGGING;

-- 同樣，建立完後記得關閉平行設定
ALTER INDEX idx_cust_region NOPARALLEL;

3. 兩者語法與行為的關鍵對比

4. 大師的實戰檢查清單

在你按下 Enter 鍵執行 SQL 之前，請幫我檢查這三點：

1. 權限： 你是否擁有 CREATE ANY INDEX 權限？
2. 空間： 檢查 DBA_FREE_SPACE。Bitmap 雖然省空間，但 B*Tree 的空間消耗通常是資料本身的 1.5 到 2 倍。
3. 鎖定 (Locking)： 如果是 B*Tree，請養成習慣加上 ONLINE。如果是 Bitmap，請確認目前沒有任何 Batch 或應用程式正在更新那張表。

額外補充：如何確認建立成功？

建立完後，你可以跑這條 SQL 來檢查索引類型：

SELECT index_name, index_type, visibility, status 
FROM dba_indexes 
WHERE index_name = '你的索引名稱';

(在 INDEX_TYPE 欄位中，你會看到 NORMAL 代表 BTree，而 BITMAP 則代表點陣圖索引。)*