依使用者產生 Oracle 索引重建腳本：釋放空間與提升效能

為什麼要定期重建索引？

大量刪除/更新後，索引區塊未釋放，導致空間浪費、掃描效能下降。重建索引可：

• 回收已刪除項目佔用的區塊空間
• 降低樹高度，提升範圍掃描速度
• 整理儲存參數（如 PCTINCREASE 歸零）

一鍵產生重建腳本 (SQL*Plus 版)

SET ECHO OFF FEEDBACK OFF VERIFY OFF PAGESIZE 0 TERMOUT ON 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

進階客製化

• 僅重建大索引：在 WHERE 加入 AND bytes > &max_bytes
• 調整儲存參數：編輯產出的 .sql，如將 PCTINCREASE 設為 0
• Windows 路徑：SPOOL c:\Oracle\rebuild_&username.sql

執行前必做：碎片嚴重度驗證

ANALYZE INDEX &index_name VALIDATE STRUCTURE;
SELECT height, lf_blks, del_lf_rows, pct_used 
FROM index_stats;

關鍵指標：HEIGHT > 3、PCT_USED < 50%、DEL_LF_ROWS / LF_ROWS > 20% 即建議重建。