簡述Oracle IOT（Index Organized Table）（下）

 

下面我們討論一下由於資料儲存為索引而帶來的Rowid、Secondary Index問題。

 

6、Logical Rowid & Secondary Index

 

在IOT的環境下，我們是不能保證一個固定的物理Rowid的。

 

堆表（Heap Table）中，一行資料被儲存在一個物理位置（file no. + block no.）之後，在正常儲存行為中，即使發生行遷移現象，它的rowid是不會發生變化的。只有在進行資料表儲存重構，如move和shrink space的時候才會發生新的rowid賦予。

 

堆表rowid的固定給我們帶來一個好處，就是連帶的資料表索引葉子節點上面的的rowid永遠有效的，除非發生move或者和shrink space操作（此時索引失效）。

 

但是，IOT存在一些問題。索引葉子節點的分裂操作是相當頻繁的，我們很難保證一個資料行維持在一個rowid不會發生大的變化。當然，如果我們保證每次訪問資料表都是透過主鍵primary key方式，變化的rowid不會有任何影響。問題出在非主鍵的索引，IOT中稱之為“二級索引”Secondary Index上。

 

對於一般的二級索引，如果葉子節點上保留資料行的rowid，那麼失效的rowid意味著所有對應的二級索引非常容易變為invalid狀態。

 

在很多資料庫版本，包括早期的Oracle版本中，對於Secondary Index是不支援的。最近的oracle中，引入了Logical Rowid和Physical Guess的方法，才最終解決了Secondary Index問題。

 

 

SQL> select rowid, object_id from t_iot where rownum<5;

 

ROWID                             OBJECT_ID

------------------------------- -----------

*BABBVmoCwQP+                             2

*BABBVmoCwQT+                             3

*BABBVmoCwQX+                             4

*BABBVmoCwQb+                             5

 

 

對IOT而言，rowid基本上是不合乎我們常見的heap table rowid格式的。我們可以對t_iot新增secondary index。

 

 

SQL> create index idx_t_iot_name on t_iot(object_name);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T_IOT',cascade => true);

PL/SQL procedure successfully completed

 

 

從資料字典中看，索引idx_t_iot_name沒有什麼額外的差異，只是對於一般索引來說，clustering factor取值略高。

 

 

SQL> select index_Name, index_type, clustering_factor  from dba_indexes where wner='SYS' and index_name='IDX_T_IOT_NAME';

 

INDEX_NAME                     INDEX_TYPE                  CLUSTERING_FACTOR

------------------------------ --------------------------- -----------------

IDX_T_IOT_NAME                 NORMAL                                  55006

 

SQL> select count(*) from t_iot;

 

  COUNT(*)

----------

     72604

 

 

SQL> select sum(bytes)/1024/1024, count(*) from dba_extents where wner='SYS' and segment_name='IDX_T_IOT_NAME';

 

SUM(BYTES)/1024/1024   COUNT(*)

-------------------- ----------

                   4         19

 

 

對於一個7萬餘條記錄的資料表索引，佔到了19個分割槽，總看空間4M。那麼，如果是一般的heap table index呢？空間如何？

 

 

SQL> desc t_heap;

Name        Type          Nullable Default Comments

----------- ------------- -------- ------- --------

OBJECT_ID   NUMBER(10)                             

OBJECT_NAME VARCHAR2(100) Y                         

 

SQL> create index idx_t_heap_name on t_heap(object_name);

 

Index created

 

SQL> select count(*) from t_heap;

 

  COUNT(*)

----------

     72605

 

SQL> select sum(bytes)/1024/1024, count(*) from dba_extents where wner='SYS' and segment_name='IDX_T_HEAP_NAME';

 

SUM(BYTES)/1024/1024   COUNT(*)

-------------------- ----------

                   3         18

 

 

相同取值，正常index只有3M空間，約佔到18個分割槽。說明：Secondary Index對比一些其他索引，有很多特殊的資訊在其中。

 

 

SQL> col object_name for a20;

SQL> select object_id, object_name from dba_objects where object_name in ('IDX_T_HEAP_NAME','IDX_T_IOT_NAME');

 

 OBJECT_ID OBJECT_NAME

---------- --------------------

     75146 IDX_T_HEAP_NAME

     75143 IDX_T_IOT_NAME

 

 

