oracle 知识

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.查询外键未建建索引的情况。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

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';