萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 依使用者重建索引自動化腳本:產生、分析、排程一條龍

為何需要定期重建索引?

  • 大量 DML 導致索引碎片化、高水位線不下降、空間浪費。
  • 重建可回收已刪除條目空間、壓縮高度、提升掃描效率。

一鍵產生重建腳本

SET ECHO OFF FEEDBACK OFF VERIFY OFF PAGESIZE 0 TERMOUT ON HEADING OFF
ACCEPT username CHAR PROMPT 'Enter index owner: '
SPOOL 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`

只重大索引:`WHERE ... AND bytes > &max_bytes`

產出腳本二次加工建議

  • 將 `PCTINCREASE` 非 0 改為 0(避免漸增區塊碎片)。
  • 加入 `ONLINE` 關鍵字(Enterprise Edition):`ALTER INDEX ... REBUILD ONLINE`,零停機。
  • 指定 `PARALLEL n` 加速重建。

碎片嚴重度判斷:ANALYZE INDEX VALIDATE STRUCTURE

SQL> ANALYZE INDEX idx_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 AS lf_rows_used,
       TO_CHAR(del_lf_rows / lf_rows * 100, '999.99999') AS ratio
FROM   index_stats
WHERE  name = UPPER('&index_name');

判準:`ratio > 15~20%` → **必須重建**。

排程部署與例外處理

  • 放入 `DBMS_SCHEDULER` 或 `CRON`,月/雙月非尖峰時段執行。
  • 遇 `ORA-00054`(資源忙) → 捕捉例外記錄日誌,**下次再試**,勿中斷整批。
  • 極熱表(核心交易表)索引**移出**此腳本,改用 `ONLINE REBUILD` 獨立排程。

結語

「產生腳本 → 分析篩選 → 手動微調 → 排程自動化」四步驟,將索引重建從人工苦力變成可控自動化作業。關鍵在於**事前分析碎片度**,避免無效重建浪費資源與鎖定風險。

copyright © 萬盛學電腦網 all rights reserved