索引空间重用实验

 

观点：一旦索引使用过存储区，就会永远在那里，并且只能被相同的值重用。（由此推测，空闲空间永远不会返回索引结构，块永远不会重用）
结论：不敢苟同此观点，测试如下。
下面测试索引空间不重用的情况
如在下面这种索引有“空洞”的情况下：
如果原始的数字2使用（1－9999之间）的空间，将永远保留在那个索引块上，索引不会“接合”自己。这意味着，如果使用1到10000的值装 载一张表，然后隔行删除（所有的偶数数字），在哪个列的索引中就会有5000个“洞”。只有重新插入适合块的数据，并且那里有洞时，空间才会重用。
oracle不会尝试“收缩”或压缩索引，这可以通过ALTER INDEX REBUILD或COALESCE命令来完成。

测试如下：
1）创建测试表
SQL> create table t(x int);

表已创建。

SQL> begin
2 for i in 1..10000 loop
3 insert into t values(i);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL 过程已成功完成。
SQL> select count(*) from t;

COUNT(*)
----------
10000

2)增加索引
SQL> alter table t add constraint t_pk primary key (x);

表已更改。

3)现在测量偶数行数据删除前后利用的情况：
SQL> exec show_space('T_PK',user,'INDEX');
Free Blocks.............................0
Total Blocks............................24
Total Bytes.............................196608
Unused Blocks...........................2
Unused Bytes............................16384
Last Used Ext FileId....................5
Last Used Ext BlockId...................2
Last Used Block.........................2

PL/SQL 过程已成功完成。
SQL> begin
2 for i in 1..10000 loop
3 if mod(i,2)=0 then
4 delete from t where x=i;
5 end if;
6 end loop;
7 end;
8 /

PL/SQL 过程已成功完成。

SQL> select count(*) from t;

COUNT(*)
----------
5000
SQL> commit;

提交完成。

SQL> exec show_space('T_PK',user,'INDEX');
Free Blocks.............................0
Total Blocks............................24
Total Bytes.............................196608
Unused Blocks...........................2
Unused Bytes............................16384
Last Used Ext FileId....................5
Last Used Ext BlockId...................2
Last Used Block.........................2

PL/SQL 过程已成功完成。

数据删除了一半，但是空间并没有回到FREELIST上，因为数据行中有空洞。说明在这种情况下，空间无法收回。
如果索引的数据和表的数据大小相当，而由于表的经常更新导致索引存在上面这种空洞，这时候就不难理解为什么索引占用空间比表占用空间大了。

4)测试使用ALTER INDEX COALESCE命令来收缩空间
SQL> alter index T_PK coalesce;

索引已更改。

SQL> exec show_space('T_PK',user,'INDEX');
Free Blocks.............................10
Total Blocks............................24
Total Bytes.............................196608
Unused Blocks...........................2
Unused Bytes............................16384
Last Used Ext FileId....................5
Last Used Ext BlockId...................2
Last Used Block.........................2

PL/SQL 过程已成功完成。

全部数据使用了22块，删除一半数据后，使用收缩命令释放了10块，差不多是一半，实现了空间的收回。

5)测试使用ALTER INDEX REBUILD命令来重建索引
SQL> exec show_space('T_PK',user,'INDEX');
Free Blocks.............................0
Total Blocks............................12
Total Bytes.............................98304
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................5
Last Used Ext BlockId...................46
Last Used Block.........................4

PL/SQL 过程已成功完成。

重建索引实现了索引数据的重新分配，这种情况是最理想，所以在对表经常更新，删除后要重建索引。