如何从多张表中获取INDEX_STATS查询B+树索引高度、叶块数等参数

INDEX_STATS是Oracle提供的存储单索引结构统计数据的动态视图，每次仅保留最后一次执行索引分析后的结果，要批量获取多表的索引指标可以按以下步骤操作：

单索引查询（仅查单个索引时使用） #

先执行索引结构验证命令，把索引数据写入INDEX_STATS：
ANALYZE INDEX [你的索引名] VALIDATE STRUCTURE;

执行完成后查询INDEX_STATS即可获取对应指标：

SELECT
  name 索引名称,
  height B树高度,
  lf_blks 叶块数量,
  btree_space 索引占用总空间,
  distinct_keys 去重键数量,
  rows_per_key 平均每个键对应行数
FROM INDEX_STATS;

批量查询Emp、Salary、Ranking三张表的所有索引指标 #

因为INDEX_STATS每次只会存一个索引的统计结果，所以我们需要用PL/SQL脚本批量分析所有目标索引，把结果存入自定义临时表统一查询：

步骤1：创建临时表存储统计结果 #

CREATE GLOBAL TEMPORARY TABLE TMP_IDX_STATS (
  TABLE_NAME VARCHAR2(128),
  INDEX_NAME VARCHAR2(128),
  BTREE_HEIGHT NUMBER,
  LEAF_BLOCKS NUMBER,
  INDEX_SPACE NUMBER,
  DISTINCT_KEYS NUMBER
) ON COMMIT PRESERVE ROWS;

步骤2：执行批量分析脚本 #

注意替换脚本开头的SCHEMA_NAME为你的表所属的用户名：

DECLARE
  V_SCHEMA VARCHAR2(128) := 'SCHEMA_NAME'; -- 替换为实际schema名
BEGIN
  -- 遍历三张目标表的所有索引
  FOR IDX_REC IN (
    SELECT TABLE_NAME, INDEX_NAME
    FROM ALL_INDEXES
    WHERE OWNER = V_SCHEMA
      AND TABLE_NAME IN ('EMP','SALARY','RANKING') -- 表名默认大写，自定义小写表名请调整此处
  ) LOOP
    -- 分析当前索引结构
    EXECUTE IMMEDIATE 'ANALYZE INDEX '||V_SCHEMA||'.'||IDX_REC.INDEX_NAME||' VALIDATE STRUCTURE';
    -- 写入统计结果到临时表
    INSERT INTO TMP_IDX_STATS
    SELECT IDX_REC.TABLE_NAME, NAME, HEIGHT, LF_BLKS, BTREE_SPACE, DISTINCT_KEYS
    FROM INDEX_STATS;
  END LOOP;
  COMMIT;
END;
/

步骤3：查询所有统计结果 #

SELECT * FROM TMP_IDX_STATS;

注意事项 #

• 锁影响：执行ANALYZE INDEX ... VALIDATE STRUCTURE时会对表加共享锁，阻塞业务DML操作，建议在业务低峰期执行
• 大小写适配：Oracle默认将未加双引号创建的表名、索引名转为大写存储，如果你创建对象时使用了双引号指定小写名称，需要调整查询条件中的表名字母大小写
• 轻量替代方案：如果不需要精准的实时结构数据，也可以直接查ALL_INDEXES视图的统计字段，不需要手动分析索引，对业务无影响：

  SELECT 
    TABLE_NAME,
    INDEX_NAME,
    BLEVEL + 1 BTREE_HEIGHT, -- BLEVEL是B树分支层数，加1就是总高度
    LEAF_BLOCKS 叶块数量
  FROM ALL_INDEXES
  WHERE OWNER = '你的SCHEMA名'
    AND TABLE_NAME IN ('EMP','SALARY','RANKING');
  

  该方案的数据准确性取决于最近一次统计信息收集的时间，如果需要最新数据可以先执行EXEC DBMS_STATS.GATHER_TABLE_STATS('你的SCHEMA名','表名');更新统计信息。

内容的提问来源于stack exchange，提问作者Kayfi