平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“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%空间

四、关键监控指标

五、 查询实践案例

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;

结果示例：

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;

示例结果如下所示：

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多兼容脚本之家。

您可能感兴趣的文章:

• Oracle查询表结构建表语句索引等方式
• oracle中如何查询所有用户表的表名、主键名称、索引及外键等
• Oracle表索引查看常用的方法总结
• Oracle如何查询表索引和索引字段
• Oracle如何借助执行计划查看查询语句是否采用索引
• ORACLE检查找出损坏索引(Corrupt Indexes)的方法详解
• Oracle中检查外键是否有索引的SQL脚本分享
• oracle 索引的相关介绍（新建、简介、技巧、怎样查看） .
• Oracle中检查是否需重构索引的sql
• oracle查所有表的索引个数的示例