首页 > 代码库 > oracle 知识

oracle 知识

2024-07-11 03:32:01   221人阅读

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) columnsfrom (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_cntfrom (select substr(table_name, 1, 30) table_name,substr(constraint_name, 1, 30) constraint_name,substr(column_name, 1, 30) column_name,positionfrom user_cons_columns) a,user_constraints bwhere a.constraint_name = b.constraint_nameand b.constraint_type = ‘R‘group by b.table_name, b.constraint_name) conswhere col_cnt > ALL(select count(*)from user_ind_columns iwhere i.table_name = cons.table_nameand i.column_name in (cname1, cname2, cname3, cname4, cname5,cname6, cname7, cname8)and i.column_position <= cons.col_cntgroup by i.index_name);

3.查询哪些组合索引组合列超过4个的

select table_name, index_name, count(*)  from user_ind_columns group by table_name, index_namehaving 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 twhere 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 t2where 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_namehaving count(distinct(column_name)) / count(*) < 1order 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 t2where 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‘;

 


联系
我们