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