[一篇好文章]关于索引重用空间的总结测试

于 2007-08-28 10:19:00 发布
阅读量697 收藏
点赞数
文章标签： sql insert structure 测试 ext delete
本文通过多个实验探讨了Oracle数据库中索引空间的重用机制，包括不同情况下的索引空间重用、索引空间自动回收及索引中“空洞”空间的重新使用等关键知识点。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

关于索引重用空间的总结测试：
因为对于这方面的知识不是很了解，就做了些测试，希望大家讨论一下！
如果我的观点有误，请大家热心指正！

1。索引空间重用的一个例子
观点：一旦索引使用过存储区，就会永远在那里，并且只能被相同的值重用。（由此推测，空闲空间永远不会返回索引结构，块永远不会重用）
结论：这种观点是错误的，测试如下。
1)创建测试表：
SQL> create table t(x int,constraint t_pk primary key(x));

表已创建。

SQL> insert into t values(1);

已创建 1 行。

SQL> insert into t values(2);

已创建 1 行。

SQL> insert into t values(999999);

已创建 1 行。
SQL> set serveroutput on
SQL> exec show_space('T_PK',user,'INDEX');
Free Blocks.............................0
Total Blocks............................4
Total Bytes.............................32768
Unused Blocks...........................2
Unused Bytes............................16384
Last Used Ext FileId....................5
Last Used Ext BlockId...................634
Last Used Block.........................2

PL/SQL 过程已成功完成。

2)根据上面的观点，如果从表T中删除X=2，那个空间就永远不会重用，除非再次插入数字2。目前这个索引使用2个空间块，一个盘区地图（extent map），一个是索引数据。如果删除数据后索引条目从来不会重用，那么如果连续不断的插入和删除，并且从来不用相同的值，索引应该不断的增大。下面继续测试
SQL> begin
2 for i in 2..9999
3 loop
4 delete from t where x=i;
5 commit;
6 insert into t values(i+1);
7 commit;
8 end loop;
9 end;
10 /

PL/SQL 过程已成功完成。
SQL> exec show_space('T_PK',user,'INDEX');
Free Blocks.............................0
Total Blocks............................4
Total Bytes.............................32768
Unused Blocks...........................2
Unused Bytes............................16384
Last Used Ext FileId....................5
Last Used Ext BlockId...................634
Last Used Block.........................2

PL/SQL 过程已成功完成。

索引的空间大小没有变化，得出的结论表明索引中的空间重用了。
因为索引能够插入到适合的地方，如上面的例子，当数据2删除后，数据2的slot可以被所有位于1－9999之间的数据使用。当数据3插入后删除了，它的slot可以被所有位于1－9999之间的数据使用，这样就实现了索引的空间重用。（这一部分一开始不理解，后来看了asktom上tom的书的英文说明，才明白了，看来expert one on one翻译成了中文确实逊色很多）


 

2。索引空间重用的另外一个例子
索引空间从来不会返回到FREELIST上，直到块上所有的entries都被释放（通过delete，removed等），但是索引块上的空闲空间可以得到最大的使用。
1）创建测试表和测试数据
SQL> create table emp(empno int constraint emp_pk primary key,
2 ename varchar2(30));

表已创建。

SQL> insert into emp select rownum, username from
2 all_users;

已创建13行。

SQL> analyze index emp_pk validate structure;

索引已分析

SQL> select lf_rows, del_lf_rows from index_stats;

LF_ROWS DEL_LF_ROWS
---------- -----------
13 0

这里说明有13个叶子节点－－－13个索引行

2）删除一部分数据
SQL> delete from emp where empno<=10;

已删除10行。

SQL> analyze index emp_pk validate structure;

索引已分析

SQL> select lf_rows, del_lf_rows from index_stats;

LF_ROWS DEL_LF_ROWS
---------- -----------
13 10

这里说明叶子节点还是13个，其中10个已经被标注“删除”了－－－在表中这些删除的叶子节点已经不指向实际的行

3）插入一部分数据
SQL> insert into emp
2 select rownum,lower(username)
3 from all_users
4 where rownum<=10;

已创建10行。

SQL> analyze index emp_pk validate structure;

索引已分析

SQL> select lf_rows, del_lf_rows from index_stats;

LF_ROWS DEL_LF_ROWS
---------- -----------
13 0

这里显示叶子节点为13个，但是没有标注“删除”的叶子节点了，说明空间重用了。

4）继续测试，使用不同的值来重用索引空间
SQL> delete from emp where empno <= 10;

已删除10行。

SQL> commit;

提交完成。

SQL> insert into emp select -rownum,
2 lower(username) from all_users where rownum <= 10;

