问题一;重建后索引使用空间增大,如下测试：
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博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-623996/ ，如需转载，请注明出处，否则将追究法律责任。