我們嘗試將兩個索引樹dump出來，探索其結構差異。

 

 

SQL> select value from v$diag_info where name='Default Trace File';

 

VALUE

--------------------------------------------------------------------------------

/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_9101.trc

 

--堆表索引結構

SQL> alter system set events 'immediate trace name treedump level  75146';

System altered

 

--IOT表索引結構

SQL> alter system set events 'immediate trace name treedump level  75143';

System altered

 

 

 

首先，我們分析一下一般堆表的索引情況。由於篇幅原因，只擷取部分內容。

 

 

*** ACTION NAME:(Command Window - New) 2012-10-05 02:43:39.561

 

----- begin tree dump

branch: 0x415c01 4283393 (0: nrow: 2, level: 2)

   branch: 0x415d3b 4283707 (-1: nrow: 312, level: 1)

      leaf: 0x415c02 4283394 (-1: nrow: 184 rrow: 184)

      leaf: 0x415c03 4283395 (0: nrow: 184 rrow: 184)

      leaf: 0x415c04 4283396 (1: nrow: 188 rrow: 188)

      leaf: 0x415c05 4283397 (2: nrow: 190 rrow: 190)

      leaf: 0x415c06 4283398 (3: nrow: 184 rrow: 184)

      leaf: 0x415c07 4283399 (4: nrow: 186 rrow: 186)

      leaf: 0x415c08 4283400 (5: nrow: 185 rrow: 185)

 

 

從Dump結果上，我們可以清晰看到IDX_T_HEAP_NAME是一個兩層索引結構。根節點地址為0x415c01（file=1, block=89089）。

 

 

SQL> select to_number('415c01','xxxxxx') from dual;

 

TO_NUMBER('415C01','XXXXXX')

----------------------------

                     4283393

 

 

其中的一個資料塊0x415c06進行試驗，轉化為十進位制地址為4283398，二進位制地址為：10000010101110000000110。根據rfile解析規則，最終地址為：file_no=1，block_no=89094。

 

 

SQL> alter system dump datafile 1 block 89094;

System altered

 

Dump檔案中葉子節點的內容為：

 

row#0[8000] flag: ------, lock: 0, len=32

col 0; len 22; (22):

 2f 31 34 64 63 62 36 32 32 5f 53 79 6e 74 68 4c 61 62 65 6c 55 49

col 1; len 6; (6):  00 41 55 91 00 4b

row#1[7968] flag: ------, lock: 0, len=32

col 0; len 22; (22):

 2f 31 34 64 63 62 36 32 32 5f 53 79 6e 74 68 4c 61 62 65 6c 55 49

col 1; len 6; (6):  00 41 55 91 00 4c

row#2[7928] flag: ------, lock: 0, len=40

col 0; len 30; (30):

 2f 31 34 65 33 63 31 31 32 5f 50 4e 47 45 6e 63 6f 64 65 50 61 72 61 6d 50

 61 6c 65 74 74

col 1; len 6; (6):  00 41 5b 9a 00 9d

 

 

 

從結構上猜測，col0和col1分別表示索引列取值和對應rowid資訊。而IOT的secondary index如何呢？

 

 

*** 2012-10-05 02:43:55.944

----- begin tree dump

branch: 0x4154b9 4281529 (0: nrow: 2, level: 2)

   branch: 0x415acd 4283085 (-1: nrow: 330, level: 1)

      leaf: 0x4154ba 4281530 (-1: nrow: 160 rrow: 160)

      leaf: 0x4154bb 4281531 (0: nrow: 158 rrow: 158)

      leaf: 0x4154bc 4281532 (1: nrow: 163 rrow: 163)

      leaf: 0x4154bd 4281533 (2: nrow: 162 rrow: 162)

      leaf: 0x4154be 4281534 (3: nrow: 163 rrow: 163)

      leaf: 0x4154bf 4281535 (4: nrow: 160 rrow: 160)

      leaf: 0x4154c0 4281536 (5: nrow: 159 rrow: 159)

      leaf: 0x4154c1 4281537 (6: nrow: 161 rrow: 161)

      leaf: 0x4154c2 4281538 (7: nrow: 160 rrow: 160)

 

 

