对索引频繁的update，delete操作会产生index Frag，影响索引效率，增加索引IO。
1、索引碎片分析
产生测试索引碎片：
SCOTT @devcedb>select count(*) from obj;
COUNT(*)
----------
124256
SCOTT @devcedb>create index ind_obj_id on obj(OBJECT_ID);
Index created.
SCOTT @devcedb>delete obj where rownumcommit;
Commit complete.
索引碎片分析：
SCOTT @devcedb>analyze index ind_obj_id validate structure;
Index analyzed.
--
SCOTT @devcedb>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
索引碎片比率：(del_lf_rows_len/lf_rows_len)*100，如果百分比超过20%就说明索引碎片比率很高了。需要整理碎片。
索引和表数据是级联关系的，当删除表数据的时候，索引条目不会被自动删除，而是在该条目上打上一个删除(D)的标识位，具体后面会说明，索引的block数量是不会改变的，空叶块不会被删除。所以当INDEX FAST FULL SCAN和INDEX FULL SCAN的时候，这些空索引块也会被加载到内存中，增加了IO。索引空叶块多，极大影响了索引扫描的效率。否则索引碎片对效率的影响不是很大。如下：
SCOTT @devcedb>select object_id from obj where object_id < 9000;
41377 rows selected.
Elapsed: 00:00:01.13
Execution Plan
----------------------------------------------------------
Plan hash value: 2777403740
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55341 | 702K| 79 (2)| 00:00:01 |
|* 1 | | IND_OBJ_ID | 55341 | 702K| 79 (2)| 00:00:01 |
-----------------------------------------------------------------------------------
SYS AS SYSDBA@devcedb>select count(*) from x$bh where obj='102822';
COUNT(*)
---------- ---从x$bh查询缓存blocks，要用DBA_OBJECTS.DATA_OBJECT_ID
268 ---该索引加载到buffer pool的数据块数，该索引分配有384 blocks，266 LEAF_BLOCKS
那么索引空块会不会被重用呢？下面测试说明：
SCOTT @devcedb>select count(*) from obj2;
COUNT(*)
----------
62144
SCOTT @devcedb>create unique index uni_ind_obj2_id on obj2(id);
Index created. --该索引分配有256 blocks，130 LEAF_BLOCKS
SCOTT @devcedb>delete obj2 where rownumcommit;
Commit complete.
SCOTT @devcedb>analyze index uni_ind_obj2_id validate structure;
Index analyzed.
SCOTT @devcedb>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
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
UNI_IND_OBJ2_ID 256 240063 938727 25.5732497 25.5732626
SCOTT @devcedb>insert into obj2 select seq_obj2.nextval id,a.* from dba_objects a;
15537 rows created.
SCOTT @devcedb>commit;
Commit complete.
SCOTT @devcedb>analyze index uni_ind_obj2_id validate structure;
Index analyzed.
SCOTT @devcedb>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
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
UNI_IND_OBJ2_ID 256 7180 938727 .764865611 .764882473
SYS AS SYSDBA@devcedb>select INDEX_NAME,LEAF_BLOCKS,BLEVEL from dba_indexes where INDEX_NAME=upper('uni_ind_obj2_id');
INDEX_NAME LEAF_BLOCKS BLEVEL
------------------------------ ----------- ----------
UNI_IND_OBJ2_ID 130 1
这里我们看到，索引的碎片降低了，而且LEAF_BLOCKS的数量没有增加，说明空叶块被重用了。
当删除表里的一条记录时，其对应于索引里的索引条目并不会被物理的删除，只是做了一个删除标记（这可以通过dump 索引数据块alter system dump datafile # block #;可以看到类似”row#0[443] flag: ---D-, lock: 2“。)当一个空叶块被重用的时候，当第一条数据插入该索引叶块之前，Oracle清空该空叶块上所有打上D标识位的索引条目，然后重用该索引块。如下：
SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100,USED_SPACE,BTREE_SPACE,PCT_USED 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 USED_SPACE BTREE_SPACE PCT_USED
------------------------------ ---------- --------------- ----------- --------------------------------- ------------------------- ---------- ----------- ----------
IND_OBJ_ID 256 307878 835601 36.8450971 36.8625788 1279392 66
SCOTT @devcedb>insert into obj select a.* from dba_objects a where rownumcommit;
Commit complete.
SCOTT @devcedb>analyze index ind_obj_id validate structure;
Index analyzed.
SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100,USED_SPACE,BTREE_SPACE,PCT_USED 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 USED_SPACE BTREE_SPACE PCT_USED
------------------------------ ---------- --------------- ----------- --------------------------------- ------------------------- ---------- ----------- ----------
IND_OBJ_ID 256 306312 834091 36.7240505 36.7303962 1279392 66
我们关注下USED_SPACE和BTREE_SPACE
USED_SPACE--Total space that is currently being used in the B-Tree
BTREE_SPACE --Total space currently allocated in the B-Tree
重新分析该索引后我们注意到USED_SPACE反而降低了，BTREE_SPACE不变（当然我们也可以看到LF_ROWS减少了），在这4条数据重用一个空索引块后，释放的空间大于使用的空间，该空叶块被重用了。
半空叶块是如何重用的呢？
我们构想一下，一个有两个叶块的index，第一个叶块包含1到10(不包含6），第二个叶块11到20，这时候我们删除表中2,4,11的数据，分析下索引后，分三种情况：1）插入键之前删除的键值，插入2，索引叶块1标识为D的两条索引记录会被清空，重新插入键值为2的记录，索引空间被重用，BTREE_SPACE不变，USED_SPACE降低,LF_ROWS减1。2）插入一个属于原来被删除键值范围内的值，插入6，我们会发现和情况1相同。3）插入比之前键值更大的值，假定叶块2空间满了，会新增一个叶块。