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

1. 查看当前用户所有表的索引数量

SELECT     t.table_name,    COUNT(i.index_name) as index_count,    LISTAGG(i.index_name, ', ') WITHIN GROUP (ORDER BY i.index_name) as index_namesFROM user_tables tLEFT JOIN user_indexes i ON t.table_name = i.table_nameGROUP BY t.table_nameORDER BY COUNT(i.index_name) DESC, t.table_name;

2. 查看所有用户所有表的索引数量（需要DBA权限）

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_namesFROM dba_indexes iWHERE i.table_owner NOT IN ('SYS', 'SYSTEM', 'XDB', 'CTXSYS', 'MDSYS', 'ORDSYS')  -- 排除系统用户GROUP BY i.table_owner, i.table_nameORDER BY i.table_owner, COUNT(i.index_name) DESC, i.table_name;

3.查询表及其索引的详细信息–推荐使用，oracle国产化转换到tidb，最好明确知道所有需要迁移的生产表的条数等令牌

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_indexesFROM dba_tables tLEFT JOIN dba_indexes i ON t.owner = i.table_owner AND t.table_name = i.table_nameWHERE t.owner = 'YOUR_SCHEMA_NAME'  -- 替换为你的模式名GROUP BY t.owner, t.table_name, t.num_rowsORDER BY COUNT(i.index_name) DESC, t.table_name;

4.按索引类型统计

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_listFROM dba_indexes iWHERE i.table_owner = 'YOUR_SCHEMA_NAME'  -- 替换为你的模式名GROUP BY i.table_owner, i.table_name, i.index_typeORDER BY i.table_name, i.index_type;

5.查询没有索引的表

-- 查找当前用户下没有索引的表SELECT     t.table_name,    t.num_rows,    t.blocksFROM user_tables tWHERE 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_rowsFROM dba_tables tWHERE 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;

6.索引列数统计

-- 统计每个索引的列数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 columnsFROM user_indexes iJOIN user_ind_columns ic ON i.index_name = ic.index_nameGROUP BY i.table_name, i.index_name, i.uniqueness, i.statusORDER BY i.table_name, i.index_name;

7.实用的汇总查询

-- 索引统计汇总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_indexesFROM index_statsGROUP BY owner;

8.生产监控大表无索引情况

-- 查找行数超过10000但索引数少于2个的表SELECT     t.owner,    t.table_name,    t.num_rows,    COUNT(i.index_name) as index_countFROM dba_tables tLEFT JOIN dba_indexes i ON t.owner = i.table_owner AND t.table_name = i.table_nameWHERE t.num_rows > 10000AND t.owner = 'YOUR_SCHEMA_NAME'GROUP BY t.owner, t.table_name, t.num_rowsHAVING COUNT(i.index_name) < 2ORDER BY t.num_rows DESC;

9.查看索引使用情况（需要Oracle 11g及以上）

SELECT     table_name,    index_name,    usedFROM v$object_usageWHERE used = 'NO'  -- 查看未使用的索引ORDER BY table_name;

10.生成创建索引的脚本

SELECT     'CREATE INDEX idx_' || table_name || '_' || column_name ||     ' ON ' || table_name || '(' || column_name || ');' as create_index_sqlFROM (    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;  -- 限制生成的数量