The following script can be used to determine which indexes would benefit from a rebuild, how much they will shrink by (assuming 8k block size and 10% pctfree), results ordered by severity.

select a.*, round(index_leaf_estimate_if_rebuilt/current_leaf_blocks*100) percent, case when index_leaf_estimate_if_rebuilt/current_leaf_blocks < 0.5 then 'candidate for rebuild' end status
from
(
select table_name, index_name, current_leaf_blocks, round (100 / 90 * (ind_num_rows * (rowid_length + uniq_ind + 4) + sum((avg_col_len) * (tab_num_rows) ) ) / (8192 - 192) ) as index_leaf_estimate_if_rebuilt
from (
select tab.table_name, tab.num_rows tab_num_rows , decode(tab.partitioned,'YES',10,6) rowid_length , ind.index_name, ind.index_type, ind.num_rows ind_num_rows, ind.leaf_blocks as current_leaf_blocks,
decode(uniqueness,'UNIQUE',0,1) uniq_ind,ic.column_name as ind_column_name, tc.column_name , tc.avg_col_len
from dba_tables tab
join dba_indexes ind on ind.owner=tab.owner and ind.table_name=tab.table_name
join dba_ind_columns ic on ic.table_owner=tab.owner and ic.table_name=tab.table_name and ic.index_owner=tab.owner and ic.index_name=ind.index_name
join dba_tab_columns tc on tc.owner=tab.owner and tc.table_name=tab.table_name and tc.column_name=ic.column_name
where tab.owner='&OWNER' and ind.leaf_blocks is not null and ind.leaf_blocks > 1000
) group by table_name, index_name, current_leaf_blocks, ind_num_rows, uniq_ind, rowid_length
) a where index_leaf_estimate_if_rebuilt/current_leaf_blocks < 0.5
order by index_leaf_estimate_if_rebuilt/current_leaf_blocks

最新文章

  1. css3箭头效果
  2. Yocto开发笔记之《应用程序架构》(QQ交流群:519230208)
  3. 一个简单的dos脚本, svn 获取代码 - Tomcat 备份 - Maven 编译 - 停止/启动Tomcat - Tomcat站点 发布
  4. 【python cookbook】【字符串与文本】16.以固定的列数重新格式化文本
  5. Java Abstract Class
  6. 黄聪:PHP json_encode中文乱码解决方法
  7. 【转载】OpenStack Swift学习笔记
  8. Spring(3.2.3) - Beans(3): Bean 实例的创建方式
  9. JS 在 HTML 无缝滚动
  10. EXP导出aud$报错EXP-00008,ORA-00904 解决
  11. Oracle 12 Rman增量备份
  12. [Java Web学习]Tomcat启动时报war exploded: Error during artifact deployment
  13. C语言的抽象与函数指针--思想(转)
  14. ASP.NET core 2.1部署到 Centos 7
  15. metaclass(元类)
  16. TensorFlow queue多线程读取数据
  17. 使用C#开发数据库应用程序
  18. df命令/du命令/磁盘分区
  19. codevs 2796 最小完全图
  20. 中文latex参考文献格式

热门文章

  1. MIME sniffing攻击
  2. replace()方法解析
  3. 判断素数(翁凯男神MOOC)
  4. location search的中文加密
  5. C++字符串和向量
  6. el-js中循环遍历el的集合
  7. jquery 滚动条位置的
  8. VBox添加虚拟磁盘挂载
  9. SVN获取最新代码,完成后续开发
  10. 八、AbstractFactory 抽象工厂模式