重建索引可能带来的问题（一）

最新推荐文章于 2022-07-26 11:20:27 发布

ciliting2867

最新推荐文章于 2022-07-26 11:20:27 发布

阅读量905

点赞数

问题一;重建后索引使用空间增大,如下测试：
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%';

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/