索引的碎片问题

由于对基表做DML操作，便导致对索引表块的自动更改操作，尤其是基表的delete操作会引起index表的index entries的逻辑删除，注意，只有当一个索引块中的全部index entry都被删除了，这个块才会被收回。如果update基表索引列，则索引块会发生entry delete，再entry insert，这都些动作都可能产生索引碎片。

在Scott用户做实验

1.搭建实验环境

SQL> create table t (id int);

SQL> create index ind_1 on t(id);

SQL>

begin

  for i in 1..1000000 loop

       insert into t values (i);

              if mod(i, 100)=0 then

              commit;

              end if;

  end loop;

end;

/

2.收集数据

SQL> analyze index ind_1 validate structure;

3.查看索引

SQL> select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats

4.删除数据

SQL> delete t where rownum<700000;

5.再次查看

SQL> select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats

6.重建索引

SQL> alter index ind_1 rebuild [online] [tablespace name];

7.再次查看

在Oracle文档里并没有清晰的给出索引碎片的量化标准,Oracle建议通过Segment Advisor(段顾问）解决表和索引的碎片问题（053课程会涉及），如果你想自行解决，可以通过查看index_stats视图，当以下三种情形之一发生时，说明积累的碎片应该整理了（仅供参考）。

1.HEIGHT >=4  

2 PCT_USED< 50%  

3 DEL_LF_ROWS/LF_ROWS>0.2

联机重建索引通常比删除后再重建要更实用，Oracle9i和10g一直提供联机索引重建功能--rebuild online,但由于涉及到使用表的排他锁，通常一段时间内其他用户的DML操作需要等待。

在Oracle11g中有了改进，实现了最终意义上的联机索引重建(rebuild index online)的特性，它重新设计了锁的模式，使其他用户对基表的DML操作可以不受干扰。

SQL> alter index ind_1 coalesce; //索引融合，比rebuild动作轻，可以合并一些块中的index entries;

索引不可用unusable，以及不可见（invisible)

索引被设定为 unusable的结果是：保存索引定义，不删除索引，也不更新索引）。视图user_indexes中status字段是unusable(考点）。

查看索引的状态

SQL> select index_name,status VISIBILITY  from user_indexes;

索引invisible

SQL> alter index ind_l invisible;

SQL> select index_name,status VISIBILITY  from user_indexes;

SQL> alter index ind_l unusable;

如想再次使用需要做rebuild。

SQL> alter index test_idx rebuild;

在11g里， Oracle提供了一个新特性来降低直接删除索引或禁用索引的风险，那就是索引不可见（Index Invisible)。我们可以在创建索引时指定invisible属性或者用alter语句来修改索引为invisible(visible)（考点）

查询索引的两个动态视图：

select * from dba_indexes;

select * from dba_ind_columns;