非唯一索引不為人所知的一個細微特徵

        如果表包含非唯一索引，在對錶進行批量delete+insert的時候，在delete完成後，必須先commit，再insert，或者是session在完成非唯一索引建立後，進行commit，否則非唯一索引需要重建。唯一索引沒有以上特性。
     
       實驗一：非唯一索引建立後未commit (delete+insert)
        SQL>create table test1 (id number, name varchar2(20));
       
        SQL>insert into test1 select rownum, 'TEST1'
                  from dual connect by level <=1000000;

        SQL>commit;

        SQL>create index test1_idx on test1(id);

        SQL>analyze index test1_idx validate structure;

        SQL>select blocks, lf_rows, lf_blks, del_lf_rows from index_stats;  
                  BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS
                   -------------- ---------------- ------------------ -------------------
                        2304      1000000          2226               0

         將表資料刪除，然後重新insert新的資料
       
         SQL>truncate table test1 (或者 delete test1)

         SQL>insert into test1 select rownum, 'LongTop'
                   from dual connect by level <= 1000000;

         SQL>commit;

         SQL>analyze index test1_idx validate structure;

         SQL>select blocks, lf_blks, del_lf_rows from index_stats;
                  BLOCKS    LF_BLKS DEL_LF_ROWS
                  -------------------- ---------- -----------------
                      4736       4520     1000000
         
        索引的儲存空間增加一倍，索引刪除空間沒有被重用，需要對索引進行重建。

       實驗二：非唯一索引 建立後未commit(delete+commit+insert)

       SQL>create table test1 (id number, name varchar2(20));
       
        SQL>insert into test1 select rownum, 'TEST1'
                  from dual connect by level <=1000000;

        SQL>commit;

        SQL>create index test1_idx on test1(id);

        SQL>analyze index test1_idx validate structure;

        SQL>select blocks, lf_rows, lf_blks, del_lf_rows from index_stats;  
                  BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS
                   -------------- ---------------- ------------------ -------------------
                        2304      1000000          2226               0

         將表資料刪除，然後commit，再重新insert新的資料
       
         SQL> truncate table test1
  
          SQL>commit;

         SQL>insert into test1 select rownum, 'LongTop'
                   from dual connect by level <= 1000000;

         SQL>commit;

         SQL>analyze index test1_idx validate structure;

         SQL>select blocks, lf_blks, del_lf_rows from index_stats;
                  BLOCKS    LF_BLKS DEL_LF_ROWS
              -------------- ---------------- ------------------ -------------------
                       2048              1999           0

          發現blocks的數量比原先的更少。

      實驗三、非唯一索引建立後commit (delete+insert)

       SQL>create table test1 (id number, name varchar2(20));
       
        SQL>insert into test1 select rownum, 'TEST1'
                  from dual connect by level <=1000000;

        SQL>commit;

        SQL>create index test1_idx on test1(id);

        SQL>commit;

        SQL>analyze index test1_idx validate structure;

        SQL>select blocks, lf_rows, lf_blks, del_lf_rows from index_stats;  
                  BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS
                   -------------- ---------------- ------------------ -------------------
                        2304      1000000          2226               0

         將表資料刪除，然後重新insert新的資料
       
         SQL>truncate table test1 (或者 delete test1)

         SQL>insert into test1 select rownum, 'LongTop'
                   from dual connect by level <= 1000000;

         SQL>commit;

         SQL>analyze index test1_idx validate structure;

         SQL>select blocks, lf_blks, del_lf_rows from index_stats;
                  BLOCKS    LF_BLKS DEL_LF_ROWS
                  -------------------- ---------- -----------------
                          2048       1999           0

    實驗四、唯一索引（delete+insert）

        SQL>create table test2 (id number, name varchar2(20));

         SQL>insert into test2 select rownum, 'TEST2'
                    from dual connect by level <=1000000;       

         SQL>commit;
 
        SQL>create unique index test2_idx on test2(id);

         SQL>analyze index test2_idx validate structure;

         SQL>select blocks, lf_rows, lf_blks, del_lf_rows from index_stats;  
                  BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS
                   -------------- ---------------- ------------------ -------------------
                        2176    1000000           2088             0
        
           將表資料刪除，然後重新insert新的資料
        
           SQL> delete test2;

           SQL>insert into test2 select rownum, 'LongTop'
                     from dual connect by level <= 1000000;

            SQL>commit;

            SQL>analyze index test2_idx validate structure;

            SQL>select blocks, lf_blks, del_lf_rows from index_stats;
                  BLOCKS    LF_BLKS DEL_LF_ROWS
                  -------------------- ---------- -----------------
                       2176              2088            0
       

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