教你如何成為Oracle 10g OCP - 第九章 物件管理(7) - B樹索引的對於DELETE的管理

9.2.2.2  B樹索引的對於刪除（DELETE）的管理  

我們知道, 可以透過index_stats來檢視索引內部的資訊,此檢視正常情況下沒有
資料, 只有執行 analyze index INDEX_NAME validate structure; 後才會有,且
只有這個session能看到, 所以此檢視永遠只有小於等於一筆記錄.

  注意,該命令會鎖定整個表(TM鎖),從而阻塞其他session對此表的DML,這是因為
此命令主要不是用來填充index_stats檢視的，而是在於校驗索引中的每個有效的
索引條目都對應到表裡的一行，同時表裡的每一行資料在索引中都存在一個對應的
索引條目，所以需要鎖定整個表，對於很大的表，執行需要消耗很多時間。

   在檢視index_stats中：
height表示B樹索引的高度；
blocks表示分配了的索引塊數，包括還沒有被使用的；
pct_used表示當前索引中被使用了的空間的百分比。其值是根據檢視中的
(used_space/btree_space)*100計算而來；
del_lf_rows表示被刪除的記錄行數(表中資料被刪除，並不會立即將對應於索引
裡面的索引條目清除出索引塊，後面會講到)。
del_lf_rows_len 表示被刪除的記錄所佔的總空間。
lf_rows 表示索引中包含的總記錄行數，包括已經被刪除的記錄行數。這樣的話，
索引中未被刪除的記錄行數就是 lf_rows-del_lf_rows, 同時我們可以計算未被
刪除的記錄所對應的索引條目(即有效索引條目)所佔用的空間：
((used_space – del_lf_rows_len) / btree_space) * 100。

接著上節中的例子(最後插入了12*2的例子)來測試一下，我們知道，例子中的索引
具有兩個葉子節點，一個葉子節點(塊號419),包含10,12,14,16,18,20,22,24和2a,
另外一個葉子節點(塊號420)，含有4a,6a,8a。這時我們插入41,42,43,44,45,46,47,
48各8條記錄，可以知道這8條記錄對應的索引條目將會進入索引塊420中，從而420
塊被充滿。

SQL> begin
 2    for i in 1..8 loop
 3        insert into index_test values (rpad('4'||to_char(i),150,'a'));
 4    end loop;
 5 end;
 6 /

我們先分析索引從而填充index_stats檢視。

SQL> analyze index idx_test validate structure;

SQL> select LF_ROWS,DEL_LF_ROWS,DEL_LF_ROWS_LEN,USED_SPACE,BTREE_SPACE
     from index_stats;

     LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN USED_SPACE BTREE_SPACE
---------- ----------- --------------- ---------- -----------
       20          0              0      3269       5600

   從上面檢視可以看到，當前索引共20條記錄，沒有被刪除的記錄，共使用了3269
個位元組。然後我們刪除位於索引塊419裡面的索引條目，包括10，12，14，16等4條記
錄。

然後我們刪除位於索引塊419裡的索引條目，包括10、12、14、16各4條記錄。
SQL> delete index_test where substr(id,1,2) in('10','12','14','16');
SQL> commit;
SQL> alter system dump datafile 7 block 419;

  開啟轉儲出來的檔案可以發現如下的內容（我們節選了部分關鍵內容）。可以
發現kdxconro為9，說明該索引節點裡還有9個索引條目。所以說，雖然表裡的數
據被刪除了，但是對應的索引條目並沒有被刪除，只是在各個索引條目上（row#
一行中的flag為D）做了一個'D'的標記，表示該索引條目被delete了。

kdxconro 9
row#0[443] flag: ---D-, lock: 2
row#1[604] flag: ---D-, lock: 2
row#2[765] flag: ---D-, lock: 2
row#3[926] flag: ---D-, lock: 2

然後我們再以樹狀結構轉儲索引，開啟樹狀轉儲跟蹤檔案可以看到如下內容。塊
419中還是包含9個索引索引條目(nrow為9),而有效索引條目只有5個(rrow為5)，
那麼被刪除的索引條目就是4個(9減5);

SQL> alter session set events 'immediate trace name treedump level 7390';

----- begin tree dump
branch: 0x1c001a2 29360546 (0: nrow: 2, level: 1)
  leaf: 0x1c001a3 29360547 (-1: nrow: 9 rrow: 5)
  leaf: 0x1c001a4 29360548 (0: nrow: 11 rrow: 11)
----- end tree dump

再次分析索引，填充index_stats檢視。
SQL> analyze index idx_test validate structure;
SQL> select LF_ROWS,DEL_LF_ROWS,DEL_LF_ROWS_LEN,USED_SPACE,BTREE_SPACE
     from index_stats;

     LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN USED_SPACE BTREE_SPACE
---------- ----------- --------------- ---------- -----------
       20          4            652      3269       5600

