[i=s] 本帖最后由 wei-xh 于 2013-7-9 21:20 编辑
不知道你是否对这些有过疑问？
我随机的删除了表里的一些记录，我不知道我的索引空间是不是会被重用？
我删除了表里大部分记录，我不知道表上一个自增的索引的空间会不会被重用？
我列举了三种场景下索引空间的重用，希望对你有一点点的帮助和提高。
【场景一】
测试的思路：
1）删除一个索引块里的某几个entry，但是不要删除完。之所以不删除完，是考虑到，如果索引块内的entry被完全删除，那么这个索引块会被加入到索引段头的空闲bitmap里，这个场景是我们测试的第二个场景。
2）新开一个事物，往表里新插入一条记录，观察新插记录会不会导致ORACLE清除索引块里的deleted entry。
我们先准备下实验的环境：创建测试表，共9条数据，在id列上创建索引
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10;
create index i on test(id);
我们用ANALYZE对索引做一下分析，然后看一下我们本次实验比较care的几个统计值。
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 0
LF_ROWS：代表索引上总共的entry数（包含deleted的entry）。
DEL_LF_ROWS：代表索引上总共的deleted的entry，这些entry还没有被cleanout。
采用ANALYZE分析索引后，我们可以知道这个索引一共9个entry，还没有被删除的entry：DEL_LF_ROWS的值为0。
我们继续实验，删除4个entry：
DELETE test WHERE id in (2,4,6,8);
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 4
查询的结果跟我们的预期相符。索引块内共9个entry，其中4个是被删除的。
我们可以通过ORACLE提供的索引tree dump工具来观察下这个时候索引entry的样子：
tree dump:
col object_name for a30
select object_name,object_id from dba_objects where object_name in ('I');
OBJECT_NAME OBJECT_ID
------------------------------ ----------
I 119015
alter session set events 'immediate trace name treedump level 119023';
tree dump 里的内容：
----- begin tree dump
leaf: 0x119994c 18454860 (0: nrow: 9 rrow: 5)
----- end tree dump
从tree dump可以看到索引只有一个块，这是由于实验的数据量非常少，这个块既充当了根块也充当了分支块还充当了叶子块。
根据leaf: 0x119994c 18454860 (0: nrow: 9 rrow: 5)，我们知道这个索引块的地址18454860，共有9个entry（nrow: 9）,实际存在的entry有5个（rrow: 5），说明有4个是delete的。
我们再来看看此时索引块内的entry长什么样子：
根据索引块地址18454860 ，我们来通过工具包转换成我们需要的格式，然后dump这个索引块。
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18454860),DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18454860)FROM dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18454860) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18454860)
---------------------------------------------- -----------------------------------------------
4 1677644
alter system dump datafile 4 block 1677644;
dump的结果：
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 19 99 44 00 00
row#1[8008] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 19 99 44 00 01
row#2[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 19 99 44 00 02
row#3[7984] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 19 99 44 00 03
row#4[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 19 99 44 00 04
row#5[7960] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 01 19 99 44 00 05
row#6[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 19 99 44 00 06
row#7[7936] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 01 19 99 44 00 07
row#8[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 19 99 44 00 08
标红部分---D--，代表这个entry已经被删除，ORACLE只是做了删除标记，并未物理的释放这个entry占有的空间。
下面的步骤就比较重要了，我们往表里插入一条记录：
insert into test values(10,'weixh');
1 row created.
commit;
alter system checkpoint;(写脏数据）
我们再次dump之前的索引块：
alter system dump datafile 4 block 1677644;
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 19 99 44 00 00
row#1[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 19 99 44 00 02
row#2[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 19 99 44 00 04
row#3[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 19 99 44 00 06
row#4[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 19 99 44 00 08
row#5[7912] flag: ------, lock: 2, len=12------------新插入的entry
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 01 19 99 46 00 00
----- end of leaf block dump -----
我们从dump的显示中可以知道，之前的索引entry已经被cleanout，也就是说空间已经被重用了，索引的最后是我们新插入的数据。
通过分析索引的统计信息，也说明了这一点：一共6个索引entry，不存在被删除的entry。
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
6 0
场景一总结：
1）任何一个新事务，往叶子块插入一条记录，ORACLE都会移除/释放这个索引块内所有的已被删除的entry。
2）在一个随机插入的索引列上，被删除空间的重用往往不会存在什么问题，就像我们看到的，只要一有新插入，空间就会释放。
【场景二】
很多人对于这个场景有误解：
一张表的字段id是自增的，如果我们按照id的某个范围，把记录删除。那么这些记录占用的索引空间将不会被重用。
测试思路：
1）新建一张表，表上有一个自增的索引字段，一万记录数，然后清空表。
2）往表里再插入一万记录，但是这一万记录的值要比上面步骤一里的大。
3）观察索引的叶子块数有没有变化
我们来试验验证一把。
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10000;
create index i on test(id);
ANALYZE INDEX i VALIDATE STRUCTURE;
Index analyzed.
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
经过上面的步骤，我们创建了一张9999条记录的一张表，id的值从1到9999。id上有一个普通的索引。
我们然后把表清空。
delete from test;
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
Index analyzed.
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 9999
表清空后，我们分析了索引，查询INDEX_STATS视图，也印证了表里有9999条记录，而且全部是已经被删除的，这些记录一共占取了21个索引块。
然后我们往表里插入10000条记录，id的值从20001开始递增，保证不会跟上面的id有重复。如果说这些叶子块不会被重用的话，我们插入数据后，叶子块的数目应该是要变多的。
INSERT INTO test SELECT rownum+20000, 'weixh' FROM dba_objects where rownum<= 10000;
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 10000 0
你发现了什么？
索引叶子块的数目没发生任何的变化！而且，DEL_LF_ROWS的值为0，说明这些被删除的entry，已经被cleanout，被oracle重用了。
场景二总结：
索引的叶子块被清空后，会把这个叶子块做为空闲块加入到索引段的空闲bitmap里（如果你使用的是ASSM），这样后面的事务就可以重新使用这些空闲的索引块了。
【场景三】
这个场景知道的人比较少，可以在Richard Foote博客里找到类似的博文。
延迟块清除会对索引叶子块做cleanout，释放索引的空间。
测试思路：
1）新建一张一万记录的表，表上存在一个普通的索引
2）清空表，不要提交
3）发生检查点
4）提交步骤2的事物
5）分析索引（分析索引会导致物理读，发生延迟块清除），查看索引统计值
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10000;
create index i on test(id);
ANALYZE INDEX i VALIDATE STRUCTURE;
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
我们经过上面的步骤，创建了一个有9999条记录的表。在id上存在一个普通索引。
delete from test;
另开一个session 执行：alter system flush buffer_cache;
然后对delete的会话进行commit;
上面的步骤，在清空表数据后，把脏数据全部刷新磁盘，这样就能保证，ORACLE不能做任何的块清除。
经过上面两个场景的锻炼，我想你一定能知道，现在索引里有9999个entry，其中9999个是被删除的entry。
ANALYZE INDEX i VALIDATE STRUCTURE;
我们通过ANALYZE来分析一下索引，注意哦，这个命令会真实的去读索引块，读取索引块的过程中，发生延迟块清除。并且这里ORACLE会在延迟块清除的过程中，把索引的被删除的entry做cleanout，空间也会被释放了。
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 0 0
可以看到DEL_LF_ROWS，LF_ROWS的值都为0。索引空间已经在ANALYZE后完全释放了。（并不是ANALYZE的功劳，而是这个操作递归导致的延迟块清楚产生的效果）
来让我们具体dump下，索引叶子块现在长什么样子。如果像INDEX_STATS里显示的，那么此时索引的叶子块应该是没有entry的。
col object_name for a30
select object_name,object_id from dba_objects where object_name in ('I');
OBJECT_NAME OBJECT_ID
------------------------------ ----------
I 119023
alter session set events 'immediate trace name treedump level 119025';
Session altered.
branch: 0x119b1ec 18461164 (0: nrow: 21, level: 1)
leaf: 0x119b1ed 18461165 (-1: nrow: 0 rrow: 0)
leaf: 0x119b1ee 18461166 (0: nrow: 0 rrow: 0)
leaf: 0x119b1ef 18461167 (1: nrow: 0 rrow: 0)
leaf: 0x119b1f0 18461168 (2: nrow: 0 rrow: 0)
leaf: 0x119b1f1 18461169 (3: nrow: 0 rrow: 0)
leaf: 0x119b1f2 18461170 (4: nrow: 0 rrow: 0)
leaf: 0x119b1f3 18461171 (5: nrow: 0 rrow: 0)
leaf: 0x119b1f4 18461172 (6: nrow: 0 rrow: 0)
leaf: 0x119b1f5 18461173 (7: nrow: 0 rrow: 0)
leaf: 0x119b1f6 18461174 (8: nrow: 0 rrow: 0)
leaf: 0x119b1f7 18461175 (9: nrow: 0 rrow: 0)
leaf: 0x119b1f8 18461176 (10: nrow: 0 rrow: 0)
leaf: 0x119b1fa 18461178 (11: nrow: 0 rrow: 0)
leaf: 0x119b1fb 18461179 (12: nrow: 0 rrow: 0)
leaf: 0x119b1fc 18461180 (13: nrow: 0 rrow: 0)
leaf: 0x119b1fd 18461181 (14: nrow: 0 rrow: 0)
leaf: 0x119b1fe 18461182 (15: nrow: 0 rrow: 0)
leaf: 0x119b1ff 18461183 (16: nrow: 0 rrow: 0)
leaf: 0x119b200 18461184 (17: nrow: 0 rrow: 0)
leaf: 0x119b201 18461185 (18: nrow: 0 rrow: 0)
leaf: 0x119b202 18461186 (19: nrow: 0 rrow: 0)
----- end tree dump
随便找个叶子块dump一下。
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18461165),DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18461165)FROM dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18461165) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18461165)
---------------------------------------------- -----------------------------------------------
4 1683949
alter system dump datafile 4 block 1683949;
Leaf block dump
===============
header address 133640292=0x7f73064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 0
kdxcofbo 36=0x24
kdxcofeo 8032=0x1f60
kdxcoavs 7996
kdxlespl 0
kdxlende 0
kdxlenxt 18461166=0x119b1ee
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
----- end of leaf block dump -----
发现索引叶子块出了块头的一些信息，没有任何的entry，真的是空空如也！被删除的索引entry都已经被ORACLE做了cleanout了。
上面的步骤里，如果你省去了flush buffer cache这个步骤，查询的INDEX_STATS的结果就会是下面这样了。由于commit发生的时候数据都在内存中，因此ORACLE会对块做快速块清除，快速的块清除不会去做索引entry的cleanout。：
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 9999
场景三的总结：
1）一些大的事物由于脏块较多，非常可能会导致事物提交的时候，一些脏块已经被刷出磁盘了
2）当这些没有清除的块再次被访问的时候，会发生延迟块清除。
3）延迟块清除会把这个块上所有的被删除的索引entry做cleanout。
不知道你是否对这些有过疑问？
我随机的删除了表里的一些记录，我不知道我的索引空间是不是会被重用？
我删除了表里大部分记录，我不知道表上一个自增的索引的空间会不会被重用？
我列举了三种场景下索引空间的重用，希望对你有一点点的帮助和提高。
【场景一】
测试的思路：
1）删除一个索引块里的某几个entry，但是不要删除完。之所以不删除完，是考虑到，如果索引块内的entry被完全删除，那么这个索引块会被加入到索引段头的空闲bitmap里，这个场景是我们测试的第二个场景。
2）新开一个事物，往表里新插入一条记录，观察新插记录会不会导致ORACLE清除索引块里的deleted entry。
我们先准备下实验的环境：创建测试表，共9条数据，在id列上创建索引
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10;
create index i on test(id);
我们用ANALYZE对索引做一下分析，然后看一下我们本次实验比较care的几个统计值。
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 0
LF_ROWS：代表索引上总共的entry数（包含deleted的entry）。
DEL_LF_ROWS：代表索引上总共的deleted的entry，这些entry还没有被cleanout。
采用ANALYZE分析索引后，我们可以知道这个索引一共9个entry，还没有被删除的entry：DEL_LF_ROWS的值为0。
我们继续实验，删除4个entry：
DELETE test WHERE id in (2,4,6,8);
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 4
查询的结果跟我们的预期相符。索引块内共9个entry，其中4个是被删除的。
我们可以通过ORACLE提供的索引tree dump工具来观察下这个时候索引entry的样子：
tree dump:
col object_name for a30
select object_name,object_id from dba_objects where object_name in ('I');
OBJECT_NAME OBJECT_ID
------------------------------ ----------
I 119015
alter session set events 'immediate trace name treedump level 119023';
tree dump 里的内容：
----- begin tree dump
leaf: 0x119994c 18454860 (0: nrow: 9 rrow: 5)
----- end tree dump
从tree dump可以看到索引只有一个块，这是由于实验的数据量非常少，这个块既充当了根块也充当了分支块还充当了叶子块。
根据leaf: 0x119994c 18454860 (0: nrow: 9 rrow: 5)，我们知道这个索引块的地址18454860，共有9个entry（nrow: 9）,实际存在的entry有5个（rrow: 5），说明有4个是delete的。
我们再来看看此时索引块内的entry长什么样子：
根据索引块地址18454860 ，我们来通过工具包转换成我们需要的格式，然后dump这个索引块。
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18454860),DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18454860)FROM dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18454860) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18454860)
---------------------------------------------- -----------------------------------------------
4 1677644
alter system dump datafile 4 block 1677644;
dump的结果：
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 19 99 44 00 00
row#1[8008] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 19 99 44 00 01
row#2[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 19 99 44 00 02
row#3[7984] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 19 99 44 00 03
row#4[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 19 99 44 00 04
row#5[7960] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 01 19 99 44 00 05
row#6[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 19 99 44 00 06
row#7[7936] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 01 19 99 44 00 07
row#8[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 19 99 44 00 08
标红部分---D--，代表这个entry已经被删除，ORACLE只是做了删除标记，并未物理的释放这个entry占有的空间。
下面的步骤就比较重要了，我们往表里插入一条记录：
insert into test values(10,'weixh');
1 row created.
commit;
alter system checkpoint;(写脏数据）
我们再次dump之前的索引块：
alter system dump datafile 4 block 1677644;
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 19 99 44 00 00
row#1[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 19 99 44 00 02
row#2[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 19 99 44 00 04
row#3[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 19 99 44 00 06
row#4[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 19 99 44 00 08
row#5[7912] flag: ------, lock: 2, len=12------------新插入的entry
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 01 19 99 46 00 00
----- end of leaf block dump -----
我们从dump的显示中可以知道，之前的索引entry已经被cleanout，也就是说空间已经被重用了，索引的最后是我们新插入的数据。
通过分析索引的统计信息，也说明了这一点：一共6个索引entry，不存在被删除的entry。
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
6 0
场景一总结：
1）任何一个新事务，往叶子块插入一条记录，ORACLE都会移除/释放这个索引块内所有的已被删除的entry。
2）在一个随机插入的索引列上，被删除空间的重用往往不会存在什么问题，就像我们看到的，只要一有新插入，空间就会释放。
【场景二】
很多人对于这个场景有误解：
一张表的字段id是自增的，如果我们按照id的某个范围，把记录删除。那么这些记录占用的索引空间将不会被重用。
测试思路：
1）新建一张表，表上有一个自增的索引字段，一万记录数，然后清空表。
2）往表里再插入一万记录，但是这一万记录的值要比上面步骤一里的大。
3）观察索引的叶子块数有没有变化
我们来试验验证一把。
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10000;
create index i on test(id);
ANALYZE INDEX i VALIDATE STRUCTURE;
Index analyzed.
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
经过上面的步骤，我们创建了一张9999条记录的一张表，id的值从1到9999。id上有一个普通的索引。
我们然后把表清空。
delete from test;
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
Index analyzed.
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 9999
表清空后，我们分析了索引，查询INDEX_STATS视图，也印证了表里有9999条记录，而且全部是已经被删除的，这些记录一共占取了21个索引块。
然后我们往表里插入10000条记录，id的值从20001开始递增，保证不会跟上面的id有重复。如果说这些叶子块不会被重用的话，我们插入数据后，叶子块的数目应该是要变多的。
INSERT INTO test SELECT rownum+20000, 'weixh' FROM dba_objects where rownum<= 10000;
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 10000 0
你发现了什么？
索引叶子块的数目没发生任何的变化！而且，DEL_LF_ROWS的值为0，说明这些被删除的entry，已经被cleanout，被oracle重用了。
场景二总结：
索引的叶子块被清空后，会把这个叶子块做为空闲块加入到索引段的空闲bitmap里（如果你使用的是ASSM），这样后面的事务就可以重新使用这些空闲的索引块了。
【场景三】
这个场景知道的人比较少，可以在Richard Foote博客里找到类似的博文。
延迟块清除会对索引叶子块做cleanout，释放索引的空间。
测试思路：
1）新建一张一万记录的表，表上存在一个普通的索引
2）清空表，不要提交
3）发生检查点
4）提交步骤2的事物
5）分析索引（分析索引会导致物理读，发生延迟块清除），查看索引统计值
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10000;
create index i on test(id);
ANALYZE INDEX i VALIDATE STRUCTURE;
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
我们经过上面的步骤，创建了一个有9999条记录的表。在id上存在一个普通索引。
delete from test;
另开一个session 执行：alter system flush buffer_cache;
然后对delete的会话进行commit;
上面的步骤，在清空表数据后，把脏数据全部刷新磁盘，这样就能保证，ORACLE不能做任何的块清除。
经过上面两个场景的锻炼，我想你一定能知道，现在索引里有9999个entry，其中9999个是被删除的entry。
ANALYZE INDEX i VALIDATE STRUCTURE;
我们通过ANALYZE来分析一下索引，注意哦，这个命令会真实的去读索引块，读取索引块的过程中，发生延迟块清除。并且这里ORACLE会在延迟块清除的过程中，把索引的被删除的entry做cleanout，空间也会被释放了。
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 0 0
可以看到DEL_LF_ROWS，LF_ROWS的值都为0。索引空间已经在ANALYZE后完全释放了。（并不是ANALYZE的功劳，而是这个操作递归导致的延迟块清楚产生的效果）
来让我们具体dump下，索引叶子块现在长什么样子。如果像INDEX_STATS里显示的，那么此时索引的叶子块应该是没有entry的。
col object_name for a30
select object_name,object_id from dba_objects where object_name in ('I');
OBJECT_NAME OBJECT_ID
------------------------------ ----------
I 119023
alter session set events 'immediate trace name treedump level 119025';
Session altered.
branch: 0x119b1ec 18461164 (0: nrow: 21, level: 1)
leaf: 0x119b1ed 18461165 (-1: nrow: 0 rrow: 0)
leaf: 0x119b1ee 18461166 (0: nrow: 0 rrow: 0)
leaf: 0x119b1ef 18461167 (1: nrow: 0 rrow: 0)
leaf: 0x119b1f0 18461168 (2: nrow: 0 rrow: 0)
leaf: 0x119b1f1 18461169 (3: nrow: 0 rrow: 0)
leaf: 0x119b1f2 18461170 (4: nrow: 0 rrow: 0)
leaf: 0x119b1f3 18461171 (5: nrow: 0 rrow: 0)
leaf: 0x119b1f4 18461172 (6: nrow: 0 rrow: 0)
leaf: 0x119b1f5 18461173 (7: nrow: 0 rrow: 0)
leaf: 0x119b1f6 18461174 (8: nrow: 0 rrow: 0)
leaf: 0x119b1f7 18461175 (9: nrow: 0 rrow: 0)
leaf: 0x119b1f8 18461176 (10: nrow: 0 rrow: 0)
leaf: 0x119b1fa 18461178 (11: nrow: 0 rrow: 0)
leaf: 0x119b1fb 18461179 (12: nrow: 0 rrow: 0)
leaf: 0x119b1fc 18461180 (13: nrow: 0 rrow: 0)
leaf: 0x119b1fd 18461181 (14: nrow: 0 rrow: 0)
leaf: 0x119b1fe 18461182 (15: nrow: 0 rrow: 0)
leaf: 0x119b1ff 18461183 (16: nrow: 0 rrow: 0)
leaf: 0x119b200 18461184 (17: nrow: 0 rrow: 0)
leaf: 0x119b201 18461185 (18: nrow: 0 rrow: 0)
leaf: 0x119b202 18461186 (19: nrow: 0 rrow: 0)
----- end tree dump
随便找个叶子块dump一下。
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18461165),DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18461165)FROM dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18461165) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18461165)
---------------------------------------------- -----------------------------------------------
4 1683949
alter system dump datafile 4 block 1683949;
Leaf block dump
===============
header address 133640292=0x7f73064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 0
kdxcofbo 36=0x24
kdxcofeo 8032=0x1f60
kdxcoavs 7996
kdxlespl 0
kdxlende 0
kdxlenxt 18461166=0x119b1ee
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
----- end of leaf block dump -----
发现索引叶子块出了块头的一些信息，没有任何的entry，真的是空空如也！被删除的索引entry都已经被ORACLE做了cleanout了。
上面的步骤里，如果你省去了flush buffer cache这个步骤，查询的INDEX_STATS的结果就会是下面这样了。由于commit发生的时候数据都在内存中，因此ORACLE会对块做快速块清除，快速的块清除不会去做索引entry的cleanout。：
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 9999
场景三的总结：
1）一些大的事物由于脏块较多，非常可能会导致事物提交的时候，一些脏块已经被刷出磁盘了
2）当这些没有清除的块再次被访问的时候，会发生延迟块清除。
3）延迟块清除会把这个块上所有的被删除的索引entry做cleanout。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-765910/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-765910/
3920
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
