Oracle数据库索引查询方式实用指南

作者：袖梨 2026-09-08

平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“Oracle数据库索引查询方式”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

一、索引基础概念
- 索引类型与适用场景
- 索引的优缺点
二、索引查询方法
- 1. 查看索引元信息
  - 表的所有索引
  - 索引的列信息
  - 索引所在的表信息分析
- 2. 分析索引采用情况
  - 监控索引采用频率
  - 检查未采用索引
  - 索引碎片与空间效率
- 3. 执行计划分析
三、优化索引空间的策略
- 1. 新建索引
- 2. 重建碎片化索引
- 3. 调整存储参数
- 4. 删除冗余索引
- 5. 启用高级压缩（仅限企业版）
四、关键监控指标
五、查询实践案例
- 1. 查询 Oracle 表空间大小
- 2. 查询 Oracle 索引采用情况
总结

一、索引基础概念

索引类型与适用场景

B树索引：最常用，适合高基数列（唯一值多）的等值或范围查询。
位图索引：适用来低基数列（如性别、状态），常用来数据仓库。
函数索引：基于列的函数表达式新建（如UPPER(name)），优化带函数的查询。
复合索引：多列组合，列顺序至关重要（高选择性列在前）。
反向索引：优化模糊查询（如LIKE ‘%abc’）。

索引的优缺点

优点：加速数据检索，减少磁盘I/O。
缺点：占用存储空间；降低DML操作（增删改）效率；需定期维护。

二、索引查询方法

1. 查看索引元信息

表的所有索引

SELECT index_name, index_type, uniqueness 
FROM dba_indexes 
WHERE table_name = 'EMPLOYEES';

索引的列信息

SELECT column_name, column_position 
FROM dba_ind_columns 
WHERE index_name = 'IDX_DEPT_FIRSTNAME';

索引所在的表信息分析

SELECT i.index_name, i.table_name, ic.column_name, ic.column_position
FROM dba_indexes i
JOIN dba_ind_columns ic ON i.index_name = ic.index_name
WHERE i.index_name = 'IDX_NAME'; --按索引名称条件查询

2. 分析索引采用情况

监控索引采用频率

SELECT * FROM v$index_usage;  -- 跟踪索引是否被有效利用,需 12c 以上版本管理员权限

检查未采用索引

SELECT index_name FROM dba_indexes 
WHERE index_name NOT IN (SELECT name FROM v$index_usage);

索引碎片与空间效率

-- 1.查询当前用户创建的索引碎片率
SELECT index_name,
       blevel,
       leaf_blocks,
       clustering_factor,
       ROUND((leaf_blocks * 100) / NULLIF(clustering_factor, 0), 2) AS fragmentation_ratio
FROM (
    SELECT di.index_name,
           di.blevel,
           di.leaf_blocks,
           di.clustering_factor
    FROM dba_indexes di
    JOIN dba_tables dt ON di.table_name = dt.table_name
    WHERE dt.owner = USER -- 只查询当前用户创建的表
      AND di.clustering_factor > 1
) t
WHERE (leaf_blocks * 100) / clustering_factor > 30; -- >30%表示需重建

-- 2.查询 索引所在的表信息分析
SELECT i.index_name, i.table_name, ic.column_name, ic.column_position
FROM dba_indexes i
JOIN dba_ind_columns ic ON i.index_name = ic.index_name
WHERE i.index_name = 'IDX_NAME'; --按索引名称条件查询

--3.重建碎片化索引
ALTER INDEX IDX_NAME REBUILD ONLINE;  -- IDX_NAME  为索引名称

3. 执行计划分析

EXPLAIN PLAN FOR 
SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

关键指标：

INDEX RANGE SCAN：索引有效采用。
FULL TABLE SCAN：可能缺失或未采用索引。

三、优化索引空间的策略

1. 新建索引

CREATE UNIQUE INDEX idx_user_name ON user_info(user_name) 
TABLESPACE idx_tbs 
COMPRESS NOLOGGING;

2. 重建碎片化索引

ALTER INDEX IDX_OLD REBUILD ONLINE;  -- 减少空间碎片，提升查询效率

3. 调整存储参数