對照刪除前檢視裡的資訊，很明顯看到，當前索引(條目)仍然是20條
記錄，但是其中有4條是刪除的，且索引使用的空間並沒有釋放被刪除
記錄所佔用的空間(652Bytes), 仍然是刪除前的3269個位元組。

刪除結束後，我們接下來測試一下插入一條記錄，索引會發生什麼變化。
分為3種情況：
A. 插入屬於原來被刪除鍵值範圍內的值，比如13,觀察如何進入包含設
置了刪除標記的索引塊 ；
B. 插入原來被刪除的鍵值中的一個，比如16，觀察是否能重新使用原來
的索引條目；
C. 插入一個完全不屬於該表中已有記錄的範圍的值，比如rpad('M',150,'M')，
觀察其對塊419以及420會產生什麼影響。

我們測試第一種情況(插入屬於原來被刪除鍵值範圍內的值13)：

SQL> insert into index_test values (rpad(to_char(13),150,'a'));
SQL> alter system dump datafile 7 block 419;

      開啟跟蹤檔案以後會發現419塊裡的內容發生了變化，如下所示。我們
可以發現一個很有趣的現象，從kdxconro為6說明插入了鍵值13以後，導致原來
四個被標記為刪除的索引條目都被清除出了索引塊。同時，我們也確實發現原來
標記為D的四個索引條目都消失了。

……
kdxconro 6
……
kdxlende 0
……
row#0[121] flag: -----, lock: 2   被插入13
col 0; len 150; (150):
 31 33 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
……

我們分析索引，看看index_stats檢視會如何變化。

SQL> analyze index idx_test validate structure;
SQL> select LF_ROWS,DEL_LF_ROWS,DEL_LF_ROWS_LEN,USED_SPACE,BTREE_SPACE
     from index_stats;

  LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN USED_SPACE BTREE_SPACE
---------- ----------- --------------- ---------- -----------
       17          0              0      2780       5600

      很明顯，原來的del_lf_rows從4變為了0，同時used_space也從原來的3269
變成了2780。表示原來被刪除的索引條目所佔用的空間已經釋放了。

我們繼續測試第二種情況(插入刪除的鍵值'16')：

SQL> insert into index_test values (rpad(to_char(8*2),150,'a'));
SQL> alter system dump datafile 7 block 419;

   開啟跟蹤檔案以後，發現對於插入已經被標記為刪除的記錄來說，其過程
與插入屬於該索引塊索引範圍的鍵值的過程沒有區別。甚至你會發現，被插入
的16的鍵值所處的位置與插入的13的鍵值所在的位置完全一樣（row#0[121]裡
的121表示在索引塊中的位置）。也就是說，oracle並沒有重用原來為16的鍵值，
而是直接將所有標記為D的索引條目清除出索引塊，然後插入新的鍵值為16的索
引條目。

    對於第三種情況，我們已經可以根據前面有關第一、第二種情況做出預測，
由於420塊已經被充滿，同時所插入的鍵值是整個表裡的最大值，因此也不會因
此420號塊發生分裂，而是直接獲取一個新的索引塊來存放該鍵值。但是419號
塊裡標記為D的索引條目是否能被清除出索引塊呢？

SQL> insert into index_test values (rpad('M',150,'M'));
SQL> alter system dump datafile 7 block 419;
SQL> alter system dump datafile 7 block 420;
SQL> alter system dump datafile 7 block 421;

      開啟跟蹤檔案，可以清楚的看到，419號塊裡的標記為D的4各索引條目仍然
保留在索引塊裡，同時420號塊裡的內容沒有任何變化，而421號塊裡則存放了新
的鍵值：rpad('M',150,'M')。

我們看看index_stats檢視會如何變化。其結果也符合我們從轉儲檔案中所看到的內容。

SQL> analyze index idx_test validate structure;
SQL> select LF_ROWS,DEL_LF_ROWS,DEL_LF_ROWS_LEN,USED_SPACE,BTREE_SPACE
     from index_stats;

  LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN USED_SPACE BTREE_SPACE
---------- ----------- --------------- ---------- -----------
       21          4            652      3441       7456

      既然當插入rpad('M',150,'M')時對419號塊沒有任何影響，不會將標記為D
的索引條目移出索引塊。那麼如果我們事先將419號索引塊中所有的索引條目都標
記為D，也就是說刪除419號索引塊中索引條目所對應的記錄，然後再次插入rpad
('M',150,'M')時會發生什麼？ 
     透過測試，我們可以發現，再次插入一個最大值以後，該最大值會進入塊421裡，
但是塊419裡的索引條目則會被全部清除，變成了一個空的索引資料塊。這也就是我們
通常所說的，當索引塊裡的索引條目全部被設定為D(刪除)標記時，再次插入任何一個
索引鍵值都會引起該索引塊裡的內容被清除。

      最後，我們來測試一下，當索引塊裡的索引條目全部被設定為D（刪除）標記
以後，再次插入新的鍵值時會如何重用這些索引塊。我們先建立一個測試表，並插入
10000條記錄。

SQL> create table delete_test(id number);

