Oracle索引碎片分析、空间重用和整理 - 数据库编程

n)*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 837792 1279392 66

SCOTT @devcedb>insert into obj select a.* from dba_objects a where rownum<5; --该索引存在大量空索引块，我们插入4条记录

4 rows created.

SCOTT @devcedb>commit;

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 836282 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空间满了，会新增一个叶块。

所以说经常被删除或更新index键值，以后几乎不再会被插入时，空间的重用率很低，碎片产生的就越快。

2、索引碎片整理
由于index_stats只能存储最近一行数据，analyze index后查询index_stats必须在同一session内，否则查询index_stats无记录，我们回到之前碎片很严重的索引ind_obj_id需要重新analyze一下：
SCOTT @devcedb>analyze index ind_obj_id validate structure;

Index analyzed.

Elapsed: 00:00:00.13
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
------------------------------ ---------- --------------- -

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