教你如何成為Oracle 10g OCP - 第九章 物件管理(5) - 索引

9.2 索引

9.2.1  B樹(Balance Tree)索引

1. 索引與表一樣也屬於一種段，存放了使用者資料，需要磁碟空間，只是存放形式
與表中不一樣，索引是一個有序排列的結構 。 雖然建立索引對於查詢有好處，
但是建立索引後對於insert,update, delete 操作而言，增加了額外的工作量，
他們在操作時需要對索引結構進行維護，所以建立索引時要權衡查詢與DML及
儲存空間等各方面的問題。

2. 索引分類
從物理上來看，索引可以分為分割槽和非分割槽索引，常規B-Tree索引，點陣圖(Bitmap)
索引，反轉(Reverse)索引，其中B-Tree是最常見的索引。本章中提到的索引，沒有
特別申明，都是指b-tree索引。

3. B-Tree索引結構

葉子節點(leaf node): 包含的條目中具有指標，指向表中的資料行，葉子節點之間
相互指向。只要表中記錄行中被索引的列的值不為空，就會在索引葉子節點裡面存在
一個對應的條目，如果被索引的列為空，那麼在葉子節點中不會存在對應的條目。

分支節點(branch node): 包含的條目指向索引裡其他的分支節點或葉子節點。

根節點(root node):  一個b-tree索引只有一個根節點，本質來講，它就是位於
樹頂端的分支節點。

備註： 索引條目 -- 索引資料塊中的資料行(包含部分資料及索引塊地址)。

對於分支節點塊(branch node), 包括根節點塊，所包含的索引條目都是按照順序排
列的(預設是升序排列的，當然也可以在建索引時指定為降序)，每個索引條目(也可
以叫做每條記錄)都有兩個欄位，第一個欄位表示當前該分支節點下所連結的索引塊中
所包含的最小鍵值 (鍵值：也就是索引所在列的值)； 第二個欄位為四個位元組，表示
所連結的索引塊的地址，該地址指向下面其所連結的索引塊。

注意：這裡的所謂索引塊，分支節點塊或葉子節點塊就是Oracle物理block。 
每個節點塊=oracle block size.  在一個分支節點塊中所能容納的記錄行數
由資料塊大小(即Oracle Block Size)以及索引鍵值(索引所在列的值)的長度決定。

例子圖片見
http://space.itpub.net/batch.download.php?aid=4959  

從圖中可以看到地址部分B1,B2,B3表示對應branch,L1,L2,L3表示對應leaf,而R1,R2,
R3等表示對應資料行rowid .

對於葉子節點塊來說，其所包含的索引條目與分支節點一樣，都是按照順序排列的
(預設升序，也可在建立時指定為降序)，每個索引條目(也可以叫做每條記錄)也具
有兩個欄位。第一個欄位表示索引的鍵值(索引所在列的值)，對於單列索引來說是
一個值，對於組合索引而言是多個值組合在一起的。 第二個欄位表示鍵值所對應
的記錄行的ROWID, 該ROWID是記錄行在表中的實體地址。如果索引是建立在非分割槽
表上或者索引是分割槽表上的本地索引的話，那麼ROWID佔用6個位元組； 如果是建立
在分割槽表上的全域性索引的話，rowid佔用10個位元組。

瞭解這些之後，我們來說明如何估算每個索引能佔用多少條目，以及對於表來說，
所產生的索引大約多大，

比如對於一個索引塊來說，預設的pctfree是10%, 在9i以後，剩下的90%也只能
使用其中的87%, 也就是說8KB的oracle block能實際用來存放索引資料的空間大
約為 8KB * 90% * 87% = 6488位元組。 

假設一個非分割槽表，表名warecountd, 資料行數是130萬，一個列goodid型別為
char(8),是固定長度，在該列上建立了一個B-Tree索引。 

在葉子節點中，每個索引條目都會在索引資料塊中佔一行空間。每一行用2到3
個位元組作為行頭，行頭用來存放標記以及鎖定型別等資訊，同時在第一個表示
索引的鍵值的欄位中，每一個索引列都有一個位元組表示資料長度，後面就是該
列具體的值，那麼對於本例來說，在葉子節點中的一行所包含的資料大致如下：

------------------------------------------------------------------------------------
行頭(2位元組) | C1列的長度(1位元組) | C1列的值(8位元組) | rowid長度(1位元組) | rowid (6位元組)
------------------------------------------------------------------------------------

