Update操作對索引的影響

        對於update操作，索引將對原先的資料進行刪除標記，然後增加一條新的記錄，因此很多人，包括我在內，就有一個錯誤觀念：大批量的update更新操作，就可能造成索引儲存空間浪費，我們需要定期對索引進行重建。實際上，當leaf block被標記為刪除記錄的時候，這些空間能被ORACLE自動清除和重用。
       
        以下是Richard Foote實驗步驟：

      1、建立表和索引
 
            SQL>create table test_update (id number,name varchar2(10));
 
            SQL>create index test_update_idx on test_update(name);
 
            SQL>insert into test_update values(1, 'BOWIE');

            SQL>commit;

       2、dump 索引資料塊
         
            SQL>select header_file, header_block
                      from dba_segments
                      where segment_name='TEST_UPDATE_IDX'
                   
              HEADER_FILE           HEADER_BLOCK
              -------------------------- ---------------------------
                          6                                   3355

              SQL>alter session set tracefile_identifier=yzb;
              SQL>alter system dump datafile 6 block 3356;
            
                檢視trace檔案：
                  ..........
                  kdxconro 1           （該資料塊索引有1行）
                     ..........
                  kdxlende 0             （該資料塊索引被刪除的是0行）
                 row#0[8017] flag: ------, lock: 2, len=15
                     col 0; len 5; (5):  42 4f 57 49 45
                     col 1; len 6; (6):  01 80 0d 17 00 00
           
           3、update表的資料，並dump索引資料塊資訊

               SQL>update test_update set name='ZIGG' where id=1;
               SQL>commit;
               SQL>alter system dump datafile 6 block 3356;

              檢視trace檔案：
                  ..........
                  kdxconro 2           （該資料塊索引有2行）
                     ..........
                  kdxlende 1             （該資料塊索引被刪除的是1行）
                  row#0[8017] flag: ---D--, lock: 2, len=15  （標識為刪除）
                       col 0; len 5; (5):  42 4f 57 49 45
                       col 1; len 6; (6):  01 80 0d 17 00 00
                   row#1[8003] flag: ------, lock: 2, len=14
                       col 0; len 4; (4):  5a 49 47 47
                       col 1; len 6; (6):  01 80 0d 17 00 00

               SQL>index test_update_idx validate structure;
               SQL>select del_lf_rows from index_stats;
           
                     DEL_LF_ROWS
                        ------------------
                                1
           
        4、插入一個新的資料
     
              SQL>insert into test_update value (2, 'PINK FLOYD');
              SQL>commit;
              SQL>alter system dump datafile 6 block 3356;
                
                 檢視trace檔案：
                  ..........
                  kdxconro 2           （該資料塊索引有1行）
                     ..........
                  kdxlende 0             （該資料塊索引被刪除的是0行）
                 row#0[7983] flag: ------, lock: 2, len=20
                     col 0; len 10; (10):  50 49 4e 4b 20 46 4c 4f 59 44
                     col 1; len 6; (6):  01 80 0d 17 00 01
                 row#1[8003] flag: ------, lock: 0, len=14
                    col 0; len 4; (4):  5a 49 47 47
                    col 1; len 6; (6):  01 80 0d 17 00 00
            
                 原先標記為刪除的行消失了。

                  SQL>analyze index test_update_idx validate structure;
                  SQL>select del_lf_rows from index_stats;
           
                     DEL_LF_ROWS
                        ------------------
                                0  
              
              


            

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