簡述Oracle IOT（Index Organized Table）

標籤：time   base   計劃   state   cost   success   from   結構   fast   

轉：http://blog.itpub.net/17203031/viewspace-744477

 

對關係型資料庫產品（RDBMS）而言，一個重要特性就是：資料資訊都被組織為二維資料表，資訊的表達可以通過一系列的關聯（Join）來完成。具體資料庫產品在實現這個標準的時候，又有千差萬別的特點。就是一個特定的資料庫RDBMS產品，往往也提供不同的實現方法。

 

1、從堆表（Heap Table）到索引組織表（Index Organization Table）

 

Oracle作為一款成熟的資料庫軟體產品，就提供了多種資料表格儲存體結構。我們最常見的就是三種，分別為堆表（Heap Table）、索引組織表（Index Organization Table，簡稱為IOT）和聚簇表（Cluster Table）。

 

Heap Table是我們在Oracle中最常使用的資料表，也是Oracle的預設資料表格儲存體結構。在Heap Table中，資料行是按照“隨機存取”的方式進行管理。從段頭塊之後，一直到高水位線一下的空間，Oracle都是按照隨機的方式進行“粗放式”管理。當一條資料需要插入到資料表中時，預設情況下，Oracle會在高水位線以下尋找有沒有閒置地方，能夠容納這個新資料行。如果可以找到這樣的地方，Oracle就將這行資料放在空位上。注意，這個空位選擇完全依“能放下”的原則，這個空位可能是被刪除資料行的覆蓋位。

 

如果Heap Table段的HWM下沒有找到合適的位置，Oracle堆表才去向上推高水位線。在資料行儲存上，Heap Table的資料行是完全沒有次序之分的。我們稱之為“隨機存取”特徵。

 

對Heap Table，索引獨立段的添加一般可以有效緩解由於隨機存取帶來的檢索壓力。Index葉子節點上記錄的資料行索引值和Rowid取值，可以讓Server Process直接定位到資料行的塊位置。

 

聚簇（Cluster Table）是一種合并段儲存的情況。Oracle認為，如果一些資料表更新頻率不高，但是經常和另外一個資料表進行串連查詢（Join）顯示，就可以將其組織在一個儲存結構中，這樣可以最大限度的提升效能效率。對聚簇表而言，多個資料表按照串連鍵的順序儲存在一起。

 

通常系統內容下，我們使用Cluster Table的情況不太多。Oracle中的資料字典大量的使用聚簇。相比是各種關聯的基表之間固定串連檢索的情境較多，從而確定的方案。

 

最後就是本系列的IOT（Index Organization Table）。同Cluster Table一樣，IOT是在Oracle資料表策略的一種“非主流”，應用的情境比較窄。但是一些情況下使用它，往往可以起到非常好的效果。

 

簡單的說，IOT區別於堆表的最大特點，就在於資料行的組織並不是隨機的，而是依據資料表主鍵，按照索引樹進行儲存。從段segment結構上看，IOT索引段就包括了所有資料行列，不存在單獨的資料表段。

 

IOT在儲存結構上有一些特殊之處，應用在一些特殊的情境之下。本系列將逐個分析IOT的一些特徵，最後討論我們究竟在什麼樣的情境下，可以選擇IOT作為資料表方案。

 

2、IOT基礎

 

在建立使用IOT上，我們要強調Primary Key的作用。對一般的堆表而言，Primary Key是可有可無的。一種說法是：當一個堆表沒有設定主鍵的時候，rowid偽列就是對應的主索引值。而且，Primary Key可以在資料表建立之後進行追加設定。

 

但是，IOT對於主鍵的設定格外嚴格，要求建立表的時候就必須指定明確的主鍵列。下面我們通過一系列的實驗來證明，實驗環境為Oracle 11g。

 

 

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0  Production

 

 

我們使用相同的結構，來建立出IOT和Heap Table對照。

 

--不指定主鍵，是無法建立IOT；

SQL> create table m (id number) organization index;

 

create table m (id number) organization index

ORA-25175: 未找到任何 PRIMARY KEY 約束條件

 

 

在create table語句後面使用organization index，就指定資料表建立結構是IOT。但是在不指定主鍵Primary Key的情況下，是不允許建表的。

 

 

SQL> create table t_iot (object_id number(10) primary key, object_name varchar2(100)) organization index;

Table created

 

SQL> create table t_heap (object_id number(10) primary key, object_name varchar2(100));

Table created

 

（插入相同資料來源行……）

 

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

PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.gather_table_stats(user,‘T_HEAP‘,cascade => true);

PL/SQL procedure successfully completed

 

 

從資料字典的層面上，我們分析一下兩個資料表的差異，一窺IOT的特點。

 

 

SQL> select table_name, tablespace_name, blocks, num_rows from user_tables where table_name in (‘T_IOT‘,‘T_HEAP‘);

 

TABLE_NAME          TABLESPACE_NAME                    BLOCKS   NUM_ROWS

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

T_HEAP                SYSTEM                                157      72638

T_IOT                                                                72638

 

SQL> select segment_name, blocks, extents from user_segments where segment_name in (‘T_IOT‘,‘T_HEAP‘);

 

SEGMENT_NAME             BLOCKS    EXTENTS

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

T_HEAP                      256         17

 

 

上面兩句SQL揭示了幾個問題。首先，Oracle承認IOT是一個資料表，並且統計了資料行數。但是對資料表的儲存資料表空間和大小沒有明確的說明，user_tables視圖中這部分的內容為空白。

 

其次，從段結構來看，Oracle明確不承認存在T_IOT段。因為如果有段segment對象，就意味有空間分配。但是資料表有資料，是存放在哪裡呢？

 