可以看出，本例的葉子節點，索引條目佔18個位元組，8K Block中能用的空間為
6488位元組，那麼本例中，一個索引資料塊可以放6488/18=360個索引條目，而對
於130萬條記錄(約130個索引條目，因為可能goodi列有些行的值為空)，則需要
大約 1300000/360 = 3611 個葉子節點塊 。

對於分支節點中的一個條目來說，因為它只需要儲存所連結的其他索引塊的地址
即可，而不需要儲存具體的資料行在哪裡，因此它佔用的空間要比葉子節點要少。
分支節點中一行中所存放的連結的最小鍵值所需空間與上面所描述的葉子節點相
同，而存放索引塊的地址只需要4個位元組，比葉子節點存放rowid少了2個位元組，少
的這兩個位元組也就是rowid中用來描述在資料塊中行號所需的空間，因此，本例中
在分支節點中的一行包含的資料大致如下：

-----------------------------------------------------------------------------------------------
行頭(2位元組) | C1列的長度(1位元組) | C1列的值(8位元組) | file#+block#長度(1位元組) | file#+block#(4位元組)
-----------------------------------------------------------------------------------------------

由上可以知道，分支節點中一個索引條目佔16個位元組，可以知道一個分支索引塊
可以存放大概6488/16=405個索引條目。而對於我們需要的3611個葉子節點來說，
則共需要 3611/405=9個分支索引塊 。

這樣，我們就知道了這個索引有兩層，第一層是1個根節點，第二層是9個分支節
點，葉子節點數為3611個，指向的資料行為130萬，不過Oracle中層級號是倒過
來的，根節點為N, 下一層分支節點層級號是N-1. 對於本例，根節點層級號為2，
9個分支節點層級號為1 .

4. B-Tree索引的內部結構

我們可以用如下方式將B-Tree索引dump成樹狀結構的形式呈現出來：
alter session set events 'immediate trace name treedump level INDEX_OBJECT_ID'; 
例子：

select object_id from user_objects where object_name='EFOXSFCEDIASBUILDCHILD_PK' ;
OBJECT_ID
-----------
    51811

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

## 以下我們加上linux行號

     21 ----- begin tree dump
     22 branch: 0x280070c 41944844 (0: nrow: 3, level: 3)
     23    branch: 0x28179f7 42039799 (-1: nrow: 154, level: 2)
     24       branch: 0x240a9be 37792190 (-1: nrow: 109, level: 1)
     25          leaf: 0x2800713 41944851 (-1: nrow: 55 rrow: 55)
     26          leaf: 0x280076f 41944943 (0: nrow: 54 rrow: 54)
     27          leaf: 0x2800770 41944944 (1: nrow: 51 rrow: 51)
     28          leaf: 0x280076a 41944938 (2: nrow: 48 rrow: 48)
     29          leaf: 0x2800711 41944849 (3: nrow: 51 rrow: 51)
     30          leaf: 0x2800778 41944952 (4: nrow: 54 rrow: 54)
     31          leaf: 0x2800771 41944945 (5: nrow: 67 rrow: 67)
     32          leaf: 0x2800772 41944946 (6: nrow: 67 rrow: 67)
     33          leaf: 0x2800773 41944947 (7: nrow: 61 rrow: 61)
     34          leaf: 0x2800774 41944948 (8: nrow: 68 rrow: 68)
     35          leaf: 0x2800775 41944949 (9: nrow: 61 rrow: 61)
     36          leaf: 0x2800776 41944950 (10: nrow: 70 rrow: 70)
     37          leaf: 0x2800777 41944951 (11: nrow: 68 rrow: 68)
     38          leaf: 0x280077b 41944955 (12: nrow: 67 rrow: 67)
     39          leaf: 0x280077c 41944956 (13: nrow: 52 rrow: 52)
......

    129          leaf: 0x240a9b6 37792182 (103: nrow: 68 rrow: 68)
    130          leaf: 0x240a9c2 37792194 (104: nrow: 67 rrow: 67)
    131          leaf: 0x240a9c6 37792198 (105: nrow: 52 rrow: 52)
    132          leaf: 0x240a98f 37792143 (106: nrow: 68 rrow: 68)
    133          leaf: 0x240a98b 37792139 (107: nrow: 65 rrow: 65)
    134       branch: 0x280f386 42005382 (0: nrow: 113, level: 1)
    135          leaf: 0x240a993 37792147 (-1: nrow: 68 rrow: 68)
    136          leaf: 0x240a99b 37792155 (0: nrow: 60 rrow: 60)
    137          leaf: 0x240a99f 37792159 (1: nrow: 66 rrow: 66)
    138          leaf: 0x240a9a3 37792163 (2: nrow: 60 rrow: 60)
    139          leaf: 0x240a9a7 37792167 (3: nrow: 66 rrow: 66)

