Index-Organized Table（IOT）

本文主要介紹oracle索引組織表（IOT）的一些特性和使用方法。

索引組織表（IOT）基本是一個儲存在索引中的表。他本質上類似於一個B*樹群，其中資料與一個索引值物理的儲存在一起，但他有以下的不同：

u   存在一個資料結構，一個索引結構，而B*樹群有一個索引和一個資料區段。

儲存的資料按鍵排序，與B* 樹群不一樣，B*樹群中資料按索引值組織，但儲存的索引值本身並不排序。

u   設定IOT的尺寸要比設定群的尺寸稍微容易一些。不需要象散列群那樣估計鍵的最大數目，而且對於如何設定按鍵儲存的資料量的尺寸具有更大的靈活性。

u   IOT在兩種實現方面非常有用，一種是作為關聯表，這些表用語多對多的關係中，另一種是表中資料的物理放置非常重要，但不能預知資料插入的順序，或者資料不能以群隨時保持資料集中放置的次序到達。

一.使用IOT替代關聯表以節省空間的

關聯表一般由兩個列或者兩個鍵組成，用來將兩個表關聯到一起。在oracle資料字典中，可把 DBA_TAB_PRIVS想象為DBA_USERS和DBA_OBJECTS之間的一個關聯對象。單個使用者在給定的對象上可能具有一個或者多個許可權：該給定對象可能具有在其上有許可權的一個或者多個使用者。

對於關聯表，一般應該建立如下的結構：

create table association

(primary_key_table1,

 primary_key_table2,

 <possibly some columns pertaining to the relationship>);

create index association_idx1

on association (primary_key_table1,primary_key_table2);

create index association_idx2

on association (primary_key_table2,primary_key_table1);

這樣，將有三個結構，分別是 ：一個表和兩個索引。這樣兩個索引允許從TABLE1遍曆所有相關的TABLE2的行，或者從TABLE2遍曆所有相關的TABLE1的行。在大多數現實中，甚至從不訪問該表本身，他是一種榮譽的資料結構，被視為一種必不可少的麻煩，因為他們只會浪費空間。在一些相對較少的情況下，他包含專門針對關係的額外資料，不過這些資料的量一般很小。

可以使用IOT獲得相同的效果：

create table association

(primary_key_table1,

primary_key_table2,

<possibly some columns pertaining to the relationship>,

primary key(primary_key_table1,primary_table2))

organization index;

create index association_idx

on association(primary_key_table2);

    我們已經消除了對錶的需求，不僅這樣，而且如果需要檢索關於TABLE1和TABLE2中兩行之間關係的資訊時謀害取消了TABLE ACCESS BY ROWID步驟，請注意，ASSOICATION表上的第二個索引未包含兩個列。這是使用IOT的附帶效果，用於IOT的邏輯ROWID位於索引結構中，且primary_key_table1的值已經存在。可以用下面的小例子看到這一點：

SQL> create table test

  2  (a int,

  3   b int,

  4   primary key (a,b)

  5  )

  6  organization index;

表已建立。

SQL> create index test_idx on test(b);

索引已建立。

SQL> set autotrace traceonly explain

SQL> select a,b from test where b=55;

執行計畫

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

Plan hash value: 2882402178

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

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

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

|  0   |  SELECT STATEMENT  |           |  1  |  26  |    1  (0)  |  00:00:01  |

| * 1   |   INDEX RANGE SCAN | TEST_IDX  |  1  |  26  |    1  (0)  |  00:00:01  |

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

Predicate Information (identified by operation id):

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

   1 - access("B"=55)

Note

-----

   - dynamic sampling used for this statement

其中並沒有TABLE ACCESS BY ROWID步驟。即使查詢中需要列A，oracle也知道可以從索引結構中的邏輯ROWID取得列A的值，從而不需要到表中取他。利用這個事實可以使得在建立IOT上的第二個索引的時候節省大量的磁碟空間。

二.利用IOT集中放置隨機插入的資料

