如何删除ADW/ADB中Auto Indexing功能自动创建的索引？

操作前准备：识别自动创建的索引 #

自动索引由系统创建，默认索引名前缀为SYS_AI，且索引属性中AUTO字段值为YES，可执行以下SQL查询确认要删除的目标索引：

-- 查看当前用户下所有自动创建的索引
SELECT index_name, table_name, status, auto 
FROM user_indexes 
WHERE auto = 'YES'
ORDER BY table_name, index_name;

-- 如需查询全库范围的自动索引，DBA权限用户可执行
SELECT owner, index_name, table_name, status, auto 
FROM dba_indexes 
WHERE auto = 'YES'
ORDER BY owner, table_name, index_name;

正式删除操作 #

推荐优先使用官方内置包操作，删除时会自动同步Auto Indexing的系统元数据，不会残留异常记录：

• 删除指定Schema下所有自动创建的索引：

-- 替换<schema名称>为实际要操作的Schema名
EXEC DBMS_AUTO_INDEX.DROP_AUTO_INDEXES(schema_name => '<schema名称>');

• 删除单个指定的自动索引：

-- 替换<索引名称>为第一步查询到的SYS_AI开头的目标索引名
EXEC DBMS_AUTO_INDEX.DROP_AUTO_INDEXES(index_name => '<索引名称>');

• 删除指定时间之前创建的所有自动索引：

-- 示例为删除7天前创建的所有自动索引，可自行调整时间参数
EXEC DBMS_AUTO_INDEX.DROP_AUTO_INDEXES(older_than => INTERVAL '7' DAY);

如果遇到内置包操作失败的特殊情况，可直接执行标准DROP INDEX语句删除，注意仅操作AUTO=YES的索引，避免误删业务手动创建的索引：

-- 替换<索引名称>为目标自动索引名
DROP INDEX <索引名称>;

操作后验证 #

删除完成后可重新执行第一步的查询SQL，确认目标索引已不存在即可。 #

注意事项 #

• 删除前可通过DBA_AUTO_INDEX_STATISTICS视图查询索引的使用频次，避免误删仍在提升查询性能的有效自动索引
• 若不希望Auto Indexing后续再次生成同类索引，可将对应表或字段加入自动索引黑名单

内容的提问来源于stack exchange，提问作者Yasin B