1.测试思想：
分别创建两个相同的表rebuild和norebuild，在每个表的id列上创建索引，对两个表进行同样的delete操作，然后对rebuild表上的索引进行重建，norebuild表上的索引保持不变。开启2个session分别对这两个表进行相同的插入操作，对比产生的redo和索引块分裂的情况。
2.测试过程：
SQL> create table rebuild (id number) tablespace users;
Table created.
SQL> insert into rebuild select ceil(dbms_random.value(0,100000)) from dual connect by level <=200000;
200000 rows created.
SQL> commit;
Commit complete.
SQL> create index i_rebuild on rebuild(id) tablespace idx_2k;
Index created.
SQL> begin
2 for i in 1 .. 100000 loop
3 delete from rebuild where id=2*i;
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> alter index i_rebuild rebuild;
Index altered.
SQL> create table norebuild (id number) tablespace users;
Table created.
SQL> insert into norebuild select ceil(dbms_random.value(0,100000)) from dual connect by level <=200000;
200000 rows created.
SQL> commit;
Commit complete.
SQL> create index i_norebuild on norebuild(id) tablespace idx_2k;
Index created.
SQL> begin
2 for i in 1 .. 100000 loop
3 delete from norebuild where id=2*i;
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed
session 1:
SQL> select s.sid,n.name,s.value from v$mystat s,v$statname n where s.statistic#=n.statistic# and (n.name ='redo size' or n.name like '%split%');
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
156 redo size 0
156 leaf node splits 0
156 leaf node 90-10 splits 0
156 branch node splits 0
156 queue splits 0
SQL> insert into norebuild select ceil(dbms_random.value(0,100000)) from dual connect by level <=100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL> select s.sid,n.name,s.value from v$mystat s,v$statname n where s.statistic#=n.statistic# and (n.name ='redo size' or n.name like '%split%');
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
156 redo size 25146860
156 leaf node splits 213
156 leaf node 90-10 splits 0
156 branch node splits 14
156 queue splits 0
session 2:
SQL> select s.sid,n.name,s.value from v$mystat s,v$statname n where s.statistic#=n.statistic# and (n.name ='redo size' or n.name like '%split%');
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
143 redo size 0
143 leaf node splits 0
143 leaf node 90-10 splits 0
143 branch node splits 0
143 queue splits 0
SQL> insert into rebuild select ceil(dbms_random.value(0,100000)) from dual connect by level <=100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL> select s.sid,n.name,s.value from v$mystat s,v$statname n where s.statistic#=n.statistic# and (n.name ='redo size' or n.name like '%split%');
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
143 redo size 30972348
143 leaf node splits 1273
143 leaf node 90-10 splits 0
143 branch node splits 16
143 queue splits 0
3.测试结论
在执行相同插入操作的情况下，重建索引之后产生了更多的redo日志和更多的索引块分裂。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-624720/ ，如需转载，请注明出处，否则将追究法律责任。
