Oracle学习笔记关于索引的扩展语句（十五）

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle学习笔记关于索引的扩展语句（十五），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7092字，纯文字阅读大概需要11分钟。

内容图文

哪些表的索引个数过多：

prompt <p>当前用户下,哪些表的索引个数字超过5个的 

select table_name, count(*) cnt
  from user_indexes
 group by table_name
having count(*) >= 5
order by cnt desc ;

　哪些表的外键未建索引：

　

prompt <p>将外键未建索引的情况列出 
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);

　　哪些表组合索引列过多

prompt <p>当前用户下，哪些组合索引组合列超过4个的
select table_name, index_name, count(*)
  from user_ind_columns
 group table_name, index_name
having count(*) >= 4
 order by count(*) desc;

　　哪些大表未建任何索引

--针对普通表（大于2GB的表未建任何索引）

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;
   
   
--针对分区表（大于2GB的分区表未建任何索引）
--无论是建了局部索引还是全局索引，在user_indexes都可以查到，只是status不一样。
select segment_name, sum(bytes)/1024/1024/1024 "GB", sum(blocks)
  from user_segments 
 where segment_type = 'TABLE PARTITION'
   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;
   
--注：无论是建了局部索引还是全局索引，在user_indexes都可以查到，只是status不一样。   

　　哪些聚索引列合因子差

prompt <p>当前用户下，哪些索引的聚合因子特别大。
select a.table_name,
       a.index_name,
       a.blevel,
       a.leaf_blocks,
       b.num_rows,
       b.blocks,
       a.clustering_factor,
       trunc(a.clustering_factor / b.num_rows,2) cluster_rate
  from user_indexes a, user_tables b
 where a.table_name = b.table_name
     and a.clustering_factor is not null
     and a.clustering_factor / b.num_rows>0.9
 order by cluster_rate desc  ;

　　哪些类型的索引已失效

prompt <p>失效-普通索引 
select t.index_name,
       t.table_name,
       blevel,
       t.num_rows,
       t.leaf_blocks,
       t.distinct_keys
  from user_indexes t
where status = 'UNUSABLE' ;


prompt <p>失效-分区索引
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';

　　哪些索引单列组合有叉

prompt <p>当前用户下，哪些表的组合索引与单列索引存在交叉的情况。
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;

---------例子

drop table t purge;

drop table t1 purge;
create table t1 as select * from dba_objects where object_id is not null;
create index idx_t1_objid_owner on t1(object_id ,owner);
create index idx_t1_object_id   on t1(object_id );


drop table t2 purge;
create table t2 as select * from dba_objects where object_id is not null;
create index idx_t2_objid_owner on t2(object_id,owner);
create index idx_t2_object_id   on t2(object_id);
create index idx_t2_owner   on t2(owner);


drop table t3 purge;
create table t3 as select * from dba_objects where object_id is not null;
create index idx_t3_objid_owner on t3(object_id,owner);
create index idx_t3_owner_objid on t3(owner,object_id);
create index idx_t3_object_id   on t3(object_id);
create index idx_t3_owner       on t3(owner);


---执行如下语句，发现当前用户下，T3,T2,T1表存在索引单列组合有交叉，最严重的是T3
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 ;

TABLE_NAME                     CROSS_IDX_RATE
------------------------------ --------------
T3                                        .33
T2                                         .5
T1                                        .66

　　哪些索引的高度比较高

prompt <p>当前用户下，哪些索引的高度比较高，大于5层（LEVEL=4)

select table_name,
       index_name,
       blevel,
       leaf_blocks,
       num_rows,
       last_analyzed,
       degree,
       status
  from user_indexes
  where  blevel>=4;

　　哪些索引建后从未使用

prompt <p>当前用户下，哪些索引最近30天内从未被使用过。

set linesize 166
col INDEX_NAME for a10
col TABLE_NAME for a10
col MONITORING for a10
col USED for a10
col START_MONITORING for a25
col END_MONITORING for a25
--以下判断在最近30天内未被使用过的索引有哪些
select *
  from v$object_usage
 where USED = 'NO'
   and START_MONITORING <= sysdate - 30
   and END_MONITORING is not null;

--注，之前需有对索引进行监控，如
alter index idx_t_id monitoring usage;
---如果取消监控就是
alter index idx_t_id nomonitoring usage;

　　哪些索引设置并行属性

prompt <p>当前用户下，哪些索引被设置了并行。

select table_name,
       index_name,
       blevel,
       leaf_blocks,
       num_rows,
       last_analyzed,
       degree,
       status
  from user_indexes
  where  degree>1;

　　哪些索引统计信息太旧

--普通索引（从未收集过统计信息或者是最近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天内未收集过统计信息的分区）

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

　哪些主外键约束失效了　

prompt <p>当前用户下，哪些外键的约束失效了。

SELECT TABLE_NAME,
       CONSTRAINT_NAME,
       STATUS,
       CONSTRAINT_TYPE,
       R_CONSTRAINT_NAME
  FROM USER_CONSTRAINTS
 WHERE STATUS='DISABLED';
--试验

drop table t_p cascade constraints purge;
drop table t_c cascade constraints purge;

CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
ALTER TABLE T_P ADD CONSTRAINT  T_P_ID_PK  PRIMARY KEY (ID);
CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));

ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);
set autotrace off
INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;
INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME  FROM ALL_OBJECTS;
COMMIT;
ALTER TABLE T_C DISABLE CONSTRAINT FK_T_C;

CREATE INDEX IND_T_C_FID ON T_C (FID);

　　

内容总结

以上是互联网集市为您收集整理的Oracle学习笔记关于索引的扩展语句（十五）全部内容，希望文章能够帮你解决Oracle学习笔记关于索引的扩展语句（十五）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---