Oracle索引碎片分析、空间重用和整理

        对索引频繁的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 rownum<50000;

49999 rows deleted.

SCOTT @devcedb>commit;

Commit complete.

索引碎片分析：
SCOTT @devcedb>analyze index ind_obj_id validate structure;

Index analyzed.

--注意一点，就是该命令有一个坏处，就是在运行过程中，会锁定整个表，从而阻塞其他session对表进行插入、更新和删除等操作。这是因为该命令的主要目的并不是用来填充index_stats视图的，其主要作用在于校验索引中的每个有效的索引条目都对应到表里的一行，同时表里的每一行数据在索引中都存在一个对应的索引条目。为了完成该目的，所以在运行过程中要锁定整个表，同时对于很大的表来说，运行该命令需要耗费非常多的时间。

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                        40.1732713                40.2394062

索引碎片比率：(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 |  INDEX FAST FULL SCAN| 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);