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;