我在数据库运维里最常被问到的问题之一就是“怎么查看Oracle某张表的索引”。这个问题看起来简单但实际工作中新人容易在几个视图之间绕晕甚至查出来的结果和真实索引对不上。今天我把这块内容完整梳理一遍从数据字典原理到可以直接抄走的SQL脚本再到索引失效、不可见索引这些维护场景一次讲透。这篇东西适合三类人看刚接手Oracle维护的DBA写SQL时想确认执行计划为什么没走索引的开发以及做数据库巡检需要批量导出索引信息的运维同学。内容不依赖任何图形化工具全部基于SQL*Plus或任意SQL客户端可执行的语句只要你连得上库就能跑。1. 为什么要盯着一张表的索引查索引是Oracle性能调优的第一道门。我们可以不看执行计划的细节但必须知道目标表上到底有哪些索引、索引建立在哪些列上、索引当前是什么状态。很多问题的排查路径最后都会收敛到“先看看这张表的索引”。先说最典型的场景某张订单表数据量从百万涨到千万级原本秒回的查询突然变成全表扫描。此时第一件事不是去改SQL而是先确认这张表的索引结构。如果发现查询条件列根本没有索引那问题就出在表结构设计上如果索引存在但状态是UNUSABLE那就是索引失效导致优化器不敢用如果索引存在且状态正常才轮到统计信息、SQL改写这些后续手段。顺序搞反了排查效率会差很多。另一个高频场景是评估索引变更的影响范围。比如你想删除一张表上的某个索引或者把普通索引改成函数索引操作之前必须搞清楚这个索引出现在哪些约束里、覆盖了哪些列组合、还有没有其他对象依赖它。盲删索引导致业务SQL性能崩塌的例子我在生产环境见过太多次了。还有一个容易忽略的问题——重复索引。Oracle不会主动拦截你创建功能完全重复的索引同一组列或同一组列的子集可能因为不同开发在不同阶段各自建索引而出现冗余。冗余索引不仅浪费存储每次DML还要多维护一份索引数据。查全表的索引清单是发现冗余索引最直接的手段。基于这些原因我把“查索引”这个动作拆成三层来看第一层是索引本身的定义信息包括索引名、类型、所属表、列顺序第二层是索引的物理状态和统计信息包括是否VALID、是否INVISIBLE、行数、聚簇因子第三层是索引和约束的关联关系特别是主键、唯一约束背后对应的索引。把这三层查清楚一张表的索引情况基本就完全透明了。2. 核心数据字典视图索引信息的藏身之处Oracle把索引相关的元数据放在数据字典里最常用的是三组视图USER_、ALL_、DBA_。前缀不同能看到的范围不同。用户只能看到自己schema下的对象ALL能看到当前用户有权限访问的对象DBA能看到整个库的所有对象。日常维护我建议直接用ALL_或DBA_前缀避免因为权限边界导致查询结果不完整产生误判。第一个必须掌握的视图是USER_INDEXES。它每一行代表一个索引核心字段包括INDEX_NAME索引名、TABLE_NAME所属表名、UNIQUENESS是否唯一索引、STATUS索引状态、TABLESPACE_NAME索引所在表空间、INDEX_TYPE索引类型如NORMAL、FUNCTION-BASED NORMAL、BITMAP、VISIBILITY可见性VISIBLE或INVISIBLE、LAST_ANALYZED最近分析时间。第二个关键视图是USER_IND_COLUMNS记录索引与列的对应关系。注意一个多列复合索引在这个视图里会显示成多行每行代表参与索引的一个列COLUMN_POSITION字段表示该列在索引中的位置这个顺序非常关键。比如索引建立在(col1, col2)和索引建立在(col2, col1)完全不是一回事查询条件只命中col1时前者能用上索引后者大概率走全表扫描。第三个是USER_IND_EXPRESSIONS函数索引的表达式存放在这里。如果你看到某个索引的INDEX_TYPE是FUNCTION-BASED NORMAL那么必须查这个视图才能看到索引到底建立在什么函数表达式上。比如SUBSTR(USER_ID,1,4)这种前缀匹配索引只查USER_IND_COLUMNS是看不到完整定义的。第四个是USER_IND_STATISTICS存储索引的统计信息。核心字段有BLEVELB树层数、LEAF_BLOCKS叶子块数、DISTINCT_KEYS去重键值数、CLUSTERING_FACTOR聚簇因子、SAMPLE_SIZE、LAST_ANALYZED。BLEVEL和CLUSTERING_FACTOR这两个字段尤其重要BLEVEL决定了索引扫一次要经过多少层B树CLUSTERING_FACTOR接近表行数时说明索引列的顺序和表存储顺序差异很大回表开销会显著增加。另外约束相关的索引要关联USER_CONSTRAINTS视图。主键和唯一约束通常会附带创建同名索引删除这类索引会报ORA-02429错误因为Oracle不允许直接drop约束对应的索引。查询时可以通过JOIN USER_CONSTRAINTS把索引和约束关联起来确认哪些索引是“受约束保护”的。为了方便理解我把常用视图的核心职责列出来做对比视图核心作用关键字段USER_INDEXES索引主信息INDEX_NAME, TABLE_NAME, UNIQUENESS, STATUS, VISIBILITYUSER_IND_COLUMNS索引与列关联COLUMN_NAME, COLUMN_POSITION, DESCENDUSER_IND_EXPRESSIONS函数索引表达式COLUMN_EXPRESSION, COLUMN_POSITIONUSER_IND_STATISTICS索引统计信息BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTORUSER_CONSTRAINTS约束关联CONSTRAINT_TYPE, STATUS, INDEX_NAME3. 实操脚本一张表索引直接看全理论说完了上实战。先给一个最常用的基础查询单表索引清单直接可以跑SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS, STATUS, VISIBILITY, TABLESPACE_NAME FROM DBA_INDEXES WHERE OWNER APPS AND TABLE_NAME ORDER_HEADERS ORDER BY INDEX_NAME;这个查询返回的结果能快速告诉你这张表有多少个索引是普通索引还是函数索引唯一还是不唯一当前是否可用是否不可见。但有个不足——它没法在一行里看到索引到底建在哪些列上。于是我把USER_INDEXES和USER_IND_COLUMNS关联起来用LISTAGG函数把多列合并这样每一个索引只显示一行列信息一目了然SELECT IDX.INDEX_NAME, IDX.INDEX_TYPE, IDX.UNIQUENESS, IDX.STATUS, IDX.VISIBILITY, ( SELECT LISTAGG(COL.COLUMN_NAME, ,) WITHIN GROUP (ORDER BY COL.COLUMN_POSITION) FROM DBA_IND_COLUMNS COL WHERE COL.INDEX_OWNER IDX.OWNER AND COL.INDEX_NAME IDX.INDEX_NAME ) AS INDEX_COLUMNS FROM DBA_INDEXES IDX WHERE IDX.OWNER APPS AND IDX.TABLE_NAME ORDER_HEADERS ORDER BY IDX.INDEX_NAME;这个脚本是我工作中用频率最高的查询没有之一。它把索引名、类型、唯一性、状态、可见性、索引列一次性展现出来。比如某表有索引IX_ORDER_HEADERS_STATUSINDEX_COLUMNS列显示STATUS,CREATED_DATE说明这是一个建立在两列上的复合索引STATUS是第一个列前导列查询条件如果只带CREATED_DATE而没带STATUS这个索引大概率不会被使用。加粗提一下前导列是复合索引的灵魂看索引列顺序比看索引名重要得多。如果表上有函数索引上面这个查询里INDEX_COLUMNS会显示为空因为列信息不在DBA_IND_COLUMNS。此时补充一个函数索引查询SELECT INDEX_NAME, COLUMN_POSITION, COLUMN_EXPRESSION FROM DBA_IND_EXPRESSIONS WHERE INDEX_OWNER APPS AND INDEX_NAME IN ( SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER APPS AND TABLE_NAME ORDER_HEADERS AND INDEX_TYPE LIKE FUNCTION-BASED% ) ORDER BY INDEX_NAME, COLUMN_POSITION;接下来是关联统计信息和约束的综合查询。这个脚本适合巡检时用一次性把索引的物理信息、列信息、约束类型全查出来SELECT IDX.OWNER, IDX.TABLE_NAME, IDX.INDEX_NAME, IDX.UNIQUENESS, IDX.STATUS, IDX.VISIBILITY, IDX.BLEVEL, IDX.LEAF_BLOCKS, IDX.DISTINCT_KEYS, IDX.CLUSTERING_FACTOR, CON.CONSTRAINT_TYPE FROM DBA_INDEXES IDX LEFT JOIN DBA_CONSTRAINTS CON ON CON.OWNER IDX.OWNER AND CON.INDEX_NAME IDX.INDEX_NAME WHERE IDX.OWNER APPS AND IDX.TABLE_NAME ORDER_HEADERS ORDER BY IDX.INDEX_NAME;CONSTRAINT_TYPE字段的取值含义P表示主键约束U表示唯一约束R表示外键约束。如果一个索引关联到的CONSTRAINT_TYPE是P那么这个索引对应主键删除它之前必须先处理掉主键约束。CLUSTERING_FACTOR字段如果特别大接近表的行数说明索引列顺序和表物理存储顺序差异很大走索引回表的开销会高这时候适合做一次索引重建或表重组来优化但注意表重组在生产环境要大窗口谨慎操作。最后提一个大家常用的图形化工具场景。很多DBA用PL/SQL Developer或Navicat鼠标点开表的Indexes页签就能看到索引列表。图形的本质其实也是在查数据字典只是工具帮你拼好了SQL。但图形工具在批量处理时效率很低比如我要导出100张表的索引信息手点要疯掉脚本一把跑完存成CSV才靠谱。所以工具可以看但SQL必须会写。4. 索引状态检查与常见坑位查索引信息之后最需要关注的就是状态。Oracle索引状态主要有以下几种VALID表示正常UNUSABLE表示失效已经不能用于查询优化必须重建或者删掉重建IN_PROGRESS表示在线重建过程中属于临时状态对于分区索引还可能出现UNUSABLE局部子分区的情况。另外VISIBILITY为INVISIBLE的索引是一种特殊存在它是创建给特定会话或特定业务临时用的默认不参与优化器的索引选择但会持续维护。这里讲一个我在生产环境踩过的坑某天早上业务报查询变慢查了USER_INDEXES发现目标索引STATUS为VALID但执行计划就是不走索引。排查了很久最后发现这个索引的VISIBILITY是INVISIBLE。原因是有开发为了特定报表创建的索引退出时忘了改回VISIBLE从那天起所有查询都不再使用这个索引。所以查索引时STATUS和VISIBILITY两个字段必须同时看任何一项异常都会影响优化器选择。索引失效最常见的原因是表空间问题或大批量DML中断。比如某张表的索引表空间满了批量update操作失败可能导致索引状态变成UNUSABLE。另外在分区表上执行TRUNCATE PARTITION或EXCHANGE PARTITION操作如果没有带UPDATE GLOBAL INDEXES子句全局索引会变成UNUSABLE。这类问题在检查索引状态时很容易暴露。处理UNUSABLE索引的标准操作是重建。有两种方式ALTER INDEX 索引名 REBUILD或者ALTER INDEX 索引名 REBUILD ONLINE。OFFLINE重建速度快但会阻塞DML适合停机窗口ONLINE重建允许DML并发适合7x24环境但会产生额外日志。如果索引是分区索引的某个分区损坏可以只重建指定分区ALTER INDEX IDX_ORDER_HEADERS_CREATED REBUILD; ALTER INDEX IDX_ORDER_HEADERS_CREATED REBUILD ONLINE; ALTER INDEX IDX_ORDER_HEADERS_CREATED REBUILD PARTITION P2024;索引维护还有一个容易被忽略的动作——定期分析统计信息。索引建好后如果长期不收集统计信息优化器得到的是过时的数据可能低估或高估索引成本。推荐的频率是当表的数据量变化超过10%时重新收集该表和索引的统计信息EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME APPS, TABNAME ORDER_HEADERS, CASCADE TRUE);CASCADE参数为TRUE表示连同索引统计信息一起收集。这个操作相对轻量对于千万级以下的表一条语句几十秒内能完成。如果表特别大亿级以上可以考虑只对目标索引做统计信息收集EXEC DBMS_STATS.GATHER_INDEX_STATS(OWNNAME APPS, INDNAME IDX_ORDER_HEADERS_CREATED);用索引监控功能也可以快速定位无用索引。开启索引监控后Oracle会记录该索引是否被某个SQL使用过查询v$OBJECT_USAGE即可看到结果ALTER INDEX APPS.IDX_ORDER_HEADERS_STATUS MONITORING USAGE; SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME IDX_ORDER_HEADERS_STATUS;监控一段时间后如果USED字段一直是NO这个索引基本可以判定为死索引考虑和业务确认后drop掉。注意开启监控本身也有微小开销建议只对怀疑无用的索引开启监控周期一到两周比较合理。5. 常见问题与排查技巧实录索引查询过程中有一些高频问题我把它整理成速查表每一条都是实际工作里遇到过的问题现象可能原因处理办法查DBA_INDEXES没数据用户权限不足只有USER层权限用ALL_INDEXES代替DBA层视图或申请只读角色索引状态为UNUSABLE表空间异常或大批量DML中断重建索引或对应分区索引索引状态VALID但不生效INVISIBLE索引或统计信息过期改回VISIBLE收集统计信息复合索引列顺序查不到列信息在DBA_IND_COLUMNS需关联查询用LISTAGG按COLUMN_POSITION聚合函数索引看不到表达式表达式在DBA_IND_EXPRESSIONS单独查询表达式视图DROP索引报ORA-02429主键或唯一约束依赖该索引先禁用或删除约束再处理索引索引占用空间过大叶子块碎片化多或索引冗余重建索引考虑压缩或删除冗余索引除了这些表格里的问题我再分享几个排查过程中培养出来的习惯。先说关联查询时“视图前缀不一致”的问题。很多新人喜欢USER_INDEXES和DBA_IND_COLUMNS混着用结果关联出来是空集。原因是USER_和DBA_前缀对应的OWNER字段不同USER_INDEXES根本没有OWNER字段它默认就是当前用户DBA_IND_COLUMNS有INDEX_OWNER。我建议统一使用DBA_或ALL_前缀带上OWNER条件这样可以避免踩这个坑。第二个经验是关于索引类型判断。查询INDEX_TYPE字段如果显示NORMAL这是普通B树索引BITMAP是位图索引适合低基数列但OLTP系统要慎用FUNCTION-BASED NORMAL是函数索引IOT - TOP是IOT表主键索引DOMAIN是应用域索引很少见。搞清楚INDEX_TYPE能帮助你判断这个索引的设计意图。比如一张流水表出现BITMAP索引优化器大概率不会用需要和开发确认设计是否有问题。第三个经验是关于主键索引和普通索引重复的问题。我曾经处理过一个表上面有主键约束PK_TAB_ID又有人建了一个普通索引IX_TAB_ID两者都建立在同一个ID列上。这就是典型的冗余索引DML需要维护两份索引但其中一个完全多余。遇到这种情况先确认普通索引是否被其他对象引用确认没有依赖后直接DROP冗余索引写入性能能立竿见影地提升。排查索引问题时我还习惯配套查看连接信息。有时候不是索引本身的问题而是SQL写法让索引无法生效。比如在索引列上做函数运算WHERE TO_CHAR(CREATED_DATE,YYYY-MM-DD)2024-01-01索引就失效了。此时不一定要改SQL可以改造成函数索引。函数索引在报表型应用中非常实用但要注意函数索引对INSERT和UPDATE性能有一定影响因为每行都要计算函数值需要根据写入频率权衡。最后多提一句索引不是越多越好。每次DML操作都要同步维护所有索引索引数量过多写入会明显变慢反过来索引太少查询又扛不住。我给自己定的检查频率是每月跑一次全库索引清单关注三点一是STATUS不是VALID的索引数量二是IS_VISIBLE为NO的索引数量三是DISTINCT_KEYS除以表行数比例极低的索引区分度差这三类基本上都是风险点或优化点。我在实际工作中经常用到的习惯是在巡检脚本里把这几个查询拼装成一个整体对目标表一键输出索引名、索引列、状态、约束类型、统计信息全量结果。每次排查性能问题先跑这个脚本看30秒比直接看执行计划更高效——因为你连这个表有没有可用索引都不知道看执行计划容易误判。 最后分享一个小技巧。如果一张表名特别长输入起来容易出错可以用下面的查询模糊匹配表名 sql SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME LIKE UPPER(%ORDER%) AND OWNERAPPS;先确认表的准确拼写再去查索引能少走很多弯路。索引查询这件事看起来只是几条SELECT语句但它背后牵涉到数据字典结构、索引物理存储、优化器选择逻辑值得每个Oracle从业者反复琢磨。