1. 先搞明白为什么要专门去查一张表的索引做Oracle开发和运维的人迟早都会遇到这么一个场景线上业务突然变慢开发同事甩过来一条SQL说“这条查询跑了十几秒帮我看看怎么优化”。你第一步要做的不是加索引也不是改SQL而是先搞清楚这张表上现在到底有哪些索引索引建在哪些列上状态是否正常。“Oracle查看指定表的索引”这个动作就是SQL调优和日常巡检里最基础、也最关键的起始动作。很多人觉得查索引不就一条SQL的事吗实际工作中你会发现真正难的不是“查出来”而是“查完之后能不能读懂、能不能判断”。索引信息分散在好几个数据字典视图里单查一个视图往往只能看到一半信息工具里虽然能看但遇到批量巡检、自动化脚本、或者生产环境只能命令行操作时你最终还是得靠SQL。这篇文章我把查索引这件事从原理到实操完整拆开讲包括常用字典表的字段含义、组合索引的列顺序怎么看、UNUSABLE状态怎么识别、以及我踩过的那些坑。适合谁看呢刚接触Oracle的开发、需要做SQL优化的DBA、以及那些“会用PL/SQL Developer点开索引页签但从不知道背后查的是什么表”的朋友们。看完之后你至少能自己写出一条带列信息的完整索引查询SQL并且能判断这个索引状态是否健康。这里先建立一个基本认知Oracle里的索引信息不是存在某一个表里的而是分散在一组以USER_、ALL_、DBA_开头的视图里。查索引名称和属性要查*_INDEXES查索引包含哪些列要查*_IND_COLUMNS查索引分区要查*_IND_PARTITIONS。把这几个视图组合起来才能拼出索引的完整画像。2. 三条实际可跑的查询SQL从系统视图到图形化工具2.1 最常用的组合USER_INDEXES 和 USER_IND_COLUMNS如果你只需要查“当前登录用户自己模式下的表”用USER_开头的数据字典视图就够了不需要额外权限不管是用系统用户还是业务用户都能直接查。基础SQL如下SELECT index_name, index_type, uniqueness, status, tablespace_name, last_analyzed FROM user_indexes WHERE table_name EMPLOYEES;这条SQL只能告诉你索引的“头信息”但你还得知道这个索引到底建在哪些列上这时候就要关联USER_IND_COLUMNSSELECT ic.index_name, ic.column_name, ic.column_position, ic.descend, ix.uniqueness, ix.status FROM user_ind_columns ic LEFT JOIN user_indexes ix ON ic.index_name ix.index_name WHERE ic.table_name EMPLOYEES ORDER BY ic.index_name, ic.column_position;COLUMN_POSITION是列在索引中的位置DESCEND表示降序还是升序。组合索引的顺序非常关键第1列决定了索引能否被这条SQL命中后面列影响排序和过滤效率这一点后面专门讲。注意一个细节USER_INDEXES里也有TABLE_NAME字段但USER_IND_COLUMNS里同样有TABLE_NAME两个表都可以用来过滤。如果只用USER_INDEXES查索引头信息你会看不到具体列如果只用USER_IND_COLUMNS你又看不到状态和唯一性。所以实际使用中我建议直接用第二条关联查询一次把列和属性都带出来。2.2 查别人的表怎么办ALL_ 和 DBA_ 的区别现实项目中你登录的是业务账号但需要查的表可能在另一个 schema 下。这时候USER_视图就查不到了要用ALL_INDEXES和ALL_IND_COLUMNS。ALL_开头代表当前用户“有权限访问”的所有对象包括自己的和别人的。SELECT ix.owner, ix.table_name, ix.index_name, ix.uniqueness, ix.status, ic.column_name, ic.column_position FROM all_indexes ix LEFT JOIN all_ind_columns ic ON ix.owner ic.index_owner AND ix.index_name ic.index_name WHERE ix.table_owner HR AND ix.table_name EMPLOYEES ORDER BY ix.index_name, ic.column_position;如果是DBA账号可以直接用DBA_INDEXES和DBA_IND_COLUMNS它们能查整个库里所有 schema 的索引不需要任何额外授权。这里有个常见的坑ALL_和DBA_视图关联时OWNER字段的对应关系不止一层。ALL_INDEXES里有OWNER索引属主和TABLE_OWNER表属主而ALL_IND_COLUMNS里对应的是INDEX_OWNER和TABLE_OWNER。写关联时最好明确指定ix.owner ic.index_owner而不是只关联索引名否则不同用户下同名索引会串数据。2.3 图形化工具的点法也要会但别依赖PL/SQL Developer 是老牌Oracle客户端查索引的方式很简单在左侧对象树里找到目标表展开后有Indexes节点点击就能看到索引列表双击索引还能看列详情。SQL Developer 类似在表节点下选择Indexes页签。但工具看着方便遇到几百张表要巡检索引状态时就抓瞎了。而且工具底层查的其实就是我刚才写的那些视图只是帮你包装了一下。建议顺序是日常巡检用SQL脚本批量跑单表临时分析时再用工具点点看。生产环境很多只开放了命令行你总不能让DBA帮你点鼠标吧。三种方式对比方式适用场景优势劣势USER_/ALL_/DBA_视图批量巡检、自动化脚本、命令行环境灵活可组合任意字段需要记视图结构PL/SQL Developer单表快速查看可视化零门槛无法批量导出信息不完整SQL Developer同上界面友好可看图形化执行计划依赖客户端批量弱3. 查询结果逐列拆解索引状态、列顺序和隐藏的危险3.1 这些字段的含义和判断标准查出来一堆字段哪些值得关注我按优先级给你列一下。INDEX_TYPE常见值是NORMAL普通B-tree索引、BITMAP位图索引、FUNCTION-BASED NORMAL函数索引、NORMAL/REV反向键索引等。OLTP系统里95%以上是NORMAL看到BITMAP要警惕位图索引在并发DML高的表上会造成锁竞争。UNIQUENESSUNIQUE是唯一索引NONUNIQUE是非唯一索引。主键约束默认会创建一个唯一索引唯一约束也一样。所以一个表上明明没手动建索引但查出来有几个UNIQUE索引不用紧张那是约束自动带出来的。STATUS这个字段最重要。VALID表示索引可用UNUSABLE表示索引已失效DML操作不会再维护它了但查询也不会走它INVALID是分区的某个分区状态异常。线上如果看到UNUSABLE基本意味着这个索引需要重建或者那个分区需要处理。TABLESPACE_NAME索引所在的表空间。如果索引和表放在同一个表空间通常不是大问题但大表大索引建议分开方便管理备份策略。LAST_ANALYZED最近一次收集统计信息的时间。如果这个字段是空的或者时间非常旧说明这张表的统计信息缺失或过期优化器可能选了错误的执行计划这时候不是加索引而是先DBMS_STATS.GATHER_TABLE_STATS。3.2 COLMUN_POSITION组合索引命中规则的核心很多人查索引的时候会忽略列顺序。我见过一个线上事故业务表上建了(A, B)组合索引开发后来加了个需求写SQL时只按B条件过滤结果这条SQL走得是全表扫描因为前导列A不在WHERE条件里。组合索引的匹配原则是“最左前缀”只有查询条件里包含索引最左边的列时优化器才有可能走这个索引。COLUMN_POSITION1的列是前导列COLUMN_POSITION2的列是次导列。你要查看索引不只是看有哪些列更要确认查询条件的列顺序是否和索引列顺序匹配。具体怎么快速判断你可以把查询条件里的列名和索引前几个列对比条件里有没有包含第1列如果包含了第2列也包含第1列那效果更好。如果条件里跳过第1列直接用了第2列索引失效。这条规则对普通B-tree组合索引适用对函数索引、位图索引规则略有不同位图索引不要求最左前缀但OLTP里也不常用。3.3 INVISIBLE 和 UNUSABLE两个容易被混淆的状态Oracle 11g 开始支持不可见索引INVISIBLE含义是优化器默认看不到这个索引不会选择它但DML操作依然维护它。这个功能是用来“临时禁用索引但又不想删”的。而UNUSABLE是另一种状态索引已经完全失效Oracle不会去维护它如果查询想用就必须重建。查索引状态时这两个字段要分开看。STATUS管可用性VISIBILITY管可见性。一张索引可以是 VALID INVISIBLE也可以是 UNUSABLE VISIBLE。查的时候建议把两个字段都带出来SELECT index_name, status, visibility, uniqueness FROM user_indexes WHERE table_name EMPLOYEES;我见过一种典型误操作DBA 用ALTER INDEX xxx INVISIBLE把索引改成不可见想测试效果结果忘了改回来。业务SQL执行计划倒是变了但性能可能变差而排查的人查STATUS看到VALID觉得索引没问题绕了一大圈才发现是INVISIBLE。所以以后查索引把VISIBILITY也带上。4. 如何把结果加工成可用的巡检报告在实际生产维护中我们通常需要定期巡检某张表上的索引情况看是否存在失效索引、重复索引、冗余索引或统计信息过期等问题。这里我直接把平时在用的几个查询脚本示例整理出来你可以根据自己的库调整。4.1 单表完整索引报告SELECT ix.index_name, ix.index_type, ix.uniqueness, ix.status, ix.visibility, ix.tablespace_name, TO_CHAR(ix.last_analyzed, YYYY-MM-DD HH24:MI:SS) AS last_analyzed, LISTAGG(ic.column_name || CASE WHEN ic.descend DESC THEN DESC END, , ) WITHIN GROUP (ORDER BY ic.column_position) AS columns_list FROM user_indexes ix LEFT JOIN user_ind_columns ic ON ix.index_name ic.index_name WHERE ix.table_name EMPLOYEES GROUP BY ix.index_name, ix.index_type, ix.uniqueness, ix.status, ix.visibility, ix.tablespace_name, ix.last_analyzed ORDER BY ix.index_name;这条SQL的关键在于用LISTAGG把同一索引的多列合并成一行一眼就能看出组合索引的列顺序。DESCEND为DESC时表示该列按降序存储默认是ASC。函数索引的列名会显示成类似SYS_NC00004$这样的系统生成列名看起来不太直观需要结合DBMS_METADATA.GET_DDL或者USER_IND_EXPRESSIONS来看具体函数表达式。4.2 查询索引对应的函数表达式如果INDEX_TYPE是FUNCTION-BASED NORMAL上面那条SQL里只能看到一个系统列名看不出到底对哪个字段做了什么函数处理。这时要查USER_IND_EXPRESSIONSSELECT index_name, column_expression FROM user_ind_expressions WHERE table_name EMPLOYEES ORDER BY index_name, column_position;这条SQL对排查“为什么SQL没走索引”很有用。比如你查WHERE UPPER(last_name) SMITH索引必须建在UPPER(last_name)上才能命中普通LAST_NAME索引根本用不上。通过USER_IND_EXPRESSIONS就能看到是否存在函数索引。4.3 分区索引的查询补充如果表是分区表索引可能是分区索引LOCAL或者全局索引GLOBAL。单表索引头信息在USER_INDEXES里有但每个分区的状态要查USER_IND_PARTITIONSSELECT index_name, partition_name, status, tablespace_name, last_analyzed FROM user_ind_partitions WHERE index_name IN ( SELECT index_name FROM user_indexes WHERE table_name EMPLOYEES ) ORDER BY index_name, partition_name;分区索引的某个分区UNUSABLE时如果查询条件匹配到那个分区执行计划会直接走全分区扫描。遇到这种情况需要重建对应分区ALTER INDEX idx_emp_id REBUILD PARTITION p_2024_01;4.4 再加一个索引大小统计做存储规划或者清理无用索引时还需要知道每个索引占多少空间。可以查询USER_SEGMENTSSELECT segment_name, bytes / 1024 / 1024 AS size_mb, blocks FROM user_segments WHERE segment_type LIKE INDEX% AND segment_name IN ( SELECT index_name FROM user_indexes WHERE table_name EMPLOYEES );索引空间和表空间分开列方便评估删除索引能释放多少存储。这个数据对下线冗余索引的决策很有用因为删除一个10GB的索引不只是执行一条DROP INDEX那么简单还要考虑重建成本和时间窗口。5. 使用这些查询定位问题时的实际案例分析5.1 一条慢SQL排查为什么加了索引还是全表扫描某次生产环境遇到一个案例应用半夜跑批有一张流水表每天新增几十万条数据跑批任务耗时持续增长。开发反馈 “我们已经在MERGE_TIME字段上加过索引了怎么还这么慢”。我登录数据库先查了这个表的索引情况SELECT index_name, index_type, uniqueness, status, visibility FROM user_indexes WHERE table_name TRANS_LOG;结果确实有个索引名为IDX_TRANS_LOG_MERGE_TIME状态VALID。接着看列信息SELECT index_name, column_name, column_position FROM user_ind_columns WHERE table_name TRANS_LOG ORDER BY index_name, column_position;这个索引建在MERGE_TIME上表结构也没问题。那为什么执行计划不走索引我抓了一下跑批SQL发现SQL条件写的是WHERE MERGE_TIME SYSDATE - 1 AND STATUS P而且STATUS选择性其实很高90%的数据都是P。问题来了索引列是MERGE_TIME但这个条件的过滤效果不佳因为近一天的数据占全表比例很大优化器认为走索引回表还不如全表扫描快。这种属于“索引建了但不合适”的情况不是查询方法的问题而是索引设计没贴合业务SQL。最后我给出了建议考虑建(STATUS, MERGE_TIME)组合索引或者至少把STATUS作为前导列把选择性高的字段放前面。这个案例说明查看表索引信息时别只看有没有索引还要把索引列和实际WHERE条件对照起来看。5.2 使用索引查询结果识别重复索引这张表的索引列信息查询出来之后我看到两个非常相似的索引IDX_EMP_DEPT DEPT_ID, HIRE_DATE IDX_EMP_DEPT_HIRE DEPT_ID, HIRE_DATE两个索引列顺序完全一致只是名称不同。这种情况通常是因为一个索引是早期开发的另一个是后来DBA做优化时新建的新索引建完忘了删旧索引。重复索引浪费存储还拖慢DML写入效率每次INSERT/UPDATE都要维护两份索引数据。通过列顺序查询可以快速定位这类问题。之后确认IDX_EMP_DEPT_HIRE被SQL实际使用后直接删除了IDX_EMP_DEPT。这里也提醒一点删除索引前一定要做抓取确认看看当前执行计划里到底用的是哪个索引不要盲目删。5.3 索引状态字段在数据库升级或批量维护后的变化有一次做数据库迁移把某个分区表的数据从旧库导入新库之后应用报错说大批SQL性能下降。我查了一下索引状态发现很多分区的状态字段不是USABLE而是UNUSABLE。原因是在数据迁移过程中部分分区是被直接TRUNCATE后重新插入的而TRUNCATE操作会导致该分区的本地索引分区失效。如果不对这些分区做索引重建查询时优化器只能走全分区扫描。处理方式就是前面写的ALTER INDEX ... REBUILD PARTITION。这也是为什么每次表维护结束后都应该跑一遍索引状态检查脚本的原因。把这些查询固化成巡检SQL定时输出结果防患于未然。5.4 看执行计划时的一个补充技巧除了直接查字典视图还需要结合执行计划来判断索引是否真的被某个SQL使用。你可以用以下方式查看执行计划中索引的使用情况SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT ALLSTATS LAST));执行计划里TABLE ACCESS BY INDEX ROWID和INDEX RANGE SCAN等操作说明索引被使用了如果看到TABLE ACCESS FULL而索引状态和列都没问题那就需要从数据量、过滤因子、索引选择性这些方向去继续深挖。按理说这一步不属于纯“查看索引”的范畴但它是索引信息查询之后最自然的下一步。这就是为什么我始终强调光会执行一条SELECT * FROM USER_INDEXES不叫会看索引能把索引视图、列信息、状态信息、执行计划结合在一起来分析才叫真正掌握了“Oracle查看指定表的索引”这个技能。
Oracle查看指定表索引的完整指南：数据字典、SQL查询与调优实战
1. 先搞明白为什么要专门去查一张表的索引做Oracle开发和运维的人迟早都会遇到这么一个场景线上业务突然变慢开发同事甩过来一条SQL说“这条查询跑了十几秒帮我看看怎么优化”。你第一步要做的不是加索引也不是改SQL而是先搞清楚这张表上现在到底有哪些索引索引建在哪些列上状态是否正常。“Oracle查看指定表的索引”这个动作就是SQL调优和日常巡检里最基础、也最关键的起始动作。很多人觉得查索引不就一条SQL的事吗实际工作中你会发现真正难的不是“查出来”而是“查完之后能不能读懂、能不能判断”。索引信息分散在好几个数据字典视图里单查一个视图往往只能看到一半信息工具里虽然能看但遇到批量巡检、自动化脚本、或者生产环境只能命令行操作时你最终还是得靠SQL。这篇文章我把查索引这件事从原理到实操完整拆开讲包括常用字典表的字段含义、组合索引的列顺序怎么看、UNUSABLE状态怎么识别、以及我踩过的那些坑。适合谁看呢刚接触Oracle的开发、需要做SQL优化的DBA、以及那些“会用PL/SQL Developer点开索引页签但从不知道背后查的是什么表”的朋友们。看完之后你至少能自己写出一条带列信息的完整索引查询SQL并且能判断这个索引状态是否健康。这里先建立一个基本认知Oracle里的索引信息不是存在某一个表里的而是分散在一组以USER_、ALL_、DBA_开头的视图里。查索引名称和属性要查*_INDEXES查索引包含哪些列要查*_IND_COLUMNS查索引分区要查*_IND_PARTITIONS。把这几个视图组合起来才能拼出索引的完整画像。2. 三条实际可跑的查询SQL从系统视图到图形化工具2.1 最常用的组合USER_INDEXES 和 USER_IND_COLUMNS如果你只需要查“当前登录用户自己模式下的表”用USER_开头的数据字典视图就够了不需要额外权限不管是用系统用户还是业务用户都能直接查。基础SQL如下SELECT index_name, index_type, uniqueness, status, tablespace_name, last_analyzed FROM user_indexes WHERE table_name EMPLOYEES;这条SQL只能告诉你索引的“头信息”但你还得知道这个索引到底建在哪些列上这时候就要关联USER_IND_COLUMNSSELECT ic.index_name, ic.column_name, ic.column_position, ic.descend, ix.uniqueness, ix.status FROM user_ind_columns ic LEFT JOIN user_indexes ix ON ic.index_name ix.index_name WHERE ic.table_name EMPLOYEES ORDER BY ic.index_name, ic.column_position;COLUMN_POSITION是列在索引中的位置DESCEND表示降序还是升序。组合索引的顺序非常关键第1列决定了索引能否被这条SQL命中后面列影响排序和过滤效率这一点后面专门讲。注意一个细节USER_INDEXES里也有TABLE_NAME字段但USER_IND_COLUMNS里同样有TABLE_NAME两个表都可以用来过滤。如果只用USER_INDEXES查索引头信息你会看不到具体列如果只用USER_IND_COLUMNS你又看不到状态和唯一性。所以实际使用中我建议直接用第二条关联查询一次把列和属性都带出来。2.2 查别人的表怎么办ALL_ 和 DBA_ 的区别现实项目中你登录的是业务账号但需要查的表可能在另一个 schema 下。这时候USER_视图就查不到了要用ALL_INDEXES和ALL_IND_COLUMNS。ALL_开头代表当前用户“有权限访问”的所有对象包括自己的和别人的。SELECT ix.owner, ix.table_name, ix.index_name, ix.uniqueness, ix.status, ic.column_name, ic.column_position FROM all_indexes ix LEFT JOIN all_ind_columns ic ON ix.owner ic.index_owner AND ix.index_name ic.index_name WHERE ix.table_owner HR AND ix.table_name EMPLOYEES ORDER BY ix.index_name, ic.column_position;如果是DBA账号可以直接用DBA_INDEXES和DBA_IND_COLUMNS它们能查整个库里所有 schema 的索引不需要任何额外授权。这里有个常见的坑ALL_和DBA_视图关联时OWNER字段的对应关系不止一层。ALL_INDEXES里有OWNER索引属主和TABLE_OWNER表属主而ALL_IND_COLUMNS里对应的是INDEX_OWNER和TABLE_OWNER。写关联时最好明确指定ix.owner ic.index_owner而不是只关联索引名否则不同用户下同名索引会串数据。2.3 图形化工具的点法也要会但别依赖PL/SQL Developer 是老牌Oracle客户端查索引的方式很简单在左侧对象树里找到目标表展开后有Indexes节点点击就能看到索引列表双击索引还能看列详情。SQL Developer 类似在表节点下选择Indexes页签。但工具看着方便遇到几百张表要巡检索引状态时就抓瞎了。而且工具底层查的其实就是我刚才写的那些视图只是帮你包装了一下。建议顺序是日常巡检用SQL脚本批量跑单表临时分析时再用工具点点看。生产环境很多只开放了命令行你总不能让DBA帮你点鼠标吧。三种方式对比方式适用场景优势劣势USER_/ALL_/DBA_视图批量巡检、自动化脚本、命令行环境灵活可组合任意字段需要记视图结构PL/SQL Developer单表快速查看可视化零门槛无法批量导出信息不完整SQL Developer同上界面友好可看图形化执行计划依赖客户端批量弱3. 查询结果逐列拆解索引状态、列顺序和隐藏的危险3.1 这些字段的含义和判断标准查出来一堆字段哪些值得关注我按优先级给你列一下。INDEX_TYPE常见值是NORMAL普通B-tree索引、BITMAP位图索引、FUNCTION-BASED NORMAL函数索引、NORMAL/REV反向键索引等。OLTP系统里95%以上是NORMAL看到BITMAP要警惕位图索引在并发DML高的表上会造成锁竞争。UNIQUENESSUNIQUE是唯一索引NONUNIQUE是非唯一索引。主键约束默认会创建一个唯一索引唯一约束也一样。所以一个表上明明没手动建索引但查出来有几个UNIQUE索引不用紧张那是约束自动带出来的。STATUS这个字段最重要。VALID表示索引可用UNUSABLE表示索引已失效DML操作不会再维护它了但查询也不会走它INVALID是分区的某个分区状态异常。线上如果看到UNUSABLE基本意味着这个索引需要重建或者那个分区需要处理。TABLESPACE_NAME索引所在的表空间。如果索引和表放在同一个表空间通常不是大问题但大表大索引建议分开方便管理备份策略。LAST_ANALYZED最近一次收集统计信息的时间。如果这个字段是空的或者时间非常旧说明这张表的统计信息缺失或过期优化器可能选了错误的执行计划这时候不是加索引而是先DBMS_STATS.GATHER_TABLE_STATS。3.2 COLMUN_POSITION组合索引命中规则的核心很多人查索引的时候会忽略列顺序。我见过一个线上事故业务表上建了(A, B)组合索引开发后来加了个需求写SQL时只按B条件过滤结果这条SQL走得是全表扫描因为前导列A不在WHERE条件里。组合索引的匹配原则是“最左前缀”只有查询条件里包含索引最左边的列时优化器才有可能走这个索引。COLUMN_POSITION1的列是前导列COLUMN_POSITION2的列是次导列。你要查看索引不只是看有哪些列更要确认查询条件的列顺序是否和索引列顺序匹配。具体怎么快速判断你可以把查询条件里的列名和索引前几个列对比条件里有没有包含第1列如果包含了第2列也包含第1列那效果更好。如果条件里跳过第1列直接用了第2列索引失效。这条规则对普通B-tree组合索引适用对函数索引、位图索引规则略有不同位图索引不要求最左前缀但OLTP里也不常用。3.3 INVISIBLE 和 UNUSABLE两个容易被混淆的状态Oracle 11g 开始支持不可见索引INVISIBLE含义是优化器默认看不到这个索引不会选择它但DML操作依然维护它。这个功能是用来“临时禁用索引但又不想删”的。而UNUSABLE是另一种状态索引已经完全失效Oracle不会去维护它如果查询想用就必须重建。查索引状态时这两个字段要分开看。STATUS管可用性VISIBILITY管可见性。一张索引可以是 VALID INVISIBLE也可以是 UNUSABLE VISIBLE。查的时候建议把两个字段都带出来SELECT index_name, status, visibility, uniqueness FROM user_indexes WHERE table_name EMPLOYEES;我见过一种典型误操作DBA 用ALTER INDEX xxx INVISIBLE把索引改成不可见想测试效果结果忘了改回来。业务SQL执行计划倒是变了但性能可能变差而排查的人查STATUS看到VALID觉得索引没问题绕了一大圈才发现是INVISIBLE。所以以后查索引把VISIBILITY也带上。4. 如何把结果加工成可用的巡检报告在实际生产维护中我们通常需要定期巡检某张表上的索引情况看是否存在失效索引、重复索引、冗余索引或统计信息过期等问题。这里我直接把平时在用的几个查询脚本示例整理出来你可以根据自己的库调整。4.1 单表完整索引报告SELECT ix.index_name, ix.index_type, ix.uniqueness, ix.status, ix.visibility, ix.tablespace_name, TO_CHAR(ix.last_analyzed, YYYY-MM-DD HH24:MI:SS) AS last_analyzed, LISTAGG(ic.column_name || CASE WHEN ic.descend DESC THEN DESC END, , ) WITHIN GROUP (ORDER BY ic.column_position) AS columns_list FROM user_indexes ix LEFT JOIN user_ind_columns ic ON ix.index_name ic.index_name WHERE ix.table_name EMPLOYEES GROUP BY ix.index_name, ix.index_type, ix.uniqueness, ix.status, ix.visibility, ix.tablespace_name, ix.last_analyzed ORDER BY ix.index_name;这条SQL的关键在于用LISTAGG把同一索引的多列合并成一行一眼就能看出组合索引的列顺序。DESCEND为DESC时表示该列按降序存储默认是ASC。函数索引的列名会显示成类似SYS_NC00004$这样的系统生成列名看起来不太直观需要结合DBMS_METADATA.GET_DDL或者USER_IND_EXPRESSIONS来看具体函数表达式。4.2 查询索引对应的函数表达式如果INDEX_TYPE是FUNCTION-BASED NORMAL上面那条SQL里只能看到一个系统列名看不出到底对哪个字段做了什么函数处理。这时要查USER_IND_EXPRESSIONSSELECT index_name, column_expression FROM user_ind_expressions WHERE table_name EMPLOYEES ORDER BY index_name, column_position;这条SQL对排查“为什么SQL没走索引”很有用。比如你查WHERE UPPER(last_name) SMITH索引必须建在UPPER(last_name)上才能命中普通LAST_NAME索引根本用不上。通过USER_IND_EXPRESSIONS就能看到是否存在函数索引。4.3 分区索引的查询补充如果表是分区表索引可能是分区索引LOCAL或者全局索引GLOBAL。单表索引头信息在USER_INDEXES里有但每个分区的状态要查USER_IND_PARTITIONSSELECT index_name, partition_name, status, tablespace_name, last_analyzed FROM user_ind_partitions WHERE index_name IN ( SELECT index_name FROM user_indexes WHERE table_name EMPLOYEES ) ORDER BY index_name, partition_name;分区索引的某个分区UNUSABLE时如果查询条件匹配到那个分区执行计划会直接走全分区扫描。遇到这种情况需要重建对应分区ALTER INDEX idx_emp_id REBUILD PARTITION p_2024_01;4.4 再加一个索引大小统计做存储规划或者清理无用索引时还需要知道每个索引占多少空间。可以查询USER_SEGMENTSSELECT segment_name, bytes / 1024 / 1024 AS size_mb, blocks FROM user_segments WHERE segment_type LIKE INDEX% AND segment_name IN ( SELECT index_name FROM user_indexes WHERE table_name EMPLOYEES );索引空间和表空间分开列方便评估删除索引能释放多少存储。这个数据对下线冗余索引的决策很有用因为删除一个10GB的索引不只是执行一条DROP INDEX那么简单还要考虑重建成本和时间窗口。5. 使用这些查询定位问题时的实际案例分析5.1 一条慢SQL排查为什么加了索引还是全表扫描某次生产环境遇到一个案例应用半夜跑批有一张流水表每天新增几十万条数据跑批任务耗时持续增长。开发反馈 “我们已经在MERGE_TIME字段上加过索引了怎么还这么慢”。我登录数据库先查了这个表的索引情况SELECT index_name, index_type, uniqueness, status, visibility FROM user_indexes WHERE table_name TRANS_LOG;结果确实有个索引名为IDX_TRANS_LOG_MERGE_TIME状态VALID。接着看列信息SELECT index_name, column_name, column_position FROM user_ind_columns WHERE table_name TRANS_LOG ORDER BY index_name, column_position;这个索引建在MERGE_TIME上表结构也没问题。那为什么执行计划不走索引我抓了一下跑批SQL发现SQL条件写的是WHERE MERGE_TIME SYSDATE - 1 AND STATUS P而且STATUS选择性其实很高90%的数据都是P。问题来了索引列是MERGE_TIME但这个条件的过滤效果不佳因为近一天的数据占全表比例很大优化器认为走索引回表还不如全表扫描快。这种属于“索引建了但不合适”的情况不是查询方法的问题而是索引设计没贴合业务SQL。最后我给出了建议考虑建(STATUS, MERGE_TIME)组合索引或者至少把STATUS作为前导列把选择性高的字段放前面。这个案例说明查看表索引信息时别只看有没有索引还要把索引列和实际WHERE条件对照起来看。5.2 使用索引查询结果识别重复索引这张表的索引列信息查询出来之后我看到两个非常相似的索引IDX_EMP_DEPT DEPT_ID, HIRE_DATE IDX_EMP_DEPT_HIRE DEPT_ID, HIRE_DATE两个索引列顺序完全一致只是名称不同。这种情况通常是因为一个索引是早期开发的另一个是后来DBA做优化时新建的新索引建完忘了删旧索引。重复索引浪费存储还拖慢DML写入效率每次INSERT/UPDATE都要维护两份索引数据。通过列顺序查询可以快速定位这类问题。之后确认IDX_EMP_DEPT_HIRE被SQL实际使用后直接删除了IDX_EMP_DEPT。这里也提醒一点删除索引前一定要做抓取确认看看当前执行计划里到底用的是哪个索引不要盲目删。5.3 索引状态字段在数据库升级或批量维护后的变化有一次做数据库迁移把某个分区表的数据从旧库导入新库之后应用报错说大批SQL性能下降。我查了一下索引状态发现很多分区的状态字段不是USABLE而是UNUSABLE。原因是在数据迁移过程中部分分区是被直接TRUNCATE后重新插入的而TRUNCATE操作会导致该分区的本地索引分区失效。如果不对这些分区做索引重建查询时优化器只能走全分区扫描。处理方式就是前面写的ALTER INDEX ... REBUILD PARTITION。这也是为什么每次表维护结束后都应该跑一遍索引状态检查脚本的原因。把这些查询固化成巡检SQL定时输出结果防患于未然。5.4 看执行计划时的一个补充技巧除了直接查字典视图还需要结合执行计划来判断索引是否真的被某个SQL使用。你可以用以下方式查看执行计划中索引的使用情况SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT ALLSTATS LAST));执行计划里TABLE ACCESS BY INDEX ROWID和INDEX RANGE SCAN等操作说明索引被使用了如果看到TABLE ACCESS FULL而索引状态和列都没问题那就需要从数据量、过滤因子、索引选择性这些方向去继续深挖。按理说这一步不属于纯“查看索引”的范畴但它是索引信息查询之后最自然的下一步。这就是为什么我始终强调光会执行一条SELECT * FROM USER_INDEXES不叫会看索引能把索引视图、列信息、状态信息、执行计划结合在一起来分析才叫真正掌握了“Oracle查看指定表的索引”这个技能。