Oracle 依用戶名重建索引方法探究：腳本生成、碎片分析、自動化排程

核心價值

大量 DML 操作導致索引碎片、空間未釋放。依用戶名批次重建索引，可同時提升查詢效能與回收表空間空間。

生成重建腳本（可自訂輸出路徑、過濾大小、調整儲存參數）

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 路徑調整

SPOOL C:\Oracle\rebuild_&username.sql;

進階過濾

AND BYTES > &max_bytes  -- 僅重建超過指定大小的索引

儲存參數微調

編輯產出的腳本，如將 PCTINCREASE 非零值改為 0。

碎片分析：判斷是否真需重建

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% 才建議重建。

自動化與例外處理

• 將修訂後腳本納入 DBMS_SCHEDULER 或 DBMS_JOB，每月/雙月離峰執行
• ORA-00054（資源忙碌）請忽略，下次排程重試
• 高頻熱門表索引請從腳本移除，改用 ALTER INDEX ... REBUILD ONLINE 或人工維護窗口