ITPub博客

首页 > Linux操作系统 > Linux操作系统 > rebuild索引,违背直觉得实验结果

rebuild索引,违背直觉得实验结果

原创 Linux操作系统 作者:wei-xh 时间:2011-12-27 14:15:13 0 删除 编辑
Prompt ================================
prompt
prompt Test 1.
prompt Rebuild before adding extra data
prompt
prompt ================================
sys@SMART>create table t1(n1 number(38));
Table created.
sys@SMART>create index i1 on t1(n1);
Index created.
sys@SMART>
sys@SMART>
sys@SMART>execute dbms_random.seed(0)
PL/SQL procedure successfully completed.
sys@SMART>
sys@SMART>begin
  2     for i in 1..400000 loop
  3             insert into t1 values(
  4                     trunc(power(10,14) * dbms_random.value)
  5             );
  6             commit;
  7     end loop;
  8  end;
  9  .
sys@SMART>/

PL/SQL procedure successfully completed.

sys@SMART>analyze index i1 validate structure;

Index analyzed.
sys@SMART>sys@SMART>select
  2     'Leaf blocks before rebuild: ' || lf_blks       leaf_blocks
  3  from       index_stats;
Leaf
Blks
--------------------------------------------------------------------
Leaf blocks before rebuild: 1472
1 row selected.

sys@SMART>alter index i1 rebuild pctfree 10;

Index altered.
sys@SMART>sys@SMART>analyze index i1 validate structure;

Index analyzed.
sys@SMART>sys@SMART>select
  2     'Leaf blocks immediately after rebuild: ' || lf_blks    leaf_blocks
  3  from       index_stats;
Leaf
Blks
-------------------------------------------------------------------------------
Leaf blocks immediately after rebuild: 1114
1 row selected.
 

sys@SMART>
sys@SMART>begin
  2     for i in 1..100000 loop
  3             insert into t1 values(
  4                     trunc(power(10,14) * dbms_random.value)
  5             );
  6             commit;
  7     end loop;
  8  end;
  9  .
sys@SMART>/
PL/SQL procedure successfully completed.
sys@SMART>
sys@SMART>
sys@SMART>analyze index i1 validate structure;

Index analyzed.
sys@SMART>sys@SMART>select
  2     'Leaf blocks after further processing: ' || lf_blks     leaf_blocks
  3  from       index_stats;
Leaf
Blks
------------------------------------------------------------------------------
Leaf blocks after further processing: 2227
1 row selected.

prompt
Prompt ================================
prompt
prompt Test 2:
prompt Without rebuilding.
prompt
prompt ================================
sys@SMART>
sys@SMART>drop table t1;

Table dropped.
sys@SMART>sys@SMART>begin
  2     begin           execute immediate 'purge recyclebin';
  3     exception       when others then null;
  4     end;
  5  end;
  6  /
PL/SQL procedure successfully completed.
sys@SMART>
sys@SMART>create table t1(n1 number(38));
Table created.
sys@SMART>create index i1 on t1(n1);
Index created.
sys@SMART>
sys@SMART>
sys@SMART>execute dbms_random.seed(0)
PL/SQL procedure successfully completed.
sys@SMART>
sys@SMART>begin
  2     for i in 1..400000 loop
  3             insert into t1 values(
  4                     trunc(power(10,14) * dbms_random.value)
  5             );
  6             commit;
  7     end loop;
  8  end;
  9  .
sys@SMART>/

PL/SQL procedure successfully completed.
sys@SMART>sys@SMART>begin
  2     for i in 1..100000 loop
  3             insert into t1 values(
  4                     trunc(power(10,14) * dbms_random.value)
  5             );
  6             commit;
  7     end loop;
  8  end;
  9  .
sys@SMART>/
PL/SQL procedure successfully completed.
sys@SMART>analyze index i1 validate structure;
Index analyzed.
sys@SMART>select
  2     'Leaf blocks with no intermediate rebuild: ' || lf_blks leaf_blocks
  3  from       index_stats;
Leaf
Blks
----------------------------------------------------------------------------------
Leaf blocks with no intermediate rebuild: 1777
1 row selected.
sys@SMART>alter index i1 rebuild pctfree 10;
Index altered.
sys@SMART>analyze index i1 validate structure;
Index analyzed.
sys@SMART>select
  2     'Leaf blocks at end of test: ' || lf_blks       leaf_blocks
  3  from       index_stats;
Leaf
Blks
--------------------------------------------------------------------
Leaf blocks at end of test: 1393
1 row selected.

实验的结果似乎违背的直觉。
第一个测试案例里:
1)我们先进行了一次数据的集中插入。完成后,索引的大小是1472个block.这个操作非常接近我们现实的情况。
2)然后我们重建了索引,索引大小降低到1114。
3)我们重新往表里插入了一批数据。这个时候索引的大小是2227。
第二个测试案例里:
1)跟案例一一样,进行了一次数据的集中插入。
2)不重建索引,直接再次进行一次数据插入,同案例一里面的步骤3.这个时候索引的大小是1777。竟然比案例一里的2227低。
这个违背直觉的实验,我想主要是因为索引重建后,导致索引的分裂比重建前更加的频繁。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-714000/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2285441