Oracle 索引組織表深入理解

Oracle 索引組織表深入理解

今天學習下Oracle中索引組織表，通過這篇文章，你可瞭解到，什麼是索引組織表？什麼情況下可以使用索引組織？索引組織表的優點？索引組織表的弊端？

一：什麼時候索引組織表（IOT）

索引組織表（index organized table): 索引組織表以B*樹結構儲存，我們知道Oracle預設的表是是堆表，堆表是以一種無組織的方式儲存的（只要有可用的空間，就可以放資料），而IOT與之不同，IOT中的資料按著主鍵的順序儲存和排序的，對於應用來說，IOT表現得和常規的堆表並無區別，需要只用sql來正確的來訪問IOT,簡單的概述起來：索引組織表----》索引就是資料，資料就是索引，因為資料就是按著B*樹結構儲存的。如是一個典型的B*tree索引的結構（針對Oracle b*tree索引的理解請參考我的另一篇文章 ）。

而我們今天探討的索引組織表也是按著這個結構儲存資料的，它與B*tree索引的區別是：B*tree索引葉子節點儲存是索引索引值+rowid;而索引組織表的葉子節點儲存的是整行資料，這很類似於mysql的innodb引擎的表。需要注意的是IOT對於主鍵的設定格外嚴格，要求建立表的時候就必須指定明確的主鍵列，因為IOT中的資料是按著主鍵的順序儲存和排序的

二;索引組織表的優點

1）首先顯而易見的是索引組織表是可以節約空間的，因為索引和表合二為一，

2）還有就是根據主鍵進行唯一掃描或者範圍掃描的時候由於索引的排列順序這些列是按索引排列好的，而且比一般索引少一次ROWID回表的操作，那麼速度會更快，

3）其次如果根據資料特點比如一個社會安全號碼ID，一個銀行卡號，顯然一個社會安全號碼ID可以有多個銀行卡號，如果我們建立索引組織表結構為(社會安全號碼ID和銀行卡號），顯然如果在查詢的時候使用ID=** 那麼這種情況下，索引組織表的優勢就出來了，首先他少一次ROWID回表操作，其次索引組織表的排列是有序的，那麼同一個身份證的ID的的卡號資訊一定儲存在臨近的塊中，這實際也是第二點的一個列子。

4）在堆組織表中，兩行資料在同一個資料庫塊上的可能性幾乎為0，而iot表根據主鍵排序後的順序進行排列，所以在按著時間範圍或者按著主鍵範圍查詢的資料在同一個塊上或者相鄰的塊上，所以查詢出來這些資料需要的邏輯io 和物理io都會減少。

5）提高緩衝區快取效率，因為給定查詢在緩衝中需要的塊更少，·減少緩衝區快取訪問，這會改善可擴縮性。

三：索引組織表的弊端以及適用情境：

索引組織表(IOT)不僅可以儲存資料，還可以儲存為表建立的索引。索引組織表的資料是根據主鍵排序後的順序進行排列的，這樣就提高了訪問的速度。但是由於每次寫入和更新後都要重新進行重新排序，導致插入和更新效能降低，所以個人認為在oltp系統中，不太適合使用IOT表，

IOT對資訊擷取、空間系統和OLAP應用最為有用，如果經常在一個主鍵或唯一鍵上使用between查詢，如果資料有序地實體儲存體，就能提升這些查詢的效能，

四：說下Oracle索引組織表的溢出段（overflow段）

1）overflow段存在的意義

為了讓索引葉子塊（包含具體索引資料的塊）能夠高效地儲存資料，索引一般在一個列子集上，通常索引塊上的行數比堆表塊上的行數多出幾倍。索引指望著每塊能得到多行，否則，Oracle會花費大量的時間來維護索引，因為每個insert或update都可能導致索引塊分解。

建立IOT時，overflow子句允許你建立另一個段（就相當於讓IOT成為了一個多段對象，就像有一個CLOB列一樣）如果IOT的行資料變得太大，就可以溢出到這個段中。讀取資料的時候，Oracle將讀取行的"首部",找到行餘下部分的指標，然後讀取這些部分。

再就是因為所有資料都放入索引，所以當表的資料量很大時，會降低索引組織表的查詢效能。此時設定溢出段將主鍵和溢出資料分開來儲存以提高效率。注意長期都是SELECT * FROM 那麼溢出段也就沒有用處；

2）overflow段實現的方式；PCTTHRESHOLD和INCLUDING 兩種

PCTTHRESHOLD n ：制定一個資料區塊的百分比，當行中的資料量超過塊的這個百分比的時候，行中餘下的列將儲存在溢出段，例如PCTTHRESHOLD是10%，而塊的大小是8kb,所以長度大於800位元組的行就會把其中一部分列值儲存在別處，而不能在索引塊上儲存。

INCLUDING column_name ：行中從第一列直到INCLUDING字句所指定列（包括這個列在內）都放入索引塊，之後的列都放到溢出段

3）關於overflow段實現的方式的選擇標準

1.如果你的應用中總是（或者幾乎總是）使用表的前4列，而很少訪問後5列，使用INCLUDING會更合適；

2.如果無法清除的指出哪些列總被訪問而哪些列一般不會被訪問，就可以考慮使用PCTTHRESHOLD。一旦確定了平均每個索引塊上可能儲存多少行，設定PCTTHRESHOLD就會很容易；例如你希望每個索引塊上儲存20行，那好，這說明每行應該是1/20(5%),你的PCTTHRESHOLD 就是5，這樣每行資料佔用塊的大小最多是塊的5%,就能保證可以存20行資料了；

總結：

針對Oracle索引組織表，資料倉儲等olap系統中可以使用，不太適合oltp系統，並且索引組織表上還可以建立索引，稱之為二次索引，並且二次索引和常規表的索引相比效率燒低，因為IOT,通常需要兩個掃描，一次掃描二次索引結構，另一次掃描IOT本身。然後關於索引組織表的溢出段可以使用ALTER TABLE ... OVERFLOW語句來更改溢出段的屬性 。