悠悠楠杉
网站页面
Oracle数据库查看表索引信息的完整指南
07/10
一、索引的重要性与查询需求
在Oracle数据库管理中，索引就像书籍的目录一样重要。当表数据量达到百万级时，没有索引的查询可能会变成性能灾难。作为DBA，我经常遇到开发团队询问："这个表到底有哪些索引？"掌握索引查询方法不仅能优化SQL性能，还能避免重复创建索引带来的存储浪费。
二、核心数据字典视图
Oracle通过以下数据字典视图存储索引信息：
- DBA_INDEXES - 数据库所有索引（需要DBA权限）
- USER_INDEXES - 当前用户拥有的索引
- ALL_INDEXES - 当前用户可访问的索引
- USERINDCOLUMNS - 索引列信息
三、7种实用查询方法
1. 查询表的基本索引信息
sql
SELECT index_name, index_type, uniqueness, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';
这会显示EMPLOYEES表的所有索引名称、类型（NORMAL/BITMAP等）、是否唯一以及当前状态（VALID/UNUSABLE）。上周我就用这个语句发现了一个UNUSABLE状态的索引，及时修复后查询速度提升了5倍。
2. 查看索引列明细
sql
SELECT i.index_name, i.uniqueness, c.column_name, c.column_position
FROM user_indexes i
JOIN user_ind_columns c ON i.index_name = c.index_name
WHERE i.table_name = 'ORDERS'
ORDER BY i.index_name, c.column_position;
这个查询结果会显示每个索引的具体列构成，特别是对复合索引（多列索引）特别有用。曾有个性能问题最终发现是复合索引的列顺序不合理导致的。
3. 查看函数索引表达式
sql
SELECT index_name, column_expression
FROM user_ind_expressions
WHERE table_name = 'SALES';
当使用函数索引（如UPPER(name)）时，这个视图可以查看索引的实际表达式，避免维护时出现表达式不一致的情况。
4. 检查索引存储空间
sql
SELECT segment_name, bytes/1024/1024 MB
FROM user_segments
WHERE segment_type = 'INDEX'
AND segment_name IN (
SELECT index_name
FROM user_indexes
WHERE table_name = 'CUSTOMERS'
);
大型索引可能占用数GB空间，这个查询可以帮助评估索引的存储成本。去年我们就通过这个分析删除了3个冗余索引，节省了40GB存储。
5. 监控索引使用频率
sql
SELECT name, used
FROM v$object_usage
WHERE index_name IN (
SELECT index_name
FROM user_indexes
WHERE table_name = 'PRODUCTS'
);
需要先执行ALTER INDEX index_name MONITORING USAGE开启监控。这个功能可以帮助识别"僵尸"索引，我曾经用这个发现过6个月未被使用的索引。
四、高级应用场景
分区表索引查询
sql
SELECT index_name, partitioning_type, partition_count
FROM user_part_indexes
WHERE table_name = 'TRANSACTIONS';
分区表的索引结构更复杂，需要额外关注本地索引和全局索引的区别。某次性能调优中，我们发现一个全局索引导致分区裁剪失效的问题。
索引统计信息检查
sql
SELECT index_name, blevel, leaf_blocks, distinct_keys
FROM user_indexes
WHERE table_name = 'INVENTORY';
这些统计信息对查询优化器非常重要，blevel过高可能意味着需要重建索引。
五、实际维护建议
- 定期检查：建议每月检查一次关键表的索引状态
- 命名规范：使用IDXTABLECOLUMN的命名规则便于管理
- 文档记录：维护索引变更日志，记录每个索引的创建目的
最近我们通过系统化的索引管理，将关键批处理作业的运行时间从4小时缩短到30分钟。合理的索引策略确实能带来质的飞跃。
掌握这些索引查询技术后，你会发现自己对数据库性能问题的诊断能力将大幅提升。建议收藏这些SQL语句，它们会成为你DBA工具箱中的常备武器。
