ITPub博客

首页 > 数据库 > Oracle > Oracle 定期检查意义不大的索引

Oracle 定期检查意义不大的索引

原创 Oracle 作者:maohaiqing0304 时间:2015-01-08 15:02:22 0 删除 编辑
 

标题: Oracle 定期检查意义不大的索引

作者:lōττéry©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]



注释:
 您数据库中某用户下是否有一些这样的索引
 1、索引字段对应数据去重后就极少个不同值;
 2、初步设计加的索引,后续并没有用到;
 等情况,这样的索引对insert update delete都有影响;
 那我们怎么找到这样的索引和做清理呢?



  --SQl如下:    /*定期检查ORACLE 意义不大的索引(也就是:索引的DISTINCT_KEYS??表总数极小)*/
  select i.table_name "表名",
       tcc.comments "表注释",
       i.index_name "索引名",
       to_char(wm_concat(ucc.column_name)) "字段名",
       to_char(wm_concat(ucc.comments)) "字段注释",
       max (distinct_keys) "distinct_keys", --(为0:字段值都为空;为1:字段值仅有一个值)
       max (t.num_rows) "表总量"
       --,t.last_analyzed "last_analyzed"
       --,round(i.distinct_keys / t.num_rows, 5) 比例
       --,i.index_type "索引类型"
  from user_indexes      i,
       user_tables       t,
       user_ind_columns  ic,
       user_tab_comments tcc,
       user_col_comments ucc
  where t.table_name = i.table_name
   and ucc.table_name = t.table_name
   and ic.index_name = i.index_name
   and tcc.table_name = t.table_name
   and ucc.column_name = ic.column_name
   and t.num_rows > 500 --过滤掉小表
   and nvl (t.num_rows, 0 ) != 0 --空:没统计,0:没数据
   and (i.distinct_keys /t.num_rows <0.001--distinct_keys索引去重值/t.num_rows表总数极小证明索引意义不大
       or distinct_keys <= 5 )
  group by i.table_name, tcc.comments, i.index_name;
    


  

   --用到的视图          视图注释            用到的字段                     注释
     --USER_INDEXES       索引信息             DISTINCT_KEYS                字段去重值
     --USER_TABLES        表对象信息           NUM_ROWS                     表最后一次统计总行数
     --USER_IND_COLUMNS   索引对应的表列信息   COLUMN_NAME                  索引字段
     --USER_TAB_COMMENTS  表注释               COMMENTS                     表注释 
     --USER_COL_COMMENTS  字段注释             COMMENTS                     字段注释
     --其实可以稍微完善下(表名字/注释若多行只显示一行,其他都显示空,索引是组合索引 只显示一行合并后的结果;)

 
    提示:
    通过如上sql 查到distinct_key<5,该索引就该确认是否有意义了[具体多大范围自己定]
    sql结果发给开发确认后,就算开发人员说没问题了可以删索引了,也存在个别功能真的有用到啥的[如:hint等];
    所以在删除索引前 最好做一个一段时间监控索引的使用情况;
    参考博客:Oracle 以月为单位检查索引的使用情况(邮件反馈)   /*参考博客中保存的表 确定好了,再放心的清理..*/ 
    博客主要的意思就是定期将用到的索引insert到一个普通表,月底做下统计。确认真的没有用过 就可以删了..

    【源于本人笔记】 若有书写错误,表达错误,请指正...


此条目发表在 SQL、SQL优化篇 分类目录。将固定连接加入收藏夹。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28602568/viewspace-1394700/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
擅长SQL编写及SQL优化,分析瓶颈,性能调优、故障处理,根据实际情况定制备份策略; 擅长编写脚本来实现自动化功能,600+SQL优化经验案例,为人热爱学习,喜欢钻研技术,对工作认真负责。

注册时间:2013-03-13

  • 博文量
    121
  • 访问量
    2295379