已创建10行。

SQL> analyze index emp_pk validate structure;

索引已分析
SQL> select lf_rows, del_lf_rows from index_stats;

LF_ROWS DEL_LF_ROWS
---------- -----------
13 0

这里删除后使用不同的值来插入，但是空间还是重用了。
当一行数据能够使用索引中的slot时，索引块的空间马上就被重用了。
而且这种情况下是不使用FREELIST的（当索引BUILT或者REBUILT后，对于索引来说就不再使用FREELIST了）


3。索引空间不重用的情况
那么什么时候索引中的空间不重用那，是在下面这种索引有空洞的情况下：
在上面的情况下，原始的数字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 过程已成功完成。

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


4。关于索引空间自动回收的例子
观点：索引空间从来不会自动“重新收回”，讲的是一旦一个索引块使用，就会永远粘在索引结构的那个空间，并且只有插入的数据进入索引的那个位置时，它才会重用。
结论：另一方面，如果使用1－10000的值装载一张表，然后删除其中值等于或小于5000的行，会发现从索引中清除的块重新放到了索引的FREELIST中，这个空间可以完全重用。

1）创建测试表和测试数据，加入测试数据17920行，然后删除连续的一半数据。
SQL> create table t(x int);

表已创建。

SQL> insert /*+ APPEND*/ into t select rownum from all_objects;

已创建4480行。
SQL> commit;

提交完成。

SQL> insert /*+ APPEND*/ into t
2 select rownum+cnt from t,(select count(*) cnt from t);

已创建4480行。
SQL> commit;

提交完成。
SQL> insert /*+ APPEND*/ into t
2 select rownum+cnt from t,(select count(*) cnt from t);

已创建8960行。
SQL> commit;

提交完成。

SQL> select count(*) from t;

COUNT(*)
----------
17920


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............................40
Total Bytes.............................327680
Unused Blocks...........................1
Unused Bytes............................8192
Last Used Ext FileId....................5
Last Used Ext BlockId...................546
Last Used Block.........................3

PL/SQL 过程已成功完成。

SQL> DELETE FROM T WHERE X<8960;

已删除8959行。

SQL> COMMIT;

提交完成。

SQL> exec show_space('T_PK',user,'INDEX');
Free Blocks.............................17
Total Blocks............................40
Total Bytes.............................327680
Unused Blocks...........................1
Unused Bytes............................8192
Last Used Ext FileId....................5
Last Used Ext BlockId...................546
Last Used Block.........................3

PL/SQL 过程已成功完成。

可以看到，现在大约有一半的索引在FREELIST上，这意味着块是空的（对索引FREELIST上的块必须是空的，不像堆组织表中FREELIST上的块）。

这主要证明了两点：
1）只要出现的行能重用，索引块上的空间就可以重用。
2）但一个索引块为空时，从索引结构中取走，以后可以重用。
当块上有“空闲空间”并且在索引结构的表中时，块时不可见的。在表中，可以看到空闲空间上的块，即使其中有数据。在索引中，只能看到FREELIST上完全空的块。块上只要有一个索引条目（留有空闲空间）就不能清楚的看到。


5。对于索引中“空洞”空间的重新使用
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

SQL> alter table t add constraint t_pk primary key(x);

表已更改。
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...................626
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...................626
Last Used Block.........................2

PL/SQL 过程已成功完成。

使用了22个block。

2）现在有5000条数据的空洞，插入范围在1－10000之外的数据5000条，理论上应该不能使用“空洞”的空间
SQL> begin
2 for i in 10001..15000 loop
3 insert into t values(i);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL 过程已成功完成。

SQL> select count(*) from t;

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

SQL> commit;

提交完成。
SQL> exec show_space('T_PK',user,'INDEX');
Free Blocks.............................2
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................5
Last Used Ext BlockId...................46
Last Used Block.........................4

PL/SQL 过程已成功完成。

使用了30个block，可以看到这里没有使用空洞的空间。

3）插入符合“空洞”范围的数据，看看是否能利用空间。
SQL> begin
2 for i in 1..10000 loop
3 if mod(i,2)=0 then
4 insert into t values(i);
5 end if;
6 end loop;
7 end;
8 /

PL/SQL 过程已成功完成。

SQL> select count(*) from t;

COUNT(*)
----------
15000

SQL> commit;

提交完成。
SQL> exec show_space('T_PK',user,'INDEX');
Free Blocks.............................1
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................5
Last Used Ext BlockId...................46
Last Used Block.........................4

PL/SQL 过程已成功完成。

使用了31个block，可以看到当插入这些符合“空洞”条件的数据时，“空洞”的空间被重用了。


shongyu
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值