索引碎片整理--重建索引、合并索引、shrink索引

随着时间的推移，索引也可能会产生碎片，oracle在处理该问题的时候提供给予我们有三种方式：重建索引、合并索引、shrink 索引。每种都有自己的特点，今天我在此学习一下记录一下笔记。
第一：重建索引：
  重建索引其实语句很简单实用alter index index_name rebuild;参数即可对指定的索引进行重建，但是注意在重建索引的时候 会对相应的对象加锁，因此重建的时候一定要注意，如何避免在重建索引的时候不影响其他业务使用呢？那么可以指定online 参数，如：alter index index_name rebuild online;指定该参数之后就不会对其他业务访问对象产生任何影响。另外有时候我们还可以指定并行创建索引，但要注意在指定parallel（degree interger）参数的时候，那么并行度将存储于索引中，随着在基于硬件如cpu个数创建速度上确实提高了，但在在执行查询的时候将使用并行方式，有时候也会伴随着等待事件的出现如：PX Deq Credit: send blkd，因此创建索引是不是应该使用paralle应该斟酌一下。
eg：

  另外当我们需要重新创建反向键索引的时候需要指定reserver参数：alter index index_name rebuild reverse;回收未使用的空间，当使用
alter index index_name deallocate unused;
命令的时候，会将没有使用的空闲段返回给数据，但是曾经使用过的空块将不会返还给数据库空间（包含之前删除的索引或是移动条目导致段内没有使用的空间）对于分区索引和索引组织表的信息查看：
http://blog.csdn.net/rhys_oracle/article/details/18671897
http://blog.csdn.net/rhys_oracle/article/details/18409063
  另外如何确定是否需要重建索引呢？一般认为有两种情况：
  1、索引深度大于等于4
  2、已删除的索引条目占总索引条目的20%
  3、索引空间使用率小于50%
再次不得不提 一个视图index_stats该视图默认是没有任何数据的，当使用analyze index index_name validate structure;对索引结构分析之后将会填充相应的数据，一般该视图可以提供给我们足够的信息去引导我们是否需要对索引进行重建。
查看相关字段信息：

查看未删除叶子行数占总行数的百分比公式为：(（lf_rows-del_lf_rows)/lf_rows)*100;
查看未删除行占用的空间百分比公式为：（（used_space-del_lf_rows_len)/btree_space)*100;
pct_used计算公式为：（used_space/btree_space)*100
eg：

收集统计信息，之后可以看到在dba_indexes中依然显示存在的索引叶块，优化器从而使用该索引。

但是注意：使用analyze index index_name validate structure ;进行索引分析的时候会锁定相应的对象直到该命令执行完成，如果不加锁可以使用online参数，但使用online参数数据信息又不会记录到index_stats视图，且在重建索引的过程中会产生很多的redo日志，可以考虑使用nologging参数，另外当在分析完成后在执行插入操作，那么相应的del_lf_rows将会改变从而影响对索引的分析信息提取：
eg：

从以上可以看出两点内容，产生索引数据之后剩余的空间不会返还给数据库，但是当插入新数据的时候将有可能重新利用之前被删除数据的空间，另外一点可以看del_lf_row已经评估出现错误，到目前为止刚刚开始删除9999条数据，然后插入一条数据在进行分析，那么现在既然是9582，因此不能仅仅依靠del_lf_rows进行索引重建评估。以前记得有个朋友曾经提过这么一个问题，说是测试环境库执行一条sql会非常的块，但是导到正式环境却很慢，但是执行计划都是一样的，我的怀疑就是需要重建正式环境库的索引。因此，如果确定对 索引相同部分执行了大量删除操作，产生了大量的索引碎片，并且查询每次读取了大量的索引行，索引被频繁使用，这时候重建索引是有价值的。
第二种：合并索引
  合并索引就是将索引段中相邻的索引块其中空闲空间进行整合重组，从而释放索引块空间，这比较类似于我们windows的磁盘碎片整理，但是注意该过程不会将腾出的空间返回与数据库，而是加入到空闲空间列表中，以便下次在进行使用。这种操作对于那种以序列或是时间日志为字段的表是有非常重要价值的，因为当我们对这些表删除了大部分数据，那么其中很多空间是无法在进行使用的，那么在我们制定谓词查询的时候通常会扫描索引中很多空快，那么合并索引就将空的索引块进行释放与索引块的空闲列表中。
语句非常简单：
alter index index_name coalesce;
合并索引与重建索引不同事，合并索引不会降低索引的高度，而是对其数据条目进行重组整合，但是重建可能会降低索引高度，另外重建索引需要2倍的磁盘空间，首先需要存储原先的索引条目数据，还需要额外的空间存储新调整 的索引数据直到重建完成才可。

注：合并索引是一种在线操作。
第三种：shrink 索引：
 因为shrink是一个耗资源相对严重的过程，因此两个过程，一个是compact参数，另一个是直接shrink space，第一种类似于coalesce但是相比会产生更多的redo日志，执行完后不会释放空间，但是shrink space 除了整理碎片还可以将空间释放给表空间，但是shrink space虽然是在线可以做的，依然会产生过打的redo日志。除此之外shrink space还要启动行移动。
eg：
alter index index_name shrink space compact;
alter index index_name shrink space;
注：Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM).



select name,
         blocks,
         del_lf_rows_len,
         lf_rows_len,
         (del_lf_rows_len / lf_rows_len) * 100,
         (DEL_LF_ROWS / LF_ROWS) * 100
    from index_stats;
  
  NAME          BLOCKS     DEL_LF_ROWS_LEN      LF_ROWS_LEN      (DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100          (DEL_LF_ROWS / LF_ROWS) * 100
  ------------  ---------  -------------------  ---------------  ----------- ---------------------------------   -------------------------
  IND_OBJ_ID    384        766085               1906952          40.1732713                                      40.2394062
  
  索引碎片比率：(del_lf_rows_len / lf_rows_len) * 100，如果百分比超过20%就说明索引碎片比率很高了。需要整理碎片。