🧩 Oracle SQL：查询与删除索引完整指南

本文整理了在 Oracle 数据库中如何 查询索引信息、判断索引类型（全局/局部） 以及 安全删除索引 的完整方法。
适合 DBA、开发者、SQL 优化人员阅读。

📘 一、查看当前用户下的所有索引

SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS
FROM USER_INDEXES;

字段说明：

字段含义 INDEX_NAME 索引名称 TABLE_NAME 所属表 UNIQUENESS 是否唯一（UNIQUE / NONUNIQUE）

⸻

📗 二、查看每个索引包含的字段

SELECT INDEX_NAME, COLUMN_POSITION, COLUMN_NAME
FROM USER_IND_COLUMNS
ORDER BY INDEX_NAME, COLUMN_POSITION;

每条索引可能对应多行结果，每行表示索引的一个字段及其顺序。

⸻

📙 三、判断索引是全局（GLOBAL）还是局部（LOCAL）

仅当表是分区表时，索引才会区分全局或局部。

SELECT INDEX_NAME, TABLE_NAME, PARTITIONED, LOCALITY
FROM USER_PART_INDEXES;

字段说明：

字段含义 LOCALITY 索引类型（LOCAL 或 GLOBAL） PARTITIONED 索引是否分区（YES / NO）

⸻

📒 四、综合查询：索引 + 字段 + 类型

下面这条 SQL 可以一次性输出： • 索引名 • 所属表 • 是否唯一 • 全局/局部 • 索引字段与顺序

SELECT a.INDEX_NAME,
       a.TABLE_NAME,
       a.UNIQUENESS,
       NVL(b.LOCALITY, 'NON-PARTITIONED') AS LOCALITY,
       c.COLUMN_NAME,
       c.COLUMN_POSITION
FROM USER_INDEXES a
LEFT JOIN USER_PART_INDEXES b
       ON a.INDEX_NAME = b.INDEX_NAME
LEFT JOIN USER_IND_COLUMNS c
       ON a.INDEX_NAME = c.INDEX_NAME
ORDER BY a.TABLE_NAME, a.INDEX_NAME, c.COLUMN_POSITION;

示例结果：

INDEX_NAME TABLE_NAME UNIQUENESS LOCALITY COLUMN_NAME COLUMN_POSITION IDX_ASSET_1 ASSET_INFO NONUNIQUE GLOBAL DEPT_CODE 1 IDX_ASSET_1 ASSET_INFO NONUNIQUE GLOBAL ASSET_TYPE 2 IDX_ASSET_2 ASSET_INFO NONUNIQUE LOCAL VOLT_LEVEL 1

⸻

📕 五、查看指定表的索引信息

如果只想查某一张表的索引：

SELECT a.INDEX_NAME, a.UNIQUENESS, NVL(b.LOCALITY,'NON-PARTITIONED') LOCALITY
FROM USER_INDEXES a
LEFT JOIN USER_PART_INDEXES b ON a.INDEX_NAME=b.INDEX_NAME
WHERE a.TABLE_NAME = 'YOUR_TABLE_NAME';

⸻

🧨 六、删除索引

删除索引的语法非常简单：

DROP INDEX 索引名;

✅ 示例

DROP INDEX IDX_ASSET_INFO_01;

该命令会直接删除索引，不影响表中的数据。

⸻

⚠️ 注意事项

场景操作说明普通索引直接使用 DROP INDEX 索引名; 唯一索引（UNIQUE）仍然可直接删除分区表局部索引同样使用 DROP INDEX 索引名; 删除整个索引删除分区索引的单个分区 ALTER INDEX 索引名 DROP PARTITION 分区名; 联合索引不区分字段数量，整条索引一并删除主键/唯一约束自动创建的索引 ❌ 不能直接删，要先删除约束

⸻

🧩 七、删除约束自带索引的正确方式

如果索引是由约束自动生成的，比如主键或唯一约束：


ALTER TABLE ASSET_INFO ADD CONSTRAINT PK_ASSET PRIMARY KEY (ASSET_ID);

此时不能 DROP INDEX PK_ASSET; 而是应使用：

ALTER TABLE ASSET_INFO DROP CONSTRAINT PK_ASSET;

Oracle 会自动删除与该约束绑定的索引。

⸻

🧭 八、删除前查询索引是否存在

在删除前，建议先核对索引名是否正确：

SELECT INDEX_NAME, TABLE_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = 'ASSET_INFO';

⸻

🧰 九、批量删除索引模板（推荐）

如果你要清理某张表的所有索引，可以执行以下 SQL：

SELECT 'DROP INDEX ' || INDEX_NAME || ';' AS DROP_SQL
FROM USER_INDEXES
WHERE TABLE_NAME = UPPER('ASSET_INFO');

执行结果会自动生成所有 DROP INDEX 语句，复制执行即可。

⸻

🏁 十、总结

操作 SQL 查看所有索引 SELECT * FROM USER_INDEXES; 查看索引字段 SELECT * FROM USER_IND_COLUMNS; 判断全局/局部索引 SELECT * FROM USER_PART_INDEXES; 删除索引 DROP INDEX 索引名; 删除约束自带索引 ALTER TABLE 表名 DROP CONSTRAINT 约束名;

⸻

✨ 小贴士：

删除索引会影响查询性能，请务必先确认该索引是否被使用（如执行计划中出现），再执行删除操作。可通过 EXPLAIN PLAN 或 DBA_HIST_SQL_PLAN 分析索引使用情况。

Oracle SQL查询与删除索引完整指南