Oracle_索引重建—优化索引碎片

– 查询是否需要重建索引：

– 分析索引的数据块是否有坏块，以及根据分析得到的数据（存放在index_stats）來判断索引是否需要重新建立。

SQL> analyze index AATD_IDX1 validate structure;
validate structure有两种模式：
offline ：（默认）会对表加一个4级別的锁（表共享），对run系統可能造成一定的影响。
online ：没有表lock的影响，但当以online模式分析时，在视图index_stats没有统计信息。

从9i开始，Oracle以建议使用dbms_stats package代替 analyze 了。

SQL> exec dbms_stats.gather_table_stats(‘用户名’,‘表名’,cascade=>true);

– 下面视图只支持：analyze index 命令

SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats;

HEIGHT DEL_LF_ROWS/LF_ROWS

---------- -------------------

1                   0

Executed in 0.381 seconds

当查询出来的 height>=4
或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合,该索引考虑重建

Oracle的最终建议

一般而言，极少需要重建 B 树索引，基本原因是 B 树索引很大程度上可以自我管理或自我平衡。
大多数索引都能保持平衡和完整，因为空闲的叶条目可以重复使用。
插入/更新和删除操作确实会导致索引块周围的可用空间形成碎片，但是一般来说这些碎片都会被正确的重用。
Clustering factor群集因子反映了给定的索引键值所对应的表中的数据排序情况。重建索引不会对群集因子产生影响，集群因子只能通过重组表的数据改变。
强烈建议不要定期重建索引，而应使用合适的诊断工具。

1、drop 原来的索引，然后再创建索引

删除索引：drop index IX_PM_USERGROUP;
创建索引：create index IX_PM_USERGROUP on T_PM_USER (fgroupid);
说明：此方式耗时间，无法在24*7环境中实现，不建议使用。

2 、直接重建

alter index indexname rebuild; 或 alter index indexname rebuild online;

说明：此方式比较快，可以在24*7环境中实现，建议使用此方式

2.1 alter index rebuild 和alter index rebuil online的区别

1、扫描方式不同
Rebuild以index fast full scan（or table full scan）方式读取原索引中的数据来构建一个新的索引，有排序的操作;
rebuild online 执行表扫描获取数据，有排序的操作;
说明：Rebuild 方式 (index fast full scan or table full scan 取决于统计信息的cost)
2 、rebuild 会阻塞 dml 操作，rebuild online 不会阻塞 dml 操作
3 、rebuild online 时系统会产生一个 SYS_JOURNAL_xxx 的 IOT 类型的系统临时日志表,所有 rebuild online 时索引的变化都记录在这个表中，当新的索引创建完成后，把这个表的记录维护到新的索引中去，然后 drop 掉旧的索引，rebuild online 就完成了

注意点：

1、执行rebuild操作时，需要检查表空间是否足够
2、虽然说rebuild online操作允许dml操作，但是还是建议在业务不繁忙时间段进行Rebuild操作会产生大量redo log

重建分区表上的分区索引

Alter index indexname rebuild partition PARTITION_NAME tablespace tablespacename;

– 分区索引重建：

select ‘alter index ‘|| index_name || ’ rebuild partition ‘|| partition_name || ’ online;’ from user_ind_partitions where index_name =‘索引’ ;

– 子分区索引重建：

Alter index indexname rebuild subpartition PARTITION_NAME tablespace tablespacename;
– SQL 拼接
select ‘alter index ‘|| index_name || ’ rebuild subpartition ‘|| subpartition_name || ’ online;’ from user_ind_subpartitions ;
注：这里的PARTITION_NAME指USER_IND_PARTITIONS中的PARTITION_NAME(索引分区中的索引分区名);

–查询分区表索引所在的分区

SELECT PI.TABLE_NAME,
IP.INDEX_NAME,
IP.PARTITION_NAME,
IP.STATUS,
IP.GLOBAL_STATS
FROM USER_PART_INDEXES PI, USER_IND_PARTITIONS IP
WHERE PI.INDEX_NAME = IP.INDEX_NAME AND PI.TABLE_NAME = ‘表名’;