索引常用脚本（三）

1、当前用户下，哪些表的索引个数过多

select table_name ,count(*) from user_indexes  group by table_name  having count(*)>5 order by count(*) 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、哪些表的组合索引列过多

 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;

--针对分区表--无论是建了局部索引还是全局索引，在user_indexes都可以查到，只是status不一样。
 select segment_name,sum(bytes)/1024/1024/1024 "GB",sum(blocks) from user_segments where segment_type='TABLE PARTITINO' and segment_name not in (select table_name from user_indexes) group by segment_name having sum(bytes)/1024/1024/1024>=2 order by GB desc;

5、当前用户下，哪些用户的聚合因子特别差

当表数据的插入顺序与索引的插入顺序相似度很高时，我们称之为聚合因子比较低；差异明显的，我们称之为聚合因子比较高。

select a.index_name,b.num_rows,a.clustering_factor,b.table_name from user_indexes a,user_tables b where a.table_name=b.table_name order by 2,3 desc;（根据row与clustering_factor比较）

6、查询失效索引

-----普通索引

 col index_name for a40;

 col table_name for a50;

 set line 999;

 set pagesize 999;

select index_name,table_name,blevel,num_rows,leaf_blocks,distinct_keys,status from dba_indexes where status='UNUSABLE';

---分区索引（）

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、哪些表中单列索引与组合索引有交叉

column_name代表了被索引列，count（*）代表了这个列被哪几个索引占用。

select table_name, trunc(count(distinct(column_name)) / count(*),2) cross_idx_rate
  from dba_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 dba_indexes where blevel>3;

9、哪些索引的统计信息过旧

---一般索引

select index_name, table_name, last_analyzed, num_rows, temporary, status
  from dba_indexes
 where status <> 'N/A'
   and (last_analyzed is null or last_analyzed < sysdate - 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);

10、哪些外键约束失效了

SELECT TABLE_NAME,
       CONSTRAINT_NAME,
       STATUS,
       CONSTRAINT_TYPE,
       R_CONSTRAINT_NAME
  FROM dba_CONSTRAINTS
 WHERE STATUS='DISABLED';