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

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'; --按索引名称条件查询

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  为索引名称

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

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

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

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

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

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

-- 表空间使用率监控（含自动扩展状态）
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;

-- 替换 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;