我們知道，給資料表添加索引的時候，Oracle會自動的添加一個唯一索引。那麼我們去檢查一下這部分的結構情況。

 

 

SQL> select index_name, index_type, table_name, PCT_THRESHOLD, CLUSTERING_FACTOR from user_indexes where table_name in (‘T_IOT‘,‘T_HEAP‘);

 

INDEX_NAME    INDEX_TYPE           TABLE_NAME PCT_THRESHOLD CLUSTERING_FACTOR

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

SYS_C0012408    NORMAL               T_HEAP                                 256

SYS_IOT_TOP_75124 IOT - TOP      T_IOT                 50                 0

 

SQL> select segment_name, blocks, extents from user_segments where segment_name in (‘SYS_C0012408‘,‘SYS_IOT_TOP_75124‘);

 

SEGMENT_NAME             BLOCKS    EXTENTS

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

SYS_C0012408                256         17

SYS_IOT_TOP_75124           256         17

 

 

索引段是存在的，而且明確標註索引類型為IOT索引。這說明幾個問題：

 

首先，對於IOT而言，只有索引段，沒有資料區段。一般的索引而言，葉子節點上只有索引列的取值和rowid。而對於IOT而言，主鍵索引上對應就是資料行和索引列取值。

 

其次，IOT的溢出段閾值（PCT_THRESHOLD）。這是Oracle IOT的特殊策略。簡單的說，當我們把全部資料行儲存在葉子節點上，一旦發生主索引值的變化、新值插入、刪除等動作，索引葉子塊的分裂動作是頻繁的。資料行儲存在葉子節點上只會讓這樣的分裂動作更加頻繁和後果嚴重。Oracle提出將一部分的非主鍵列單獨儲存，這個參數就是比例值。

 

最後，我們探討一下IOT索引的Clustering Factor。Clustering Factor是反映索引葉子節點順序和資料儲存行直接離散程度的綜合性指標。一般來說，堆表的Clustering Factor是隨著DML操作不斷退化的過程。Clustering Factor是影響到Oracle索引路徑成本的一個重要參數（http://space.itpub.net/17203031/viewspace-680936），會影響到CBO的成本決策。 IOT的索引這部分的值永遠為0，因為索引的順序就是資料行的順序，兩者儲存順序相同，絕對一致。

 

 

3、IOT與執行計畫

 

在IOT資料表下，我們通常的執行計畫會如何呢？普通Heap Table和IOT在這部分的差異很大。

 

通常而言，Heap Table的索引路徑伴隨著兩次段結構的讀取——索引段和資料區段。先讀取索引段段頭，經曆根節點、分支節點、葉子節點，最後擷取到結果集合rowid列表。之後進行回表操作，使用rowid依次查詢資料表的行。

 

但是IOT表可以不同。索引和資料保留在一起，理論上拿到了葉子節點，也就是拿到了資料行。IOT是不存在回表操作的，所以相對heap table來說，回表部分成本是節省的。

 

下面我們通過執行計畫，來看IOT的特徵。

 

 

SQL> explain plan for select * from t_iot where object_id=1000;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 2277898128

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

| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Tim

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

|   0 | SELECT STATEMENT  |                 |     1 |    11 |     1   (0)| 00:

|*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_75124 |    1 |    11 |     1   (0)| 00:

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

Predicate Information (identified by operation id):

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

   1 - access("OBJECT_ID"=1000)

13 rows selected

 

SQL> explain plan for select * from t_iot;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 4201110863

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

| Id  | Operation            | Name              | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT     |                   | 72638 |   780K|    47   (0)|

|   1 |  INDEX FAST FULL SCAN| SYS_IOT_TOP_75124 | 72638 |   780K|    47   (0)|

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

8 rows selected

 

 

對於IOT，我們要保證訪問的資料表的方式是主鍵路徑為主。在上面的兩個執行計畫中，我們按照主鍵進行檢索，路徑為Index Unique Scan。全表掃描為Index Fast Full Scan。兩者都沒有明顯的回表動作。

 

試想，如果資料表較小，Index Full Scan也是IOT表常常出現的執行路徑。

 

對一般的Heap Table，執行路徑如何呢？

 

 

SQL> explain plan for select * from t_heap where object_id=1000;

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1833345710

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

| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)

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

|   0 | SELECT STATEMENT           |              |     1 |    11 |     2   (0)

|   1 |  TABLE ACCESS BY INDEX ROWID| T_HEAP      |     1 |    11 |     2   (0)

|*  2 |   INDEX UNIQUE SCAN        | SYS_C0012408 |     1 |       |     1   (0)

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=1000)

14 rows selected

 

 

SQL> explain plan for select * from t_heap;

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1253663840

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

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |        | 72638 |   780K|    42   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T_HEAP | 72638 |   780K|    42   (0)| 00:00:01 |

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

 

8 rows selected

 

 

普通堆表都不能避免出現回表動作。

 

最後，我們要聲明一下回表動作的成本影響。IOT和Heap Table一個很大的執行計畫差異，就是回表。但是從成本上計算，CBO並不是因為回表動作才確定執行計畫，而是Clustering Factor的影響。

 

對堆表而言，Clustering Factor都是一個很大的問題，無論是CBO的成本公式上，還是不斷Degrade的前景。IOT一個突出優勢就是直接消滅了Clustering Factor的成本因素。

 

但是這也就帶來一個問題，一個資料表只能按照主鍵的順序進行組織，輔助索引（Secondary Index）的問題是很多版本Oracle和IOT使用者爭議的話題。Secondary Index問題我們在後面會繼續討論到。

 

下篇中我們會繼續討論有關IOT維護等其他內容。

簡述Oracle IOT（Index Organized Table）