.......

    245          leaf: 0x280f32e 42005294 (109: nrow: 68 rrow: 68)
    246          leaf: 0x280f32a 42005290 (110: nrow: 67 rrow: 67)
    247          leaf: 0x280f332 42005298 (111: nrow: 52 rrow: 52)
    248       branch: 0x240aa85 37792389 (1: nrow: 100, level: 1)
    249          leaf: 0x280f336 42005302 (-1: nrow: 68 rrow: 68)
    250          leaf: 0x280f33e 42005310 (0: nrow: 65 rrow: 65)
    251          leaf: 0x280f33a 42005306 (1: nrow: 68 rrow: 68)

........

    458          leaf: 0x280f3da 42005466 (107: nrow: 65 rrow: 65)
    459       branch: 0x280f3b9 42005433 (3: nrow: 113, level: 1)
    460          leaf: 0x280f3de 42005470 (-1: nrow: 68 rrow: 68)
    461          leaf: 0x280f3e2 42005474 (0: nrow: 60 rrow: 60)

........

  16382          leaf: 0x2816324 42033956 (89: nrow: 68 rrow: 68)
  16383          leaf: 0x2816334 42033972 (90: nrow: 67 rrow: 67)
  16384          leaf: 0x2816344 42033988 (91: nrow: 52 rrow: 52)
  16385    branch: 0x2817a07 42039815 (0: nrow: 156, level: 2)
  16386       branch: 0x281603d 42033213 (-1: nrow: 100, level: 1)
  16387          leaf: 0x2816354 42034004 (-1: nrow: 68 rrow: 68)
  16388          leaf: 0x2816364 42034020 (0: nrow: 65 rrow: 65)
  16389          leaf: 0x2816374 42034036 (1: nrow: 68 rrow: 68)

........

  32602          leaf: 0x2818350 42042192 (89: nrow: 66 rrow: 66)
  32603          leaf: 0x2818360 42042208 (90: nrow: 68 rrow: 68)
  32604    branch: 0x2819da1 42048929 (1: nrow: 231, level: 2)
  32605       branch: 0x28183f3 42042355 (-1: nrow: 109, level: 1)
  32606          leaf: 0x2818370 42042224 (-1: nrow: 69 rrow: 69)
  32607          leaf: 0x2818380 42042240 (0: nrow: 64 rrow: 64)

其中，第一列表示節點型別，branch表示分支節點(包括根節點)，leaf表示葉子
節點，第二列表示16進位制的節點地址； 第三列表示十進位制的節點地址；第四列表
示相對於前一個節點的位置，根節點從0開始計算，其他分支節點和葉子節點從-1
開始計算； 第五列的 nrow: 9 表示當前節點所含索引條目的數量，nrow: 9表示
根節點中含有9個索引條目，分別指向9個分支節點； 第六個列中的level表示分
支節點的層級，對於葉子節點level都是0， 第六列中rrow表示有效的索引條目數量
(因為索引條目如果被刪除，不會立即被清除出索引塊。所以nrow減rrow的數量就
表示已經被刪除的索引條目數量)，

備註： 比如16進位制280f3b9透過計算器轉換為十進位制42005433 。

同時我們還可以轉儲一個索引節點(塊)來看看其中存放了什麼，轉儲方式為：
alter system dump datafile file#  block block# ;

從上面的trc檔案可以知道，索引的根節點的地址是 41944844 . 我們先將其
轉換為檔案號和資料塊號得到 file#=10, block#=1804 . 

select  dbms_utility.data_block_address_file(41944844),
dbms_utility.data_block_address_block(41944844) from dual ;

然後執行：
alter system dump datafile 10 block 1804 ;

從轉儲trc檔案中可以看到如下索引頭部的內容：

Branch block dump
=================
header address 216753740=0xceb664c
kdxcolev 3
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 3
kdxcosdc 3
kdxconro 2
kdxcofbo 32=0x20
kdxcofeo 8005=0x1f45
kdxcoavs 7973
kdxbrlmc 42039799=0x28179f7
kdxbrsno 1
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8030] dba: 42039815=0x2817a07
col 0; len 20; (20):  39 53 34 32 39 34 30 37 34 32 31 33 30 34 53 37 30 35 32 39
col 1; TERM
row#1[8005] dba: 42048929=0x2819da1
col 0; len 19; (19):  39 53 34 32 39 34 30 37 34 32 31 33 30 34 55 37 30 34 34
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 9 file#: 10 minblk 1804 maxblk 1804

