重建索引一定能提高索引存储空间利用率和效率吗？

    在日常交流中经常听到一个错误的观点，数据库索引需要重建，理由是重建索引至少能有以下好处：

     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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/354732/viewspace-605540/