SQL> begin
 2    for i in 1..10000 loop
 3        insert into delete_test values (i);
 4    end loop;
 5    commit;
 6 end;
 7 /

SQL> create index idx_delete_test on delete_test(id);

SQL> analyze index idx_delete_test validate structure;

SQL> select LF_ROWS,LF_BLKS,DEL_LF_ROWS,USED_SPACE,BTREE_SPACE from index_stats;

  LF_ROWS   LF_BLKS DEL_LF_ROWS USED_SPACE BTREE_SPACE
---------- ---------- ----------- ---------- -----------
    10000        21          0    150021     176032

   可以看到，該索引具有21個葉子節點。然後我們刪除前9990條記錄。從而使得
21個葉子節點中只有最後一個葉子節點具有有效索引條目，前20個葉子節點裡的索
引條目全都標記為D（刪除）標記。

SQL> delete delete_test where id >= 1 and id <= 9990;
SQL> commit;

SQL> analyze index idx_delete_test validate structure;
SQL> select LF_ROWS,LF_BLKS,DEL_LF_ROWS,USED_SPACE,BTREE_SPACE from index_stats;

  LF_ROWS   LF_BLKS DEL_LF_ROWS USED_SPACE BTREE_SPACE
---------- ---------- ----------- ---------- -----------
    10000        21       9990   150021     176032

最後，我們插入從20000開始到30000結束，共10000條與被刪除記錄完全不重疊的記錄。

SQL> begin
 2    for i in 20000..30000 loop
 3        insert into delete_test values (i);
 4    end loop;
 5    commit;
 6 end;
 7 /

SQL> analyze index idx_delete_test validate structure;

SQL> select LF_ROWS,LF_BLKS,DEL_LF_ROWS,USED_SPACE,BTREE_SPACE from index_stats;

  LF_ROWS   LF_BLKS DEL_LF_ROWS USED_SPACE BTREE_SPACE
---------- ---------- ----------- ---------- -----------
    10011        21          0    160302     176032

  很明顯的看到，儘管被插入的記錄不屬於被刪除的記錄範圍，但是隻要索引塊中所有
的索引條目都被刪除了(標記為D)，該索引就變成可用索引塊而能夠被新的鍵值重新利用了。

 

   因此，根據上面我們所做的試驗，可以對索引的刪除情況總結如下：

1). 當刪除表裡的一條記錄時，其對應於索引裡的索引條目並不會被物理的刪除(空間
還是被佔用)，只是做了一個刪除標記('D')。

2). 當一個新的索引條目進入一個索引葉子節點的時候，oracle會檢查該葉子節點裡是
否存在被標記為刪除的索引條目，如果存在，則會將所有具有刪除標記的索引條目從該
葉子節點裡物理的刪除。

3). 當一個新的索引條目(鍵值)插入索引時，這個值不屬於該表中有記錄的範圍的值，
比如插入的是整表的最大值，那麼不會將標記為'D'的索引條目移出索引塊(特例見第
4條)。

4). 如果有所有索引條目都標記為'D'的索引塊的話，再次插入任何一個索引鍵值都會
引起這些索引塊(所有索引條目都標記為'D')裡的內容被清除。

5). 當一個新的索引條目進入索引時，oracle會將當前所有被清空的葉子節點（該葉子
節點中所有的索引條目都被設定為刪除'D'標記）收回，從而再次成為可用索引塊,能
被重新使用了。

 

索引碎片問題 --

儘管被刪除的索引條目所佔用的空間大部分情況下都能夠被重用，但仍然存在一些情況
可能導致索引空間被浪費，並造成索引資料塊很多但是索引條目很少的後果，這時該索
引可以認為出現碎片。而導致索引出現碎片的情況主要包括：

1). 不合理的、較高的PCTFREE。很明顯，這將導致索引塊的可用空間減少。

2). 索引鍵值持續增加（比如採用sequence生成序列號的鍵值），同時對索引鍵值按
照順序連續刪除，這時可能導致索引碎片的發生。因為前面我們知道，某個索引塊中
刪除了部分的索引條目，只有當有鍵值進入該索引塊時才能將空間收回。而持續增加
的索引鍵值永遠只會插入排在前面的索引塊中，因此這種索引裡的空間幾乎不能收回，
而只有其所含的索引條目全部刪除時，該索引塊才能被重新利用。

3). 經常被刪除或更新的鍵值，以後幾乎不再會被插入時，這種情況與上面的情況類似。
對於如何判斷索引是否出現碎片，方法非常簡單：直接執行ANALYZE INDEX … VALIDATE
STRUCTURE命令，然後檢查index_stats檢視的pct_used欄位，如果該欄位過低(低於50％)，
則說明存在碎片。

 

9.2.2.3  B樹索引對於更新（UPDATE）的管理

而對於值被更新對於索引條目的影響，則可以認為是刪除和插入的組合。也就是
將被更新的舊值對應的索引條目設定為D（刪除）標記，同時將更新後的值按照順
序插入合適的索引塊中。這裡就不重複討論了。