其中
kdxcolev 3表示索引層級號，我們轉儲的是根節點，層級號為3.
kdxcolok 表示該索引上是否正在發生修改塊結構的事務，0表示沒有。
kdxcoopc 表示內部操作程式碼。
kdxconco 表示索引條目中列的數量，這裡是3，屬於聯合索引。
kdxcosdc 表示索引結構發生變化的數量，當你修改表裡某個某個索引鍵值時，該值增加。
kdxconro 表示當前索引節點中索引條目的數量，但注意不包括kdxbrlmc指標。
kdxcofbo 表示當前索引節點中可用空間的起始點相對當前塊的位移量。
kdxcofeo 表示當前索引節點中可用空間的最尾端相對當前塊的位移量。
kdxcoavs 表示當前索引塊中的可用空間總量，也就是用kdxcofeo減去kdxcofbo得到的。
kdxbrlmc 表示分支節點的地址，該分支節點存放了索引鍵值小於row#0（在轉儲文件後半
部分顯示）所含有的最小值的所有節點資訊；
kdxbrsno  表示最後一個被修改的索引條目號，這裡看到是0，表示該索引是新建的索引；
kdxbrbksz 表示可用資料塊的空間大小。實際從這裡已經可以看到，即便是PCTFREE設定為0，
也不能用足8192位元組。

row#0[8043] dba: 25226808=0x180ee38
col 0; len 8; (8): 31 30 30 30 30 33 39 32
col 1; len 3; (3): 01 40 1a
……
row#7[7918] dba: 25229599=0x180f91f
col 0; len 8; (8): 31 30 30 31 31 32 30 33
col 1; len 4; (4): 01 40 8f a5

每個索引條目都指向一個分支節點。其中col 1表示所連結的分支節點的地址，
該值經過一定的轉換以後實際就是row#所在行的dba的值。如果根節點下沒有其
他的分支節點，則col 1為TERM；col 0表示該分支節點所連結的最小鍵值。其轉換
方式非常複雜，比如對於row #0來說，col 0為31 30 30 30 30 30 30 33，則將其
中每對值都使用函式to_number(NN,’XX’)的方式從十六進位制轉換為十進位制，於是
我們得到轉換後的值：49,48,48,48,48,48,48,51，因為我們已經知道索引鍵值是char
型別的，所以對每個值都運用chr函式就可以得到被索引鍵值為：10000003。實際上，
對10000003運用dump函式得到的結果就是：49,48,48,48,48,48,48,51。所以我們也就
知道，10000003就是dba為25226808的索引塊所連結的最小鍵值。

kdxlespl 0
kdxlende 0
kdxlenxt 25226403=0x180eca3
kdxleprv 25226400=0x180eca0
kdxledsz 0
kdxlebksz 8036
其中的kdxlespl表示當葉子節點被拆分時未提交的事務數量；kdxlende表示被刪除
的索引條目的數量；kdxlenxt表示當前葉子節點的下一個葉子節點的地址；kdxlprv
表示當前葉子節點的上一個葉子節點的地址；kdxledsz表示可用空間，目前是0。

轉儲檔案中接下來的部分就是索引條目部分，每個條目包含一個ROWID，指向一個表裡
的資料行。如下所示。其中flag表示標記，比如刪除標記等；而lock表示鎖定資訊。
col 0表示索引鍵值，其演算法與我們在前面介紹分支節點時所說的演算法一致。col 1表
示ROWID。我們同樣可以看到，該葉子節點中包含了359個索引條目，與我們前面所
估計的一個葉子節點中大約可以放360個索引條目也是基本一致的。

row#0[8018] flag: -----, lock: 0
col 0; len 8; (8): 31 30 30 30 30 33 39 33
col 1; len 6; (6): 01 40 2e 93 00 16

row#1[8000] flag: -----, lock: 0
col 0; len 8; (8): 31 30 30 30 30 33 39 33
col 1; len 6; (6): 01 40 2e e7 00 0e
…………

row#358[1574] flag: -----, lock: 0
col 0; len 8; (8): 31 30 30 30 30 33 39 37
col 1; len 6; (6): 01 40 18 ba 00 1f

----- end of leaf block dump -----