问题一;重建后索引使用空间增大,如下测试:
SQL> desc bowie
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
ID NUMBER
SQL> truncate table bowie;
Table truncated.
Elapsed: 00:00:38.76
SQL> insert into bowie select rownum from dual connect by level <=1000000;
1000000 rows created.
Elapsed: 00:00:20.54
SQL> commit;
Commit complete.
Elapsed: 00:00:00.02
SQL> analyze index bowie_i validate structure;
Index analyzed.
Elapsed: 00:00:00.39
-----重建前索引相关信息
SQL> select blocks,lf_blks,btree_space,pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
---------- ---------- ----------- ----------
2048 1999 16016116 100
Elapsed: 00:00:00.14
SQL> alter index bowie_i rebuild;
Index altered.
Elapsed: 00:00:05.41
SQL> analyze index bowie_i validate structure;
Index analyzed.
Elapsed: 00:00:00.92
SQL> select blocks,lf_blks,btree_space,pct_used from index_stats;
BLOCKS LF_BLKS BTREE_SPACE PCT_USED
---------- ---------- ----------- ----------
2304 2226 17839236 90
Elapsed: 00:00:00.01
之所以重建后空间利用率下降,在于重建前索引块的空间是完全利用的,而重建后pct_free被设置为10,导致空间的浪费。
问题二:重建索引可能导致索引块分裂加剧
SQL> create table test (id number) tablespace users;
Table created.
SQL> create unique index idx_test on test(id) tablespace idx_2k;
Index created.
SQL> insert into test select rownum from dual connect by level<=200000;
200000 rows created.
SQL> commit;
Commit complete.
SQL> analyze index idx_test validate structure;
Index analyzed.
SQL> select blocks,lf_rows,lf_blks,del_lf_rows from index_stats;
BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- ---------- -----------
2048 200000 1621 0
SQL> begin
2 for i in 1 .. 100000 loop
3 delete from test where id=2*i;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> analyze index idx_test validate structure;
Index analyzed.
SQL> select blocks,lf_rows,lf_blks,del_lf_rows from index_stats;
BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- ---------- -----------
2048 200000 1621 100000
此时,对于索引idx_test,20万条记录中被删除了10万条,在这种情况下,再将删除的10万条记录插入,按照索引的结构原理,这些再次被插入的记录将被
放回原来的位置,也就是说,不会发生块分裂,进行如下测试来验证这个推测:
SQL> select n.name,s.value from v$statname n,v$mystat s where s.statistic#=n.statistic# and n.name like '%split%';
NAME VALUE
---------------------------------------------------------------- ----------
leaf node splits 0
leaf node 90-10 splits 0
branch node splits 0
queue splits 0
SQL> begin
2 for i in 1 .. 100000 loop
3 insert into test values(2*i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select n.name,s.value from v$statname n,v$mystat s where s.statistic#=n.statistic# and n.name like '%split%';
NAME VALUE
---------------------------------------------------------------- ----------
leaf node splits 0
leaf node 90-10 splits 0
branch node splits 0
queue splits 0
SQL> select blocks,lf_rows,lf_blks,del_lf_rows from index_stats;
BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- ---------- -----------
2048 200000 1621 0
可见,新插入的记录,恰好被放在了原来的位置,发生块分裂的次数为0,此时,索引的结构与执行delete操作之前是一致的。
接下来,再将插入的10万条记录删除,然后执行rebuild index操作,再插入10万条记录,然后查看索引块分裂的情况:
SQL> begin
2 for i in 1 .. 100000 loop
3 delete from test where id=2*i;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> analyze index idx_test validate structure;
Index analyzed.
SQL> select blocks,lf_rows,lf_blks,del_lf_rows from index_stats;
BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- ---------- -----------
2048 200000 1621 100000
SQL> alter index idx_test rebuild;
Index altered.
SQL> analyze index idx_test validate structure;
Index analyzed.
SQL> select blocks,lf_rows,lf_blks,del_lf_rows from index_stats;
BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- ---------- -----------
1024 100000 907 0
重建之后,索引的block数量减少为原来的一半,leaf block数量也降低到907,此时再次将原来删除的10万条记录插入,查看索引块分裂情况:
SQL> begin
2 for i in 1 .. 100000 loop
3 insert into test values(2*i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select n.name,s.value from v$statname n,v$mystat s where s.statistic#=n.statistic# and n.name like '%split%';
NAME VALUE
---------------------------------------------------------------- ----------
leaf node splits 1082
leaf node 90-10 splits 0
branch node splits 10
queue splits 0
SQL> analyze index idx_test validate structure;
Index analyzed.
SQL> select blocks,lf_rows,lf_blks,del_lf_rows from index_stats;
BLOCKS LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- ---------- -----------
2560 200000 1989 0
很明显,发生了1082次50-50的leaf block split,10次的branch block split,同时注意到,在重建索引并插入了10万条记录之后,该索引的block
数量达到了2560,leaf block 数量达到了1989,均大于不执行重建的情况,这意味着重建索引可能带来更大的空间浪费,而造成空间浪费的主要原因是由于
50-50的索引块分裂,导致数据块上的空间50%被浪费。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-623996/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-623996/