Oracle IOT

Index Organized table by itself is a B-tree index. Index key is the primary key and the rest of columns are index values. 

The rows are stored in the primary key order. 

IOT provides fast access to a specific row by primary ke or the prefix of primary key. 

Any insert/update/delete will cause the IOT rebuilt, therefore use IOT only when data is rarely changed.

IOT cannot contain virtual columns.

Row overflow Area

The index entries can be large as they contain an entire row. Row overflow area is a seperate segment. If a row overflow area is specified, database divides a row into two parts:

The index entry: contains primary key, a physical rowid pointing to the overflow part of the row, and optionally a few of non-key columns. This part is saved in the b-tree segment.

Overflow part: contains remaining, non-key columns, stored in the row overflow area segment.

假设有个表存储了城市及号段模式。表里数据几乎不跟新，而且只有两列。是个非常好的做为索引组织表的例子。

desc msisdn_city
Name           Null     Type         
-------------- -------- ------------ 
MSISDN_PATTERN NOT NULL VARCHAR2(50) 
CITY_ID        NOT NULL NUMBER  

我们来比较下分别用堆表和索引组织表时，他们的select性能。我们用了tkprof报告。

msisdn_pattern是堆表，主键：city_id和msisdn_pattern。msisdn_city_iot是索引组织表，主键一样。

select /*my1*/ msisdn_pattern
from
 msisdn_city where city_id=2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0          27
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0          27

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows     Row Source Operation
-------  ---------------------------------------------------
     27  INDEX RANGE SCAN MSISDN_CITY_PK (cr=3 pr=0 pw=0 time=0 us cost=1 size=66 card=3)(object id 81066)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
********************************************************************************

SQL ID: 0ba8ury1m3twq
Plan Hash: 3216661436
select /*my1*/ msisdn_pattern
from
 msisdn_city_iot where city_id=2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          2          0          27
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          2          0          27

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows     Row Source Operation
-------  ---------------------------------------------------
     27  INDEX FULL SCAN MC_IOT_PK (cr=2 pr=0 pw=0 time=0 us cost=1 size=189 card=27)(object id 81068)

可以看到堆表的查询多一个IO，这个IO是找到索引中的rowid，通过rowid去表里找时引发的。而IOT表本身即为索引，不需要这个IO。而且，堆表需要两个segment，一个是table segment，一个为pk index segment。而主键是表的所有字段。因而存在不必要的冗余。