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

按用戶生成 Oracle 索引重建腳本：從碎片分析到定時部署

編輯：Oracle 資料庫相關知識

生成重建腳本核心 SQL

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

碎片判斷標準

ANALYZE INDEX &index_name VALIDATE STRUCTURE;

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% 必須重建。