小伙們日常里有沒有被業務和BOSS要求新建索引或是重建索引？他們都想著既快又穩，那么索引在在Oracle上如何實現、新建、重建。原則是什么：

1、新建索引，查詢是否高頻且慢，索引列是否高選擇性，新增索引對寫負載的影響是否可接受。

2、重建索引，驗證碎片率/B樹高度是否超標，測試重建后查詢提升是否有15%以上呢。

一、?核心索引類型與原理?

二、Oracle的index演進：重建機制對比

?1. 11g時代：基礎重建框架?

?2. 19c增強：穩定與自動化?

?3. 23ai革新：AI驅動與智能治理?

三、新建索引方法與場景?

?1. 場景選擇與優化原則

單表索引數 ≤ 列數的20%，避免DML性能下降。小表無需索引，全表掃描更快?

?索引類型?	?適用場景?	?優化建議?
?B*Tree?	主鍵、外鍵、范圍查詢	避免在頻繁更新的列上創建
?位圖索引?	性別、狀態等低基數枚舉值	僅適用于OLAP，禁用OLTP
?復合索引?	多列組合查詢（如WHERE dept_id=10 AND job_id='IT_PROG'）	第一列需被WHERE引用
?函數索引?	條件含表達式（如UPPER(last_name)='SMITH'）	確保函數穩定性

2. 新建步驟（以HR.EMPLOYEES為例）?

-- 單列B*Tree索引（高頻查詢列）
CREATE INDEX IDX_EMP_DEPT_lastname ON employees(last_name);
--
SYS@CDB$ROOT> CREATE INDEX IDX_EMP_DEPT_lastname ON HR.employees(last_name);
Index IDX_EMP_DEPT_LASTNAME created.
-- 復合索引（多列查詢，高頻條件列在前）
CREATE INDEX IDX_EMP_DEPT_dept ON HR.employees(EMPLOYEE_id,department_id);-- 位圖索引（低基數列）
ALTER TABLE HR.EMPLOYEES ADD (gender int);--官方schema的sql中建表無性別
CREATE BITMAP INDEX IDX_EMP_DEPT_gender ON HR.employees(gender);

四、索引重建策略與實戰分析（以HR.EMPLOYEES為例）??

?1. 重建前提與評估?

?2. 重建實操流程

-- 步驟1：分析碎片率
ANALYZE INDEX IDX_EMP_DEPT_dept VALIDATE STRUCTURE;
SELECT name, height, DEL_LF_ROWS/LF_ROWS AS frag_ratio FROM index_stats;
-- 若frag_ratio>0.2則需重建
SYS@CDB$ROOT> ANALYZE INDEX IDX_EMP_DEPT_DEPT VALIDATE STRUCTURE;
Index IDX_EMP_DEPT_DEPT analyzed.
NAME               HEIGHT    FRAG_RATIO
_______________ _________ _____________
IDX_EMP_DEPT_DEPT            1             0-- 步驟2：在線重建（避免阻塞DML）
ALTER INDEX IDX_EMP_DEPT_dept REBUILD ONLINE TABLESPACE HR_data;
SYS@CDB$ROOT> ALTER INDEX IDX_EMP_DEPT_dept REBUILD ONLINE TABLESPACE HR_data;
Index IDX_EMP_DEPT_DEPT altered.-- 步驟3：驗證效果  
-- 檢查是否走索引
EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id=60;ALTER INDEX IDX_EMP_DEPT_dept REBUILD ONLINE;
SYS@CDB$ROOT> ALTER INDEX IDX_EMP_DEPT_dept REBUILD ONLINE;
Index IDX_EMP_DEPT_DEPT altered.--遷移索引位置：TABLESPACE從HR_DATA 索引重建到SH_data
ALTER INDEX IDX_EMP_DEPT_DEPT REBUILD TABLESPACE SH_DATA;
SYS@CDB$ROOT> ALTER INDEX IDX_EMP_DEPT_DEPT REBUILD TABLESPACE SH_DATA;
Index IDX_EMP_DEPT_DEPT altered.--性能優化參數?PARALLEL n**?啟用并行進程（建議值為CPU核數50%-70%）
ALTER INDEX IDX_EMP_DEPT REBUILD PARALLEL 8;--STORAGE**?調整物理存儲屬性（需在重建前規劃）
ALTER INDEX IDX_EMP_DEPT REBUILD STORAGE (INITIAL 100M NEXT 50M);--COMPRESS ADVANCED啟用高級壓縮減少空間占用
ALTER INDEX IDX_EMP_DEPT REBUILD COMPRESS ADVANCED;

五、典型異常與解決方案

1. ?重建失敗索引失效（ORA-01502）??

?現象?：索引狀態變為UNUSABLE，查詢報錯ORA-01502，表空間遷移或手動禁用索引后未重建

-- 檢查失效索引
SELECT index_name, status FROM dba_indexes WHERE status='UNUSABLE';-- 重建失效索引
ALTER INDEX IDX_EMP_DEPT REBUILD;

2. ?空間不足（ORA-01654）??

?預防?：重建前檢查表空間，規則?：所需空間 ≈ 原索引大小的1.2倍

SELECT tablespace_name, SUM(bytes)/1024/1024 free_space_mb 
FROM dba_free_space 
GROUP BY tablespace_name;

3. 碎片DBMS包診斷

--基礎調用（僅收集索引統計），注意OWNNAME 哪個用戶建的
BEGINDBMS_STATS.GATHER_INDEX_STATS(ownname => 'SYS',indname => 'IDX_EMP_DEPT_dept');
END;
/
--擴展參數（采樣率 + 并行度）
BEGINDBMS_STATS.GATHER_INDEX_STATS(ownname          => 'SYS',indname          => 'IDX_EMP_DEPT_dept',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- 自動采樣degree           => 4  -- 并行度（建議≤CPU核數）);
END;
/
-- 23ai支持JSON輸出診斷報告
ANALYZE INDEX emp_name_idx VALIDATE STRUCTURE;
SELECT name, height, ROUND((del_lf_rows/NULLIF(lf_rows,0))*100,2) frag_pct 
FROM index_stats;

4. 重建后必做操作?

DECLAREisClean BOOLEAN;
BEGIN-- 使用有效參數名，且不傳遞 cleanup_levelisClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(OBJECT_ID => DBMS_REPAIR.ALL_INDEX_ID,  -- 清理所有中斷索引WAIT_FOR_LOCK => DBMS_REPAIR.LOCK_WAIT    -- 默認鎖等待策略);
END;
/
--PL/SQL procedure successfully completed.

• 指定OBJECT_ID修復?

DECLAREisClean BOOLEAN;v_index_id NUMBER := 68100; -- 替換為實際索引的OBJECT_ID（從DBA_OBJECTS查詢）
BEGINisClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(v_index_id);
END;
/
---若表上有活躍DML操作，函數可能因無法獲取鎖而返回FALSE。此時需檢查鎖競爭
SELECT sid, serial# 
FROM v$session 
WHERE sid IN (SELECT session_id FROM dba_locks WHERE object_id = <索引ID>);

六、索引生命周期中索引影響與風險控制?