🚗 2.8 Oracle失效索引检查

在 Oracle 数据库中，失效索引 指的是状态为 UNUSABLE 的索引。这类索引无法被查询优化器使用，会导致全表扫描，严重影响性能。本文介绍如何检查失效索引、常见原因及修复方法。

一、检查失效索引

1. 查询所有失效索引（普通索引）

SELECT owner, index_name, table_name, status
 FROM dba_indexes
 WHERE status = 'UNUSABLE'
 ORDER BY owner, index_name;

2. 查询分区索引失效情况

分区索引可能部分分区失效，整体索引状态仍为 VALID，但需要检查每个分区：

SELECT index_owner, index_name, partition_name, status
 FROM dba_ind_partitions
 WHERE status = 'UNUSABLE'
 ORDER BY index_owner, index_name, partition_name;

3. 查询子分区索引失效情况

SELECT index_owner, index_name, subpartition_name, status
FROM dba_ind_subpartitions
WHERE status = 'UNUSABLE';

4. 结合表空间查看

SELECT i.owner, i.index_name, i.table_name, i.status, 
       i.tablespace_name, s.bytes/1024/1024 AS size_mb
FROM dba_indexes i
LEFT JOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_name
WHERE i.status = 'UNUSABLE';

二、失效索引常见原因

三、修复失效索引

1. 重建普通索引

ALTER INDEX schema.index_name REBUILD;

推荐使用在线重建，减少锁影响：

ALTER INDEX schema.index_name REBUILD ONLINE;

2. 重建分区索引的分区

ALTER INDEX schema.index_name REBUILD PARTITION partition_name;

3. 重建整个分区索引（所有分区）

如果多个分区失效，可重新创建整个索引：

ALTER INDEX schema.index_name REBUILD;

对于分区索引，REBUILD 会重建所有分区，但可能需要较多时间和资源。

4. 批量重建失效索引

使用以下脚本生成重建语句：

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD ONLINE;' AS rebuild_cmd
FROM dba_indexes
WHERE status = 'UNUSABLE';

或针对分区：

SELECT 'ALTER INDEX ' || index_owner || '.' || index_name || 
       ' REBUILD PARTITION ' || partition_name || ' ONLINE;' AS rebuild_cmd
FROM dba_ind_partitions
WHERE status = 'UNUSABLE';

四、预防与监控

• 日常巡检：定期执行失效索引检查，纳入数据库巡检脚本。
• DDL 操作规范​：执行 MOVE​、TRUNCATE PARTITION 等操作后，立即重建受影响索引。
• 监控表空间使用：确保索引表空间有足够空间，避免索引扩展失败。
• 收集统计信息：重建索引后应及时收集统计信息，帮助优化器正确使用索引。

五、注意事项

• 重建索引会消耗资源，建议在业务低峰期进行。
• 对于超大索引，可使用 REBUILD ONLINE 减少锁影响，但会增加日志生成和 I/O。
• 分区索引重建时，可分批处理，避免一次锁住所有分区。
• 如果失效索引属于某个应用且不常用，可考虑先评估是否必要重建。

通过定期检查并修复失效索引，可以确保数据库始终处于高效运行状态，避免因索引不可用导致的性能问题。