做了这么多年Oracle数据库运维被同事问得最频繁的问题之一就是“怎么看某张表的索引”乍一听好像很简单用PL/SQL Developer选中表名展开目录树索引分类下面列了一堆名字。但要真讲清楚“这张表到底有哪些索引、索引建在哪些列上、列的顺序是什么、索引当前是否失效、以及SQL执行时到底有没有踩中这个索引”光靠图形工具那点信息差了十万八千里。“Oracle查看指定表的索引”这件事往小了说是一条SQL的事往大了说能牵扯出索引失效、统计信息过期、执行计划偏差、甚至整个数据库性能治理的链条。这篇内容我不打算只丢一句select * from user_indexes where table_nameXXX就收工而是从最基础的视图查询讲起结合我实际处理过的案例把索引查看的完整思路、常用SQL模板、GUI工具的局限性、以及日常巡检时的个人经验全部翻出来适合刚接触Oracle的开发也适合想补全索引运维体系的DBA。1. 为什么总有人问“怎么看Oracle指定表的索引”1.1 场景比语法更重要你查索引到底想解决什么问题我复盘过很多次被问索引查询的场景发现大家问出这句话时背后往往藏着一个更具体的诉求。最常见的场景是SQL变慢了开发同事怀疑缺索引但又不确定表上是否已经存在可能被利用的索引于是先查一下。这类人需要的不仅是索引列表还需要索引列顺序、索引类型、是否唯一这些信息用来对照SQL的where条件和join条件。另一个常见场景在DBA这边某张历史表一直在膨胀或者准备归档数据需要评估表上的索引是否还有保留价值。这时候光看索引存在与否不够你还得知道索引占了多少空间、最近有没有被使用过、以及如果删掉它会不会影响统计信息或约束。还有一类场景是数据库迁移或表结构重构。把一张表从一个库搬到另一个库时索引信息必须一并梳理清楚特别是函数索引、位图索引这些容易漏掉的特殊类型。只看名字很难判断索引的本质必须结合索引类型和表达式信息去甄别。这三种场景对应了三个层次的查询需求第一层知道表上有哪些索引第二层知道每个索引的字段构成和状态第三层知道索引是否可用、是否被使用、是否值得保留。后面所有内容都围绕这三个层次展开。1.2 查索引背后真正考验的是数据字典的熟悉程度Oracle里索引的元数据放在数据字典里最常用的几个视图是user_indexes、user_ind_columns、all_indexes、dba_indexes另外还有user_ind_expressions、user_constraints、v$object_usage这些辅助视图。很多人查索引只会用其中一个视图结果要么查漏了函数索引要么看不清组合索引的列顺序要么忽略了索引和约束之间的绑定关系。其实查索引这件事并不难难的是形成一套完整的查询习惯。比如我看到一张表第一反应不是直接敲SQL而是先确定这张表的owner是谁。Oracle里表名允许在不同schema下重复如果你直接where table_nameORDER_INFO而不带owner条件查出来的可能是别人家schema下的同名表这种坑我见得不少。再比如索引状态user_indexes.status字段有三个常见值VALID、UNUSABLE、IN_PROGRESS。前两个容易理解IN_PROGRESS是重建过程中出现的临时状态。如果你查出的索引状态是UNUSABLE那这索引对执行计划来说基本等于不存在这才是查询索引时必须第一时间抓住的关键信息。2. 三大核心视图与标准SQL从入门到能干活2.1 user_indexes / all_indexes / dba_indexes到底该查哪个很多教程一上来就让你查dba_indexes好像权限不要钱一样。实际上这三个视图的使用场景差异很大。user_indexes只返回当前登录用户自己schema下拥有的索引。如果你用scott登录它只显示scott名下的索引逻辑最简单也是最不容易出错的起点。all_indexes范围更大一些返回你当前用户有权限访问的所有schema的索引只要别人给你授权了表的访问权限你就能看到对应的索引信息。dba_indexes则是数据库全局视图能看到所有schema的所有索引但需要DBA角色或相应的系统权限。实际工作中我的习惯是查看当前用户自己的表用user_indexes需要排查别人schema下的表或者做跨schema分析时用all_indexes并显式指定table_owner只有做全库级的索引巡检时才会用dba_indexes。把三个视图的权限边界搞清楚能在很大程度上避免权限报错也能避免因为查错范围而得出错误结论。我整理了一张对照表方便你快速选择视图名可见范围典型使用场景权限要求user_indexes当前用户自己的索引开发自查、应用排障无特殊要求all_indexes当前用户有权限访问的索引跨schema排查、联调支持需要表访问权限dba_indexes全库所有索引DBA巡检、全局治理需要DBA角色或SELECT ANY DICTIONARY2.2 先认识user_indexes和user_ind_columns的关键字段查看索引的SQL想写得顺手必须先熟悉两个核心视图的字段。user_indexes这张视图里我认为最关键的字段有这几个index_name索引名称数据库里同一schema下索引名不能重复。index_type索引类型常见的包括NORMAL普通B树索引、BITMAP位图索引、FUNCTION-BASED NORMAL函数索引、CLUSTER聚簇索引、IOT - TOP索引组织表相关等。table_name索引所在表的名称。table_owner表的所有者。uniqueness取值UNIQUE或NONUNIQUE表明该索引是否唯一索引。status索引状态VALID可用UNUSABLE不可用。tablespace_name索引所在表空间判断空间分配时有用。last_analyzed最近一次收集统计信息的时间排查统计信息过期问题时会用到。user_ind_columns则是用来查看索引列信息的核心视图字段比较简单index_name索引名称。table_name表名。column_name被索引的列名。column_position列在索引中的位置。组合索引中这个字段非常重要它直接决定索引的匹配规则。比如idx (a,b,c)a列的位置是1b是2c是3查询时如果只带b列条件通常用不上这个索引这就是最左侧前缀原则的基础。2.3 查询索引的SQL模板直接抄作业的版本先给一个最常用、也最能满足日常需求的标准SQL。下面的脚本查询指定表的所有索引并关联出每个索引包含的列以及列的位置顺序SELECT a.index_name, a.index_type, a.uniqueness, a.status, LISTAGG(b.column_name, , ) WITHIN GROUP (ORDER BY b.column_position) AS columns FROM user_indexes a LEFT JOIN user_ind_columns b ON a.index_name b.index_name WHERE a.table_name ORDER_INFO GROUP BY a.index_name, a.index_type, a.uniqueness, a.status ORDER BY a.index_name;这条SQL把索引基本信息和列信息合并成一行查看的时候非常直观。假设ORDER_INFO表上有三个索引运行结果大致是INDEX_NAME INDEX_TYPE UNIQUENESS STATUS COLUMNS IDX_ORDER_USER NORMAL NONUNIQUE VALID USER_ID,ORDER_STATUS IDX_ORDER_EMAIL FUNCTION-BASED NORMAL NONUNIQUE VALID LOWER(USER_EMAIL) PK_ORDER_ID NORMAL UNIQUE VALID ORDER_ID注意看第二行函数索引在user_ind_columns里查不到普通列名需要到user_ind_expressions视图里看具体的函数表达式。如果你发现某个索引在user_ind_columns对应不上列十有八九是函数索引。查询方式如下SELECT index_name, column_expression FROM user_ind_expressions WHERE table_name ORDER_INFO;如果是跨schema查看别人的表只需要把user_indexes和user_ind_columns分别改成all_indexes和all_ind_columns并在where后面增加a.table_owner 目标用户名条件即可。这里有个易错点all_ind_columns这个视图名不带user_前缀很多人习惯性写成all_user_ind_columns结果报ORA-00942表或视图不存在代码没问题却在拼写上栽跟头。2.4 索引和主键、唯一约束的绑定关系查索引时经常被忽略的一个问题是主键和唯一约束会自动创建索引。这意味着你用drop index去删除一个由约束生成的索引时大概率会报错ORA-02429“无法删除用于强制唯一/主键的索引”。我自己就遇到过类似的场面开发同学想清理冗余索引直接从索引列表里看中了PK_ORDER_ID执行drop index后收到报错跑来问是不是数据库出了问题。其实处理办法不是删索引而是先禁用或删除对应的约束ALTER TABLE order_info DROP CONSTRAINT pk_order_id;约束删除后对应的索引通常会被自动删除。所以查看指定表的索引时我建议你同时关注约束信息。下面这条SQL可以快速定位表上的主键约束和唯一约束以及它们关联的索引名SELECT constraint_name, constraint_type, index_name, status FROM user_constraints WHERE table_name ORDER_INFO AND constraint_type IN (P, U);查出来之后你会理清一条逻辑链主键约束通过某个唯一索引来强制这个索引不能单独删除必须通过约束操作来处理。只有把索引和约束的关系放在一起看才算真正掌握了这张表的索引全貌。3. 完整实操从建表到索引状态全解读3.1 准备一张订单表并创建各类索引空谈理论没有意义这里我拿一张简化版的订单表来做完整演示。这张表的场景和很多业务系统的订单表类似包含订单ID、用户ID、商品ID、订单金额、订单状态、用户邮箱、下单时间等字段。先建表CREATE TABLE order_info ( order_id NUMBER(16) PRIMARY KEY, user_id NUMBER(12) NOT NULL, product_id NUMBER(12), order_amount NUMBER(10,2), order_status VARCHAR2(10), user_email VARCHAR2(100), create_time DATE DEFAULT SYSDATE );这个语句里直接用PRIMARY KEY创建了主键约束Oracle会自动生成名为PK_ORDER_ID的唯一索引。接着插入一批测试数据用来模拟真实场景INSERT INTO order_info SELECT rownum, MOD(rownum, 1000) 1, MOD(rownum, 500) 1, ROUND(DBMS_RANDOM.VALUE(10, 5000), 2), DECODE(MOD(rownum, 3), 0, COMPLETED, 1, PENDING, CANCELLED), user || (MOD(rownum, 1000) 1) || example.com, SYSDATE - MOD(rownum, 30) FROM dual CONNECT BY LEVEL 100000; COMMIT;数据量不大十万行但足够演示索引查看的各种细节。再补几条不同类型索引覆盖日常和进阶场景CREATE INDEX idx_order_user_status ON order_info(user_id, order_status); CREATE INDEX idx_order_email_func ON order_info(LOWER(user_email)); CREATE UNIQUE INDEX uk_order_product ON order_info(order_id, product_id);这里我建了普通组合索引idx_order_user_status函数索引idx_order_email_func以及一个唯一索引uk_order_product。刻意加入函数索引是为了演示如何查看表达式索引唯一索引则是为了演示uniqueness字段的差异。3.2 用SQL把这张表的索引底裤翻出来现在运行前面给过的标准SQLSELECT a.index_name, a.index_type, a.uniqueness, a.status, LISTAGG(b.column_name, , ) WITHIN GROUP (ORDER BY b.column_position) AS columns FROM user_indexes a LEFT JOIN user_ind_columns b ON a.index_name b.index_name WHERE a.table_name ORDER_INFO GROUP BY a.index_name, a.index_type, a.uniqueness, a.status ORDER BY a.index_name;执行结果中你会同时看到四个索引主键索引、普通组合索引、唯一索引、函数索引。重点观察两点。第一IDX_ORDER_EMAIL_FUNC的index_type字段是FUNCTION-BASED NORMAL并且它的columns字段是空值因为user_ind_columns里没有普通列信息。你需要去user_ind_expressions里查它的列表达式确认索引到底建在哪个函数上。第二IDX_ORDER_USER_STATUS的columns字段显示USER_ID,ORDER_STATUS这个顺序来自column_position排序。如果组合索引顺序反了比如写成ORDER_STATUS,USER_ID同一个SQL的优化空间完全不同。再补充一条查看索引列详细顺序的SQL适合排查组合索引的列位置SELECT index_name, column_position, column_name FROM user_ind_columns WHERE table_name ORDER_INFO ORDER BY index_name, column_position;显示结果很清晰地列出每个索引包含哪些列、分别在哪个位置。组合索引的列顺序是SQL优化的核心信息我通常会把这张小表截图发给开发同事比口头解释半小时都管用。3.3 在PL/SQL Developer里的对照操作我知道肯定有人习惯用PL/SQL Developer的图形界面。操作路径很简单左侧Tables目录下找到ORDER_INFO表双击打开表定义面板切换到Indexes标签页就能看到索引列表和部分属性。但这个界面的局限性很明显。它显示的信息止步于索引名、索引类型、唯一性、表空间这些基本内容不展示组合索引的列顺序也看不出索引的表达式内容更不会告诉你索引状态是VALID还是UNUSABLE。所以我的建议是图形界面适合快速瞄一眼表有没有索引一旦需要判断索引可用性、函数索引定义、组合索引列顺序马上切回SQL查询。两者结合最快但核心判断依据一定以视图查询结果为准。3.4 顺便对比一下MySQL的索引查询习惯网上关于MySQL索引的提问非常多如果你同时维护Oracle和MySQL两套数据库会发现两者的索引查看方式差异很大。MySQL查看指定表索引通常用一条SHOW INDEX FROM语句SHOW INDEX FROM order_info;它返回结果的字段和Oracle有对应关系Key_name对应index_nameSeq_in_index对应column_positionNon_unique取值为0表示唯一索引对应Oracle的uniquenessUNIQUEColumn_name对应column_name。需要注意的核心差异是Oracle的组合索引列上限和命名规则与MySQL不完全一致跨库迁移时如果只搬索引名和表结构很容易忽略函数索引的表达式差异。更关键的是Oracle的索引状态有UNUSABLE概念MySQL一般不会出现类似的逻辑失效状态。这意味着同一套索引维护经验不能直接平移跨库时要重新审视状态字段。4. 索引失效与巡检实战踩过的坑和常用SQL4.1 一个真实案例ORA-01502索引失效有次一个业务系统的报表查询突然报错错误码是ORA-01502“索引或这类索引的分区处于不可用状态”。开发同事很着急把错误信息发过来问怎么回事。我先让他执行下面的查询SELECT index_name, status, tablespace_name FROM user_indexes WHERE table_name REPORT_DETAIL ORDER BY index_name;查询结果里有个索引的status是UNUSABLE问题一目了然。这个索引失效的直接原因是当时批量清理分区数据时有人对分区做了TRUNCATE操作某些情况下会导致本地索引或全局索引状态异常。解决办法是重建索引ALTER INDEX idx_report_detail_create_time REBUILD ONLINE;ONLINE关键字表示在重建过程中允许表上的DML操作继续进行不至于锁定所有业务请求。如果是大表上的索引重建前最好先评估一下表空间剩余空间重建过程中索引段会临时占用额外空间空间不足会导致重建失败。这个案例想表达的是索引失效在Oracle里是真实存在的运维问题而日常查看索引状态恰恰是发现问题最快的手段。不要等业务报错了才去查索引定期的状态巡检能把这类风险降到最低。4.2 怎么知道索引到底有没有被使用比“看到索引失效”更深一层的需求是怎么知道一个索引有没有真正被SQL用到。尤其是表上的冗余索引占了空间但从来不进执行计划删掉又能节省不少存储。Oracle提供了一个比较轻量的监控方式就是v$object_usage视图。它需要你先手动开启某个索引的监控然后过一段时间再来查看统计结果。开启监控ALTER INDEX idx_order_user_status MONITORING USAGE;等到业务运行一段时间后查看监控结果SELECT * FROM v$object_usage;这个视图会返回表名、索引名、是否被使用USED字段、监控开始时间等信息。如果USED是NO说明在监控周期里这个索引没有被任何SQL使用可以考虑删除或进一步分析。看完之后记得关闭监控ALTER INDEX idx_order_user_status NOMONITORING USAGE;我把这个监控方式用在了很多次索引治理项目里。操作方法不复杂难点在于监控周期要覆盖业务高峰期否则监控结果没有代表性。比如你只监控了一个业务低谷时段某些白天常用的索引在该时段内没有访问但你不能据此判断它冗余。4.3 索引健康体检三件套做数据库日常巡检时我习惯用五个字概括索引体检关注点失效、超占、过期。对应的就是三个常见问题索引不可用、索引段占用空间异常、索引统计信息过期。第一件套查全库无效索引。下面的SQL能快速列出所有status不为VALID的索引SELECT owner, table_name, index_name, status FROM dba_indexes WHERE status ! VALID ORDER BY owner, table_name;DBA权限普通账号不一定有如果你只有当前库的访问权限把dba_indexes换成all_indexes即可。第二件套查占用空间最大的索引。大索引往往意味着高存储成本和维护成本SELECT segment_name, ROUND(bytes / 1024 / 1024, 2) AS size_mb FROM user_segments WHERE segment_type INDEX ORDER BY bytes DESC FETCH FIRST 20 ROWS ONLY;第三件套查统计信息过期或缺失的索引。统计信息过期会影响优化器对索引成本的判断SELECT index_name, table_name, last_analyzed FROM user_indexes ORDER BY last_analyzed NULLS FIRST;如果last_analyzed是空或者日期明显早于最近一次大批量数据变更时间就该重新收集一下统计信息EXEC DBMS_STATS.GATHER_INDEX_STATS(USER, IDX_ORDER_USER_STATUS);这三个体检SQL我建议直接存成脚本每月跑一次输出结果放到巡检报告里。它们的价值不在于查出多少问题而在于让索引状态透明化避免线上系统在最关键的时候给你来一次“惊喜”。5. 高频问题速查与我的个人经验5.1 查索引时最常踩的坑我把这几年工作中被高频问到的问题整理成了一张速查表先说现象再给解决方案。问题现象根因分析解决办法查询索引结果为空但表上明显有索引表名大小写或owner不对确认表owner使用UPPER函数统一表名如WHERE table_name UPPER(order_info)索引查出来了但看不到组合索引的列用了user_ind_columns但未关联条件错误确认index_name唯一性组合索引按column_position排序显示函数索引列信息为空函数索引在user_ind_columns无对应记录使用user_ind_expressions查表达式删除索引报ORA-02429索引由主键或唯一约束自动生成先禁用或删除对应约束再处理索引索引状态不是VALID分区操作或重建中断导致失效执行ALTER INDEX ... REBUILD ONLINE重建查询的索引信息是隔壁schema的多schema下存在同名表查询时显式带上table_owner条件以上每个问题我在实际工作里都遇到过尤其是表名大小写和同名不同schema这类低级别错误经常在忙乱时给人头一棒。5.2 我个人的几个实操习惯踩过足够多的坑之后我养成了几个查索引、管索引的习惯谈不上标准但确实让工作省心很多。第一个习惯是命名规范前置。建索引时统一用idx_前缀加表名缩写加字段名缩写的格式比如idx_order_user_status一眼就能看出索引建在哪个表的哪些列上。唯一索引用uk_前缀主键索引由约束自动命名不用手动干预。规范的好处是查索引时不用费劲猜看名字就能建立初步判断。第二个习惯是组合索引字段顺序先问业务需求再拍板。(user_id, order_status)和(order_status, user_id)看着差异不大实际查询效果天差地别。我的习惯是优先把等值条件的字段放前面把范围条件的字段放后面再结合实际SQL的执行计划微调。别拿到字段就建索引先看看最频繁的查询长什么样。第三个习惯是定期巡检而不是等到出问题再排查。每个月初我会跑一遍索引状态、索引空间、统计信息这三类SQL输出结果归档。如果发现某个索引连续三个月USED都是NO我会主动和业务方沟通是否删除。这种主动式的索引治理比被动救火舒服得多。第四个习惯其实最不起眼但特别实用每次查索引都带上table_owner条件。哪怕是查当前用户的表我也会加一行a.table_owner USER不为别的就是为了防止哪天脚本复用的时候因为漏掉owner条件而查错表。这四个习惯组合起来基本能保证在“查看指定表索引”这个动作上不出现方向性错误也为后续的维护和治理打好了基础。