您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 依用戶名重建索引完整指南：提升效能與釋放空間

編輯：Oracle 資料庫基礎

為什麼需要定期重建索引？

在 Oracle 資料庫管理中，若應用系統頻繁執行大量修改、刪除操作，索引結構會逐漸產生碎片，導致查詢效能下降。更關鍵的是，Oracle 在大量刪除記錄後，表與索引佔用的資料區塊空間並不會自動釋放，這會造成索引表空間浪費。透過重建索引，不僅能大幅提升查詢效能，還能回收已刪除記錄佔用的空間，整理表內資料結構。

依用戶名生成重建索引 SQL 腳本

以下腳本可針對指定使用者產生所有索引的 ALTER INDEX ... REBUILD 指令，並保留原有儲存參數：

SET ECHO OFF;
SET FEEDBACK OFF;
SET VERIFY OFF;
SET PAGESIZE 0;
SET TERMOUT ON;
SET HEADING OFF;
ACCEPT username CHAR PROMPT 'Enter the index username: ';
spool /Oracle/rebuild_&username.sql;

SELECT
'REM +-----------------------------------------------+' || chr(10) ||
'REM | INDEX NAME : ' || owner || '.' || segment_name ||
lpad('|', 33 - (length(owner) + length(segment_name))) ||
chr(10) ||
'REM | BYTES : ' || bytes ||
lpad('|', 34 - (length(bytes))) || chr(10) ||
'REM | EXTENTS : ' || extents ||
lpad('|', 34 - (length(extents))) || chr(10) ||
'REM +-----------------------------------------------+' || chr(10) ||
'ALTER INDEX ' || owner || '.' || segment_name || chr(10) ||
'REBUILD ' || chr(10) ||
'TABLESPACE ' || tablespace_name || chr(10) ||
'STORAGE ( ' || chr(10) ||
' INITIAL ' || initial_extent || chr(10) ||
' NEXT ' || next_extent || chr(10) ||
' MINEXTENTS ' || min_extents || chr(10) ||
' MAXEXTENTS ' || max_extents || chr(10) ||
' PCTINCREASE ' || pct_increase || chr(10) ||
');' || chr(10) || chr(10)
FROM dba_segments
WHERE segment_type = 'INDEX'
AND owner = '&username'
ORDER BY owner, bytes DESC;

spool off;

Windows 環境調整輸出路徑

若在 Windows 系統執行，請修改 spool 路徑，例如：

spool c:\Oracle\rebuild_&username.sql;

進階過濾與參數調整

僅重建大型索引：在 AND owner='&username' 後加入 AND bytes > &max_bytes
調整儲存參數：生成的腳本中可直接修改，例如將 PCTINCREASE 非零值改為 0

判斷索引是否真正需要重建

生成腳本後，務必分析索引碎片程度，避免不必要的重建操作。執行以下查詢觀察刪除葉節點比率：

SQL> ANALYZE INDEX &index_name VALIDATE STRUCTURE;

col name heading 'Index Name' format a30
col del_lf_rows heading 'Deleted|Leaf Rows' format 99999999
col lf_rows_used heading 'Used|Leaf Rows' format 99999999
col ratio heading '% Deleted|Leaf Rows' format 999.99999

SELECT name,
       del_lf_rows,
       lf_rows - del_lf_rows lf_rows_used,
       to_char(del_lf_rows / (lf_rows) * 100, '999.99999') ratio
FROM index_stats
WHERE name = upper('&index_name');

經驗法則：當刪除葉節點比率超過 15%~20% 時，建議執行重建。