ALTER INDEX IDX_LARGE PCTFREE 10;  -- 降低空闲空间预留，压缩索引体积

4. 删除冗余索引

DROP INDEX IDX_REDUNDANT;  -- 通过监控确认使用率低的索引

5. 启用高级压缩（仅限企业版）

ALTER INDEX IDX_BIG COMPRESS ADVANCED LOW;  -- 节省30-50%空间

四、关键监控指标

指标	查看方式	优化阈值
索引大小	dba_segments.bytes	>表空间的20%需优化
碎片率	(leaf_blocks / clustering_factor) * 100	>30%需重建
采用频率	v$index_usage.user_reads	近30天无读操作可删
分区均匀性	dba_index_partitions.bytes 的方差值	方差>50%需调整分区

五、查询实践案例

1. 查询 Oracle 表空间大小

-- 表空间使用率监控（含自动扩展状态）
SELECT 
    df.tablespace_name "Tablespace",
    df.total_mb,
    df.total_mb - fs.free_mb "Used_MB",
    fs.free_mb "Free_MB",
    ROUND((df.total_mb - fs.free_mb) / df.total_mb * 100, 2) Pct_Used, -- 使用率
    autoext "AutoExt"
FROM 
    (SELECT tablespace_name, 
            SUM(bytes)/1024/1024 total_mb,
            MAX(DECODE(autoextensible,'YES','Y','N')) autoext
     FROM dba_data_files 
     GROUP BY tablespace_name) df
JOIN 
    (SELECT tablespace_name, 
            SUM(bytes)/1024/1024 free_mb 
     FROM dba_free_space 
     GROUP BY tablespace_name) fs 
    ON df.tablespace_name = fs.tablespace_name
WHERE ROUND((df.total_mb - fs.free_mb) / df.total_mb * 100, 2) > 80  -- 仅显示>80%使用率的表空间
ORDER BY Pct_Used DESC;

结果示例：

Tablespace	total_mb	Used_MB	Free_MB	Pct_Used	AutoExt
TBS_PICP	3548	3309	239	93	.26
TBS_PICP_NEW	4048	3523	525	87	.03

2. 查询 Oracle 索引采用情况

替换 PICP_FORMAL（表用户）和 T_USER_INFO（表名称需大写）

-- 替换 PICP_FORMAL 和 T_USER_INFO（需大写）
WITH table_info AS (
    SELECT 
        t.owner,
        t.table_name,
        t.tablespace_name,
        t.num_rows,
        t.avg_row_len,
        ROUND((t.num_rows * t.avg_row_len) / 1024 / 1024, 2) AS estimated_data_size_mb,
        ROUND(SUM(s.bytes) / 1024 / 1024, 2) AS actual_table_size_mb
    FROM dba_tables t
    JOIN dba_segments s ON t.owner = s.owner AND t.table_name = s.segment_name
    WHERE t.owner = 'PICP_FORMAL'
      AND t.table_name = 'T_USER_INFO'
      AND s.segment_type = 'TABLE'
    GROUP BY t.owner, t.table_name, t.tablespace_name, t.num_rows, t.avg_row_len
),
index_info AS (
    SELECT 
        i.index_name,
        ROUND(s.bytes / 1024 / 1024, 2) AS index_size_mb,
        i.uniqueness
    FROM dba_indexes i
    JOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_name
    WHERE i.table_owner = 'PICP_FORMAL'
      AND i.table_name = 'T_USER_INFO'
      AND s.segment_type = 'INDEX'
)
SELECT 
    -- 表基本信息
    ti.table_name,
    ti.tablespace_name,
    ti.num_rows,
    ti.avg_row_len,
    ti.estimated_data_size_mb,
    ti.actual_table_size_mb,
    
    -- 索引详细信息
    ii.index_name,
    ii.index_size_mb,
    ii.uniqueness,
    
    -- 索引汇总信息
    ROUND(SUM(ii.index_size_mb) OVER (), 2) AS total_index_size_mb,
    ROUND((SUM(ii.index_size_mb) OVER () / ti.actual_table_size_mb) * 100, 2) AS index_to_table_ratio_percent
FROM table_info ti
LEFT JOIN index_info ii ON 1=1
ORDER BY ii.index_size_mb DESC NULLS LAST;