小伙们日常里有没有被业务和BOSS要求新建索引或是重建索引？他们都想着既快又稳，那么索引在在Oracle上如何实现、新建、重建。原则是什么：

1、新建索引，查询是否高频且慢，索引列是否高选择性，新增索引对写负载的影响是否可接受。

2、重建索引，验证碎片率/B树高度是否超标，测试重建后查询提升是否有15%以上呢。

一、核心索引类型与原理

B*Tree索引（默认）

结构：平衡树（根节点→分支节点→叶子节点），叶子节点双向链表存储键值+ROWID
查询效率：时间复杂度O(log n)，千万级数据定位仅需约23次比较
适用场景：高基数列（比如EMPLOYEE_ID）、范围查询（比如SALARY > 10000）

位图索引

原理：为每个键值创建位图（0/1标识行存在性），通过位运算（AND/OR）加速组合查询
适用场景：低基数列（如GENDER）、OLAP系统

函数索引（Oracle 8i就开始引入）

机制：对列的函数结果建索引（如UPPER(LAST_NAME)）

二、Oracle的index演进：重建机制对比

1. 11g时代：基础重建框架

在线重建初现：REBUILD ONLINE首次实现DML不阻塞，但首尾需毫秒级表锁（LOCK TABLE IN EXCLUSIVE MODE）
资源消耗大：并行处理需手动管理（PARALLEL n），临时表空间易爆增（10亿级索引排序易触发multipass）
空间要求高：需预留1.5倍原索引空间，否则引发ORA-01654

2. 19c增强：稳定与自动化

在线锁优化：
自治能力引入：支持自动统计信息收集（DBMS_STATS.AUTO_GATHER），减少手动维护
热重建支持：RESUMABLE_TIMEOUT支持中断恢复（如空间不足暂停）

3. 23ai革新：AI驱动与智能治理

向量索引革命：新增VECTOR数据类型，支持AI语义搜索（需启用vector pool内存区）
自治重建升级：当blevel≥4或碎片率>20%时，自动触发重建（需开启AUTO_INDEX_MAINTENANCE）

资源自适配：OPTIMIZE_LOAD参数自动平衡I/O与CPU负载（NVMe环境性能提升40%）

三、新建索引方法与场景

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. 重建前提与评估

触发条件：
- 索引高度 ≥4（SELECT blevel FROM dba_indexes WHERE index_name='IDX_EMP_DEPT_DEPT';）
- 碎片率 >20%（ANALYZE INDEX idx_name VALIDATE STRUCTURE; → SELECT DEL_LF_ROWS/LF_ROWS FROM index_stats;）
- 表频繁发生UPDATE/DELETE（如HR.EMPLOYEES的历史数据清理）

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. 重建后必做操作

ORA-08104 残留中断

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>);