1.查询索引数量
select table_name, count(*) cnt
from user_indexes where index_type=‘NORMAL‘
group by table_name
having count(*) >= 1
order by cnt desc ;
2.查询外键未建建索引的情况。
select table_name, constraint_name, cname1 || nvl2(cname2, ‘,‘ || cname2, null) || nvl2(cname3, ‘,‘ || cname3, null) || nvl2(cname4, ‘,‘ || cname4, null) || nvl2(cname5, ‘,‘ || cname5, null) || nvl2(cname6, ‘,‘ || cname6, null) || nvl2(cname7, ‘,‘ || cname7, null) || nvl2(cname8, ‘,‘ || cname8, null) columns from (select b.table_name, b.constraint_name, max(decode(position, 1, column_name, null)) cname1, max(decode(position, 2, column_name, null)) cname2, max(decode(position, 3, column_name, null)) cname3, max(decode(position, 4, column_name, null)) cname4, max(decode(position, 5, column_name, null)) cname5, max(decode(position, 6, column_name, null)) cname6, max(decode(position, 7, column_name, null)) cname7, max(decode(position, 8, column_name, null)) cname8, count(*) col_cnt from (select substr(table_name, 1, 30) table_name, substr(constraint_name, 1, 30) constraint_name, substr(column_name, 1, 30) column_name, position from user_cons_columns) a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = ‘R‘ group by b.table_name, b.constraint_name) cons where col_cnt > ALL (select count(*) from user_ind_columns i where i.table_name = cons.table_name and i.column_name in (cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8) and i.column_position <= cons.col_cnt group by i.index_name);
3.查询哪些组合索引组合列超过4个的
select table_name, index_name, count(*) from user_ind_columns group by table_name, index_name having count(*) >= 4 order by count(*) desc;
4.查询大表创建索引
select segment_name, bytes/1024/1024/1024 "GB", blocks, tablespace_name from user_segments where segment_type = ‘TABLE‘ and segment_name not in (select table_name from user_indexes) and bytes / 1024 / 1024 / 1024 >= 2 order by GB desc;
5.查询失效的索引
select t.index_name, t.table_name, blevel, t.num_rows, t.leaf_blocks, t.distinct_keys from user_indexes t where status = ‘UNUSABLE‘ ;
6.查询分区失效索引
select t1.blevel, t1.leaf_blocks, t1.INDEX_NAME, t2.table_name, t1.PARTITION_NAME, t1.STATUS from user_ind_partitions t1, user_indexes t2 where t1.index_name = t2.index_name and t1.STATUS = ‘UNUSABLE‘;
7.查询单列索引和组合索引存在交叉的情况。
单列索引和组合索引使用了相同的字段。
select table_name, trunc(count(distinct(column_name)) / count(*),2) cross_idx_rate from user_ind_columns group by table_name having count(distinct(column_name)) / count(*) < 1 order by cross_idx_rate desc;
8.查询索引高度较高的索引。
select table_name, index_name, blevel, leaf_blocks, num_rows, last_analyzed, degree, status from user_indexes where blevel>=1;
9.普通索引（从未收集过统计信息或者是最近10天内未收集过统计信息的表）
select index_name, table_name, last_analyzed, num_rows, temporary, status from user_indexes where status <> ‘N/A‘ and (last_analyzed is null or last_analyzed < sysdate - 10);
10.分区索引（从未收集过统计信息或者是最近10天内未收集过统计信息的分区）
select t2.table_name, t1.INDEX_NAME, t1.PARTITION_NAME, t1.last_analyzed, t1.blevel, t1.leaf_blocks, t1.STATUS from user_ind_partitions t1, user_indexes t2 where t1.index_name = t2.index_name and (t1.last_analyzed is null or t1.last_analyzed < sysdate - 10);
11.外键约束失效。
SELECT TABLE_NAME, CONSTRAINT_NAME, STATUS, CONSTRAINT_TYPE, R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE STATUS=‘DISABLED‘;
原文：http://www.cnblogs.com/yg_zhang/p/3830575.html