oracle查所有表的索引个数的示例代码

select 
    t.table_name,
    count(i.index_name) as index_count,
    listagg(i.index_name, ', ') within group (order by i.index_name) as index_names
from user_tables t
left join user_indexes i on t.table_name = i.table_name
group by t.table_name
order by count(i.index_name) desc, t.table_name;

select 
    i.table_owner,
    i.table_name,
    count(i.index_name) as index_count,
    listagg(i.index_name, ', ') within group (order by i.index_name) as index_names
from dba_indexes i
where i.table_owner not in ('sys', 'system', 'xdb', 'ctxsys', 'mdsys', 'ordsys')  -- 排除系统用户
group by i.table_owner, i.table_name
order by i.table_owner, count(i.index_name) desc, i.table_name;

select 
    t.owner,
    t.table_name,
    t.num_rows as table_rows,
    count(i.index_name) as total_indexes,
    sum(case when i.uniqueness = 'unique' then 1 else 0 end) as unique_indexes,
    sum(case when i.uniqueness = 'nonunique' then 1 else 0 end) as nonunique_indexes,
    sum(case when i.index_type = 'function-based normal' then 1 else 0 end) as function_based_indexes
from dba_tables t
left join dba_indexes i on t.owner = i.table_owner and t.table_name = i.table_name
where t.owner = 'your_schema_name'  -- 替换为你的模式名
group by t.owner, t.table_name, t.num_rows
order by count(i.index_name) desc, t.table_name;

select 
    i.table_owner,
    i.table_name,
    i.index_type,
    count(*) as count_per_type,
    listagg(i.index_name, ', ') within group (order by i.index_name) as index_list
from dba_indexes i
where i.table_owner = 'your_schema_name'  -- 替换为你的模式名
group by i.table_owner, i.table_name, i.index_type
order by i.table_name, i.index_type;

-- 查找当前用户下没有索引的表
select 
    t.table_name,
    t.num_rows,
    t.blocks
from user_tables t
where not exists (
    select 1 
    from user_indexes i 
    where i.table_name = t.table_name
)
and t.table_name not like 'bin$%'  -- 排除回收站中的表
order by t.num_rows desc nulls last;

-- 查找所有用户下没有索引的表（需要dba权限）
select 
    t.owner,
    t.table_name,
    t.num_rows
from dba_tables t
where not exists (
    select 1 
    from dba_indexes i 
    where i.table_owner = t.owner 
    and i.table_name = t.table_name
)
and t.owner not in ('sys', 'system', 'xdb')
and t.table_name not like 'bin$%'
order by t.owner, t.num_rows desc nulls last;

-- 统计每个索引的列数
select 
    i.table_name,
    i.index_name,
    i.uniqueness,
    i.status,
    count(ic.column_position) as column_count,
    listagg(ic.column_name, ', ') within group (order by ic.column_position) as columns
from user_indexes i
join user_ind_columns ic on i.index_name = ic.index_name
group by i.table_name, i.index_name, i.uniqueness, i.status
order by i.table_name, i.index_name;

-- 索引统计汇总
with index_stats as (
    select 
        owner,
        table_name,
        count(*) as total_indexes,
        round(avg(blevel), 2) as avg_blevel,
        round(avg(leaf_blocks), 2) as avg_leaf_blocks,
        sum(case when status != 'valid' then 1 else 0 end) as invalid_indexes
    from dba_indexes
    where owner = 'your_schema_name'
    group by owner, table_name
)
select 
    owner,
    count(distinct table_name) as tables_with_indexes,
    sum(total_indexes) as total_index_count,
    round(avg(total_indexes), 2) as avg_indexes_per_table,
    round(median(total_indexes), 2) as median_indexes_per_table,
    max(total_indexes) as max_indexes_in_table,
    sum(invalid_indexes) as total_invalid_indexes
from index_stats
group by owner;

-- 查找行数超过10000但索引数少于2个的表
select 
    t.owner,
    t.table_name,
    t.num_rows,
    count(i.index_name) as index_count
from dba_tables t
left join dba_indexes i on t.owner = i.table_owner and t.table_name = i.table_name
where t.num_rows > 10000
and t.owner = 'your_schema_name'
group by t.owner, t.table_name, t.num_rows
having count(i.index_name) < 2
order by t.num_rows desc;

select 
    table_name,
    index_name,
    used
from v$object_usage
where used = 'no'  -- 查看未使用的索引
order by table_name;

select 
    'create index idx_' || table_name || '_' || column_name || 
    ' on ' || table_name || '(' || column_name || ');' as create_index_sql
from (
    select distinct
        t.table_name,
        tc.column_name
    from user_tables t
    join user_tab_columns tc on t.table_name = tc.table_name
    where not exists (
        select 1 
        from user_ind_columns ic 
        where ic.table_name = t.table_name 
        and ic.column_name = tc.column_name
    )
    and t.table_name not like 'bin$%'
    and tc.column_name not like '%id'  -- 排除id列
    and tc.data_type in ('varchar2', 'char', 'number', 'date')  -- 只对某些数据类型创建索引
)
where rownum <= 10;  -- 限制生成的数量