Oracle資料庫索引維護實戰指南

前言

本文專注於 Oracle 資料庫中最常見的 B-tree 索引維護操作，參考版本為 Oracle 8i。透過系統視圖查詢與儲存結構分析，協助 DBA 掌握索引健康狀態，預防效能瓶頸。

一、檢查 SYSTEM 表空間中的非系統索引

SYSTEM 表空間為資料庫安裝時自動建立，存放資料字典、預存程序、套件、函數、觸發器定義及系統回滾段。為避免管理風險，強烈建議不要在 SYSTEM 表空間存放非 SYS/SYSTEM 使用者的物件。一旦 SYSTEM 表空間損壞，往往需重建整個資料庫。

以下 SQL 可快速偵測是否有其他使用者的索引佔用 SYSTEM 表空間：

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

若回傳值大於 0，應立即規劃移動這些索引至適當的使用者表空間。

二、索引儲存結構深度解析

1. Oracle 儲存層級架構

Oracle 邏輯儲存單位由小到大依序為：資料區塊、範圍、段。

• 資料區塊：最小儲存單位，大小由 DB_BLOCK_SIZE 參數決定，資料庫建立後不可變更（Oracle 9i 另有彈性設定，不在本文討論範圍）。
• 範圍：由一組連續區塊組成，一個或多個範圍構成一個段。
• 段：包含特定邏輯儲存結構的所有資料，其範圍可不連續，甚至分佈於不同資料檔案。

每個物件（如索引）僅對應一個段，透過查看段中的範圍分佈，即可掌握物件的儲存狀況。

2. 實用查詢腳本

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

結語

索引維護是資料庫效能調優的基石。定期檢查 SYSTEM 表空間純淨度、監控索引段範圍碎片化程度，並配合適當的儲存參數設定，可顯著提升查詢效能並降低維護成本。

• 首頁
• 上一頁
• 1
• 2
• 3
• 下一頁
• 尾頁
• 共3頁