rebuild索引，違背直覺得實驗結果

wei-xh發表於2011-12-27
Rebuild索引
Prompt ================================
prompt
prompt Test 1.
prompt Rebuild before adding extra data
prompt
prompt ================================
table t1(n1 number(38));
Table created.
index i1 on t1(n1);
Index created.
>
>
sys@SMART>execute dbms_random.seed(0)
PL/SQL procedure successfully completed.
>

  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  .

PL/SQL procedure successfully completed.

index i1 validate structure;

Index analyzed.

  2     'Leaf blocks before rebuild: ' || lf_blks       leaf_blocks
  3  from       index_stats;
Leaf
Blks
--------------------------------------------------------------------
Leaf blocks before rebuild: 1472
1 row selected.

index i1 rebuild pctfree 10;

Index altered.
index i1 validate structure;

Index analyzed.

  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.
 

>

  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  .
PL/SQL procedure successfully completed.
>
>
index i1 validate structure;

Index analyzed.

  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 ================================
>
table t1;

Table dropped.

  2     begin           execute immediate 'purge recyclebin';
  3     exception       when others then null;
  4     end;
  5  end;
  6  /
PL/SQL procedure successfully completed.
>
table t1(n1 number(38));
Table created.
index i1 on t1(n1);
Index created.
>
>
sys@SMART>execute dbms_random.seed(0)
PL/SQL procedure successfully completed.
>

  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  .

PL/SQL procedure successfully completed.

  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  .
PL/SQL procedure successfully completed.
index i1 validate structure;
Index analyzed.

  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.
index i1 rebuild pctfree 10;
Index altered.
index i1 validate structure;
Index analyzed.

  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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章