葉子節點0x4154bc，對應具體的二進位制為：10000010101010010111100。分析獲得的位置為：file_no=1，block_no=87228。

 

我們將該塊dump出。

 

 

SQL> alter system dump datafile 1 block 87228;

System altered

 

 

row#0[7986] flag: K-----, lock: 0, len=46

col 0; len 30; (30):

 2f 31 32 30 66 34 37 30 38 5f 46 75 6c 6c 48 54 4d 4c 44 6f 63 75 6d 65 6e

 74 61 74 69 6f

col 1; len 4; (4):  c3 04 39 24

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  00 41 59 3e

row#1[7940] flag: K-----, lock: 0, len=46

col 0; len 30; (30):

 2f 31 32 30 66 64 37 36 64 5f 4f 72 61 63 6c 65 44 61 74 61 62 61 73 65 4d

 65 74 61 44 61

col 1; len 4; (4):  c3 02 50 1a

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  00 41 54 a1

row#2[7894] flag: K-----, lock: 0, len=46

col 0; len 30; (30):

 2f 31 32 30 66 64 37 36 64 5f 4f 72 61 63 6c 65 44 61 74 61 62 61 73 65 4d

 65 74 61 44 61

col 1; len 4; (4):  c3 04 4c 1a

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  00 41 54 a2

 

 

上面標紅的部分是我們看到了和Heap Index的差異，正式由於這部分資訊的差異，才讓IOT Secondary Index體積略大。

 

從概念上，Secondary Index包括三部分葉子節點內容：索引鍵值、logical rowid和對應資料行的主鍵值。在進行檢索的時候，Oracle首先用logical rowid進行初步的試探，看看對應的位置是否可以找到對應資料。這個過程我們稱為physical guess。

 

如果找到了對應資料行，那麼皆大歡喜。如果沒有，oracle就只能透過資料行的主鍵值，進行IOT索引樹定位。這個過程，要重複多讀一個段結構。

 

具體Secondary Index的分析，留待下次進行更加詳細的說明。

 

7、IOT的使用

 

我們已經在一個系列中，詳細介紹了IOT的特性，最後我們聊一聊IOT應用的場景。總的來說，筆者認為IOT在一般系統的應用中，是沒有很廣泛的發揮場景的。在沒有明確的分析和POC實驗基礎上，我們不要輕易進行IOT決策。具體來說，有如下的幾點：

 

ü  IOT環境下，有更多的限制

 

我們常使用的堆表，雖然有各種問題，但是是目前我們可以得到的適應性最廣，最佳化策略最多的一種表儲存結構。IOT則要受到很多的限制，例如：IOT必須要制定主鍵，也就是定義出核心訪問方式；PCTThreshold引數如果設定了，但是沒有指定overflew segment，那麼超出閾值的資料行是不會被接受，要丟擲異常。IOT表中分割槽和Lob型別不能同時使用。IOT維護工作要更多。

 

ü  單一讀取，讀多寫少的操作型別

 

我們定義出IOT後，實際上就是規定了資料表的核心訪問方式。當我們使用主鍵條件時，IOT可以方便的幫助我們定位記錄。但是其他查詢條件應用secondary index的效率就是一個問題。而且secondary index也是不久前才支援的Oracle特性。如果我們的資料表應用是一個多種檢索方式並存的操作表，那麼IOT不是理想的選擇。

 

索引操作本身對增加、修改和刪除等DML操作是具有效能影響的。在IOT環境下，這種影響只會讓其更加劇烈。所以，如果資料表不是很少修改的資料表，那麼使用IOT不是最好的選擇。

 

ü  主鍵列和列數目的約束

 

索引葉子節點中就能將所有資料行列儲存在葉子節點上。而索引葉子節點是變化分裂頻繁的物件。所以，如果資料行列數很多，或者資料主鍵列相對很小，那麼IOT的效果是不好的。

 

 

8、結論

 

Heap，IOT和Cluster是資料表的三大基本儲存型別。我們在實際中，要把握原則是：以堆表為核心，預設都使用Heap Table。如果在架構分析、效能測試和試執行階段，發現效能問題，可以考慮使用IOT或者Cluster。但是，在選型的時候，一定要明確兩種表結構的優缺點和適用範圍。