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;

示例结果如下：

TABLE_NAME	TABLESPACE_NAME	NUM_ROWS	AVG_ROW_LEN	ESTIMATED_DATA_SIZE_MB	ACTUAL_TABLE_SIZE_MB	INDEX_NAME	INDEX_SIZE_MB	UNIQUENESS	TOTAL_INDEX_SIZE_MB	INDEX_TO_TABLE_RATIO_PERCENT
T_USER_INFO	TBS_PICP_NEW	636046	37	262.55	271	PK_T_USER_ID	45	UNIQUE	371	37.09
T_USER_INFO	TBS_PICP_NEW	636046	37	262.55	271	IDX_USER_NAME	28	NONUNIQUE	71	37.09

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。