重复索引指同表同列定义的冗余索引，需通过dba_indexes与dba_ind_columns联查列顺序和内容识别，而非仅依赖索引名称。
如何识别重复索引（同表同列的冗余索引）
oracle 允许在同一个表、相同列上创建多个索引，但这些索引物理上互不感知，容易被误建为“重复索引”——比如 idx_emp_dept_id 和 idx_emp_deptid 都基于 dept_id 列，或两个索引都只包含单列 status。这类索引不会报错，但会浪费空间、拖慢 dml 性能。
识别关键点是：比较索引定义的列顺序和内容，而非仅看名称。需联合 dba_indexes 与 dba_ind_columns 查询：
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
);这个查询只返回「列集合完全一致」的索引对。注意：LISTAGG 依赖列顺序，所以即使列名相同但顺序不同（如 (a,b) vs (b,a)），不会被识别为重复——这其实是合理差异，不能删。
删除冗余索引前必须做的三件事
直接 DROP INDEX 可能导致 SQL 执行计划劣化或应用报错，尤其当索引被隐式依赖时（如外键约束、物化视图日志、统计信息采样）。务必验证：
-
检查是否被外键引用：执行
SELECT * FROM dba_constraints WHERE constraint_type = 'R' AND index_name = 'YOUR_IDX_NAME'；若有结果，说明该索引支撑外键，不可删 -
确认无监控中的使用痕迹：运行
ALTER INDEX your_idx MONITORING USAGE至少一个完整业务周期（含批处理），再查v$object_usage中USED是否为NO -
比对执行计划影响：对典型 SQL（如高频查询、关键更新）用
EXPLAIN PLAN FOR ...分别在删前/重建后跑一次，确认access_predicates和filter_predicates没变化
删除索引后空间不会自动回收？
执行 DROP INDEX 后，对应段（segment）立即释放，但所在表空间的空闲空间（free space）未必立刻反映在 dba_free_space 中——尤其当索引段原本位于非自动段管理（ASSM）表空间，或存在高水位线（HWM）卡住的情况。
若目标是真正释放磁盘空间（比如收缩数据文件），需要额外操作：
- 如果是本地管理表空间（LMT），且启用了 ASSM：
ALTER TABLESPACE your_ts_name COALESCE可合并相邻空闲区，但不缩减文件大小 - 要缩小数据文件，必须先确保该文件末尾全是空块：
SELECT block_id, blocks FROM dba_extents WHERE file_id = X AND segment_name IS NULL ORDER BY block_id DESC查末尾是否为空；确认后执行ALTER DATABASE DATAFILE 'path/to/file.dbf' RESIZE N G - 更稳妥的方式是重建索引到新表空间（如
CREATE INDEX ... TABLESPACE new_ts），再删旧索引，最后DROP TABLESPACE old_ts INCLUDING CONTENTS AND DATAFILES
为什么 v$object_usage 显示 USED=NO 还不能删？
v$object_usage 只记录自开启监控以来是否被 CBO 选中用于执行计划，不覆盖以下场景：
- 应用代码里硬编码了
INDEX提示（/*+ INDEX(t idx_name) */），即使 CBO 不选它，SQL 仍强制走该索引 - 物化视图快速刷新依赖特定索引，删掉会导致
ORA-12004错误 - 分区表的局部索引被用于分区裁剪，而监控期间没触发对应分区访问
- 审计、闪回查询、LogMiner 等后台任务可能间接依赖索引结构
真正安全的判断依据，是结合 AWR 报告中的 Index Usage 部分 + 应用方确认 + 变更窗口内灰度验证——而不是只盯着 v$object_usage 的 YES/NO。
