需先启用索引监控（ALTER INDEX ... MONITORING USAGE），再查V$OBJECT_USAGE中USED='NO'且END_MONITORING非空者，方为长期未用索引；同时须排除列集合完全重复的索引。
查哪些索引段长期没被使用过
Oracle 不会自动标记“从未被用过的索引”，但可以通过 V$OBJECT_USAGE 视图确认某个索引是否在启用监控后被实际使用过。关键前提是：必须先对目标索引执行 ALTER INDEX ... MONITORING USAGE，否则该视图始终为空。
常见错误是直接查 V$OBJECT_USAGE 发现没数据，就以为索引没用——其实是还没开监控。监控开启后需持续观察业务高峰期（至少 1–2 个完整业务周期），再查结果才可信。
实操建议：
- 对怀疑冗余的索引逐个启用监控：
ALTER INDEX schema_name.idx_name MONITORING USAGE; - 等足够时间后，用以下语句查真实使用状态：
SELECT index_name, used, start_monitoring, end_monitoring FROM V$OBJECT_USAGE WHERE index_name = 'IDX_NAME'; -
USED = 'NO'且END_MONITORING非空，才表示该索引在整个监控期内未被任何执行计划选中
区分“未使用”和“重复索引”两类问题
一个索引没被用过，不等于它能删；它可能是另一个更宽泛索引的子集（比如 (status) 和 (status, create_time)），删掉前者会导致后者无法高效支持单列查询。所以得先排除重复定义。
判断是否为重复索引，不能只比名字或列名，必须比列顺序和完整列集合。用如下查询找同表下列定义完全一致的索引对：
SELECT i1.owner, i1.table_name, i1.index_name AS idx1, i2.index_name AS idx2,
LISTAGG(ic1.column_name, ',') WITHIN GROUP (ORDER BY ic1.column_position) AS cols1,
LISTAGG(ic2.column_name, ',') WITHIN GROUP (ORDER BY ic2.column_position) AS cols2
FROM dba_indexes i1
JOIN dba_indexes i2
ON i1.owner = i2.owner AND i1.table_name = i2.table_name AND i1.index_name < i2.index_name
JOIN dba_ind_columns ic1
ON i1.owner = ic1.index_owner AND i1.index_name = ic1.index_name
JOIN dba_ind_columns ic2
ON i2.owner = ic2.index_owner AND i2.index_name = ic2.index_name
AND ic1.column_position = ic2.column_position AND ic1.column_name = ic2.column_name
WHERE i1.owner NOT IN ('SYS','SYSTEM')
GROUP BY i1.owner, i1.table_name, i1.index_name, i2.index_name
HAVING COUNT(*) = (SELECT COUNT(*) FROM dba_ind_columns icx
WHERE icx.index_owner = i1.owner AND icx.index_name = i1.index_name)
AND COUNT(*) = (SELECT COUNT(*) FROM dba_ind_columns icy
WHERE icy.index_owner = i2.owner AND icy.index_name = i2.index_name);
返回结果里的 idx1 和 idx2 就是物理上完全冗余的一对，留一个即可。
删索引前必须检查依赖和执行计划影响
直接 DROP INDEX 很快，但可能让原本走索引的 SQL 突然变慢。尤其要注意那些没显式写索引提示、却依赖 CBO 自动选择的语句。
安全做法是分三步验证：
- 用
DBMS_XPLAN.DISPLAY_CURSOR抓几个核心业务 SQL 的实际执行计划，确认当前是否真用了待删索引 - 临时禁用该索引（
ALTER INDEX ... UNUSABLE），观察业务是否报错或性能抖动；注意：这会让所有依赖它的 DML 失败，仅限测试环境或维护窗口内操作 - 检查是否有物化视图日志、函数索引依赖、或分区局部索引引用该索引——这些不会报错但会导致后续刷新失败
删完索引后空间不一定立刻释放
执行 DROP INDEX 后，对应段（segment）立即从 DBA_SEGMENTS 中消失，但表空间里那块空间只是变成“空闲”，不会返还给操作系统（df -h 不变）。真正释放需要触发段收缩或等待自动空间管理生效。
如果发现删完后 DBA_FREE_SPACE 没明显增长，大概率是因为：
- 该索引段所在表空间启用了本地管理（LMT），且 extent 是 uniform size，小碎片无法合并
- 有其他对象正在同一表空间高频分配/释放空间，导致空闲区被快速复用
- 回收站里还存着同名索引的旧版本（极少见，但若之前用过
DROP INDEX ... PURGE以外的方式删过，需查DBA_RECYCLEBIN）
此时不要急着重建表空间，先查 SELECT tablespace_name, SUM(bytes)/1024/1024 FROM dba_free_space GROUP BY tablespace_name; 确认空闲量是否真实增加——数字变了，说明空间已可复用，只是没“归还”给 OS。