除了作為一種節省空間的手段外，通過消除對某些冗餘表的需求，IOT在為快速存取而把相關的資訊物理的放置在一起的能力方面具有優勢。群的缺點之一是要對資料的訪問進行控制，以便最佳化其物理放置。IOT不需要滿足這個條件，因為他們結構性地對自身進行調整以適應插入的資料。

考慮一個經常檢索某個給定使用者擁有的文獻表的應用程式。在現實世界中，使用者不會在一個會話中插入他曾經擁有的所有文獻，此文獻表的尺寸是不可預知的，他將隨著使用者添加刪除文獻而變動。因此，在傳統的堆表中，代表此使用者的文獻的行將散布在各處。如果執行以下查詢：

select * from document table where username=:bind_variable

會出現oracle將利用一個索引來從整個表中讀取許多塊。如果我們利用IOT物理的將資料集群在一起，就不會發生這種情況。可利用一個簡單的類比以及AUTOTRACE來觀察這一點。例如，建立兩個表：一個使用IOT，另一個使用基於堆實現：

SQL> create table iot

  2  (username varchar2(30),

  3   document_name varchar2(30),

  4   other_data char(100),

  5   constraint iot_pk primary key (username,document_name)

  6  )

  7  organization index

  8  /

表已建立。

SQL> create table heap

  2  (username varchar2(30),

  3   document_name varchar2(30),

  4   other_data char(100),

  5   constraint heap_pk primary key (username,document_name)

  6  )

  7  /

表已建立。

表中使用CHAR(100)，恰好使表中的行平均寬度大約為130個位元組。兩個表的唯一差別在ORGANIZATION INDEX子句。此子句指示oracle在索引段而不是表段中儲存表資料，所以相應表的資料將以索引結構儲存。

下面使用範例資料填充這兩個表，構造一個迴圈，給ALL_USERS表中每個使用者添加100個文獻。在這裡使用類比現實的方式做這件事情，其中給定使用者文獻不是一次添加，而是隨著其他使用者添加了許多文獻時逐步添加。

SQL> begin

  2   for i in 1 .. 100

  3   loop

  4      for x in (select username from all_users)

  5      loop

  6          insert into heap

  7          (username,document_name,other_data)

  8          values

  9          (x.username,x.username || '_' || i,'x');

 10          insert into iot

 11          (username,document_name,other_data)

 12          values

 13          (x.username,x.username || '_' || i,'x');

 14       end loop;

 15    end loop;

 16    commit;

 17   end;

 18  /

PL/SQL 過程已成功完成。

  這樣將從表中讀取所有資料，也就是說，對於USER1，將讀出對應於他的所有行，然後再讀出對應於USER2的所有行，如此往下。此外，將以兩種方式讀取，一種是利用BULK COLLECT，另一種是利用單行取，目的是瞭解數組處理對效能和延展性有什麼差別，瞭解IOT與堆表有多大的不同。相應的基準測試常式如下：

SQL> alter session set sql_trace=true;

會話已更改。

SQL> declare

  2  type array is table of varchar2(100);

  3  l_array1 array;

  4  l_array2 array;

  5  l_array3 array;

  6  begin

  7  for  i in 1 .. 10

  8  loop

  9      for x in (select username from all_users)

 10      loop

 11          for y in (select * from heap single_row

 12                    where username=x.username)

 13          loop

 14              null;

 15          end loop;

 16          for y in (select * from iot single_row

 17                    where username=x.username)

 18          loop

 19              null;

 20          end loop;

 21          select * bulk collect

 22            into l_array1,l_array2,l_array3

 23            from heap bulk_collect

 24            where username=x.username;

 25          select * bulk collect

 26            into l_array1,l_array2,l_array3

 27            from iot bulk_collect

 28            where username=x.username;

 29       end loop;

 30    end loop;

 31  end;

 32  /

PL/SQL 過程已成功完成。

SQL> alter session set sql_trace=false;

會話已更改。