Oracle数据库中如何检查索引碎片并重建索引-sdwjian-ChinaUnix博客

当索引的碎片过多时，会影响执行查询的速度，从而影响到我们的工作效率。这时候采取的最有利的措施莫过于重建索引了。本文主要介绍了Oracle数据库中检查索引碎片并重建索引的过程，接下来我们就开始介绍这一过程。

重建索引的步骤如下：

1. 确认基本信息

登入数据库，找到专门存放index 的tablespace，并且这个tablespace下所有index的owner都是tax.将index专门存放在一个独立的tablespace, 与数据表的tablespace分离，是常用的数据库设计方法。

2. 查找哪些index需要重建

通过anlyze index .... validate structure命令可以分析单个指定的index，并且将单个index 分析的结果存放到 index_stats试图下。一般判断的依据是：

height >4
pct_used < 50%
del_lf_rows / lf_rows +0.001 > 0.03

3. google上下载了遍历所有index脚本

发现anlyze index .... validate structure只能填充单个index分析信息，于是google了下，从网上下了个Loop 脚本，遍历索引空间下所有的索引名字，并且可以把所有index的分析信息存放到自己建立的一个用户表中。

先建立个统计表

create table T_ANALYZ_MONITOR_INDEX
(
  F_INDEX_NAME  VARCHAR2(50),
  F_DEL_LF_ROWS NUMBER,
  F_LF_ROWS     NUMBER,
  F_RATE        NUMBER(4,2),
  F_MONITOR_DATE DATE default sysdate not null
);

再建个历史表

create table t_analyz_index_stats as select * from index_stats

做个分析过程  查出表并且分析插入历史表统计删除比率到统计表

create or replace procedure P_ANALYZ_DAY_INDEX_SATAS is
    v_sql varchar2(100);
Begin

  for a in (Select INDEX_NAME   From User_Indexes  Where index_type<>'LOB') loop
    v_sql := ' analyze index ' || a.index_name || ' validate structure';
    execute immediate v_sql;

    Insert Into T_ANALYZ_INDEX_STATS
     Select * From Index_Stats;

    insert into T_ANALYZ_MONITOR_INDEX(F_INDEX_NAME, F_DEL_LF_ROWS, F_LF_ROWS, F_RATE)
     select name,del_lf_rows,lf_rows, round(del_lf_rows * 100 / decode((lf_rows + del_lf_rows),0,1), 2)
     from index_stats;

  End loop;

end;

4. anlyze index 锁定index

发现下载的脚本不好用，应为anlyze index在分析索引前要争取独占锁，锁住index，很明显有些index正在被应用系统的使用，所以运行anlyze失败。这里吸取的教训是，尽量晚上做这种事。但是本人比较喜欢准时回家，所以在语句中添加Exception Handler，抛出anlyze index执行失败的那些index 名称，使脚本正常运行完毕。并且根据打印到前台的index name手动执行那些index分析。

5. 总结

虽然发现522个index中有160个符合上面的判断的依据。但是发现索引都不大，而那些拥有百万leaf的索引又没有符合上面的判断条件，所以结论是无需index rebuild online. 没有啥碎片。

6.什么时候可以rebuild index呢？

rebuild index online，对那些有大量DML操作的大索引是有益的。可以每个月季度做一次针对较大索引的rebuild。通常哪怕rebuild index online也会造成I/O争用，所以有无online意义不大，可以放到３－５个晚上，分批执行rebuild index，锁定index，不让用户用（没有用户等入的时候），并且加上paralle 8关键字，应为发现数据库服务器有8个cpu processors.

原文地址: