目录
一、索引基础概念
索引类型与适用场景 索引的优缺点
二、索引查询方法
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;
示例结果如下：
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
总结以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。
您可能感兴趣的文章:Oracle查询表结构建表语句索引等方式oracle中如何查询所有用户表的表名、主键名称、索引及外键等Oracle表索引查看常见的方法总结Oracle如何查询表索引和索引字段Oracle如何通过执行计划查看查询语句是否使用索引ORACLE检查找出损坏索引(Corrupt Indexes)的方法详解Oracle中检查外键是否有索引的SQL脚本分享oracle 索引的相关介绍（创建、简介、技巧、怎样查看） .
Oracle中检查是否需要重构索引的sql oracle查所有表的索引个数的示例代码