重建索引一定能提高索引儲存空間利用率和效率嗎？

    在日常交流中經常聽到一個錯誤的觀點，資料庫索引需要重建，理由是重建索引至少能有以下好處：

     1、索引的重建，即使不能提高效能，也可以壓縮索引的儲存空間，使得單位索
           引資料塊所包含的索引更多，效率更高；
 
      2、節省資料庫的儲存空間

       但實際上，我們不要盲目的重建索引，有可能重建索引造成索引的儲存空間佔用更大，效率更低。所以重建以前，最好對索引進行分析，檢視索引的當前情況，以確定是否要重建。

      以下是Richard Foote的三個具有代表性的實驗：

     實驗一：建立一個普通表，表的數值是連續

         SQL>create table test1(id number);
        
         SQL>create index test1_idx on test1(id);
        
         SQL>insert into test1 select rownum from dual connect by level<=1000000;
  
         SQL>commit;

         SQL>analyze index test1_idx validate structure;

         SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;

            BLOCKS             LF_BLKS          BTREE_SPACE         PCT_USED
           ------------------    -------------------   --------------------------  ---------------------
                 2048                   1999                   16016116                    100

          以上PCT_USED已經達到的最佳狀態100%，如果對索引重建，會增大索引的儲存空間：

         SQL>alter index test1_idx rebuild;

         SQL>analyze index test1_idx validate structure;

         SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;

            BLOCKS             LF_BLKS          BTREE_SPACE         PCT_USED
           ------------------    -------------------   --------------------------  ---------------------
                2304                     2226                       17839236             90

   實驗二：建立小表，生成隨機數

          SQL>create table test2 (id number);
         
          SQL>insert into test2 select ceil(dbms_random.value(0, 100000))
                     from dual
                     connect by level<=1000000;
         
           SQL>commit;

           SQL>create index test2_idx on test2(id);

           SQL>insert into test2 select ceil(dbms_random.value(1,100000))
                      from dual connect by level<=50000;
    
           SQL>commit;

           SQL>analyze index test2_idx validate structure;

           SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;

            BLOCKS             LF_BLKS          BTREE_SPACE         PCT_USED
            -------------------- -------------------- ----------------------- -----------------------
                  2304                    2216                     17767304               95

         我們對索引重建，也將增加索引空間的使用：

          SQL>alter index test2_idx rebuild;

         SQL>analyze index test2_idx validate structure;

         SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;

            BLOCKS             LF_BLKS          BTREE_SPACE         PCT_USED
           ------------------    -------------------   --------------------------  ---------------------
                   2304                  2226                     17839236                 90
      
     實驗三：與實驗二相類似，但增加了一個批量的update，我們通常認為update後會造成索引空間的浪費，所以需要重建索引，能有效降低索引的儲存空間，但實際上，並不是這麼回事。

        SQL>create table test3 (id number, value number);
      
        SQL>insert into test3 select rownum, ceil(dbms_random.value(0, 100000))
                   from dual connect by level<=100000;

        SQL>commit;

        SQL>create index test3_idx on test3(value);
 
        SQL>insert into test3 select rownum+100000,
                  ceil(dbms_random.value(0,100000)) from dual
                   connect by level <=5000;

         SQL>commit;
   
        
    執行以下儲存過程，更新約為10%的資料
         begin
             for i in 1..10500 loop
                   update test3 set value =ceil(dbms_random.value(0,100000))
                   where id=i;
                 commit;
              end loop;
          end;
       
        SQL>analyze index test3_idx validate structure;

       SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;

            BLOCKS             LF_BLKS          BTREE_SPACE         PCT_USED
           ------------------    -------------------   --------------------------  ---------------------
                  256                         223                  1791136                   94     
        SQL>select del_lf_rows from index_stats;
           DEL_LF_ROWS
            -----------
                  110

     我們更新了約10%的行（10500），但實際上索引儲存空間標識為刪除的110行，證明了大部分索引空間被重用。

    重新建立索引，增加索引的儲存空間:
         SQL>alter index test3_idx rebuild;

         SQL>analyze index test3_idx validate structure;

        SQL>select blocks, lf_blks, btree_space, pct_used from index_stats;
        BLOCKS             LF_BLKS          BTREE_SPACE         PCT_USED
        --------------------    -------------------   --------------------------  ---------------------
                256                       233                    1871096                       90
      

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-605540/，如需轉載，請註明出處，否則將追究法律責任。