最近群里有讨论schemas的设计，技术手段还是比较多的，分区表，分区索引（本地分区索引和全局分区索引），b tree和bitmap tree，物化视图，临时表，cluster。这里还有一个比较重要的是IOT表，也就是常说的索引组织表。

对于索引组织表用的还是比较少，仅自己的理解说下，一门技术手段需要知道其缘由，然后用在合适的地方，说来简单，其实能融会贯通并不容易，上面所说的技术手段也是需要长期的用例和维护才能理解的。

应用透明：

IOT存储在索引结构中的表，普通的表（也就是所说的堆表）存放数据都是无组织的，也就是能存储的空间随机利用，IOT中的数据则按主键存储和排序，而和分区表一样对应用透明。

IOT的结构：

IOT是把索引和数据存储在相同位置上的上，从b tree索引的结构上来看，就是叶块不再只存储rowid和键值，而是存储了整行的值，IOT不再利用rowid而是利用主键来查找该数据行。个人更觉得把IOT理解为表的索引化，也就是对应用而言是表，可以进行查询更新，而其存储结构则和index相似。

IOT和普通index的rowid查询的消耗：

虽然利用IOT的结构特性是可以减去rowid来查找block的消耗，其实这个应该不是最主要的原因，主键和唯一索引的查询本来就非常快，不外乎是普通的堆表还是IOT。其实消耗是源于大范围主键查询下，例如between and的，由于堆表的数据存储是不顺序的，利用index也会多次通过rowid去随机读取表段的block，这个消耗其实是一笔很大的开销，而通过IOT则可以有效的避免这次开销，不过相对来说IOT也有其局限的地方。

IOT的适用局限：

IOT一般用于主键查找的表，而且主键占据数据行较大的比例，更新少的表的应用，表中必须存在主键且无法使用cluster。

占据数据行长度较大和更新较少的一点分析：

根据b-tree的结构，由于IOT下整行数据都存储在leaf node叶块上，那么b—tree随着数据的增加而不断生长同时，叶块就有可能分裂，致使某个主键值无法继续存储在同一个块中，那么当然可想而知，表中的数据行的rowid地址会随时变化，IOT则利用主键来代替rowid。

IOT的补充索引：

IOT允许创建补充索引，不过补充索引确是利用主键来查询数据行的，这个可以从上面的分析中得出，补充索引也无法利用rowid的获取数据行，在早期的oracle 8I的版本中甚至完全不支持补充索引。其实oracle利用补充索引的rowid来获取数据时，会先试着通过rowid在IOT中获取，如果获取的数据行被判定为不正确，则舍弃逻辑rowid而是利用主键再次读取，物理的rowid和主键构成了一个可以完全正确获取数据行的逻辑rowid。

何不直接利用主键获取：

刚开始我也认为oracle有点多此一举，既然物理的rowid不准确，何不直接利用主键来获取了，我们清楚主键比rowid的读取效率还是低一点的，更多时候IOT并不是经常更新，发生索引分裂的概率并不是很大，而如果我们经常大批量的更新，那么IOT也不适合这种场景了。

看看逻辑rowid的格式：

SQL> create table iot01(id number,

2 name varchar2(100),

3 primary key(id))

4 organization index;

Table created.

SQL> insert into iot01 select rownum,object_name from dba_objects;

52212 rows created.

SQL> col name for a20

SQL> select id,name,rowid from iot01 where rownum<3;

ID NAME ROWID

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

1 ICOL$ *BABA9XMCwQL+

2 I_USER1 *BABA9XMCwQP+

溢出区：

应该对IOT有些基本的了解了，虽然IOT下索引和数据一起读取可以减少ＩＯ，但是IOT很有可能发生块的分裂和存储密度降低，当行的长度越长时，数据增加的越快，则越容易发生上述情况，也就引出了下面所要叙述的溢出区Overflow Area，其实感觉和oracle的分区表思想有点相似，拆分，可以发现oracle mysql等乃至别的一些技术拆分思想也无处不在，溢出区把不经常使用的列存储在溢出段中，对于经常使用的列则存储在IOT所有的物理段中。

行链接和溢出区

行链接是会增加IO消耗的，而且貌似我们用户根本无法控制行迁移，而在IOT下我们是可以通过表的应用和结构控制行链接，还可以有效的利用的行链接，需要设计合理的列的属性和顺序，通过溢出段来有效的分离常用列和不常用列，减少系统的IO消耗。

Create table xiaoyu_iot(id number,name varchar2(100),content varchar2(4000),e_date date,

Primary key(id))

Organization index

Pctthreshold 30

Including content

Overflow tablespace USERS

上述的ddl语句中，pctthreshold表示数据行占用索引块的百分比，也就是数据行大多pctthreshold/100*db_block_size时，content和其后面的列将存储在溢出段中，如果没有设置overflow区域，那么大于pctthreshold的部分将不允许插入，pctthreshold的取值范围为0~50。

Oracle会根据你的表结构来判断是否需要设置overflow，下面的表的数据行的长度相对比较大，需要设置overflow区。

SQL> Create table xiaoyu_iot01d number,name varchar2(100),content varchar2(4000),e_date date,

2 Primary key(id))

3 Organization index

4 ;

Create table xiaoyu_iot(id number,name varchar2(100),content varchar2(4000),e_date date,

*

ERROR at line 1:

ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces

Xiaoyu_iot表是IOT表，xiaoyu_no_iot是以ID为主键的堆表，现在测试大范围查找的效率：

SQL> insert into xiaoyu_no_iot select rownum,object_type,rpad(object_Name,4000,'x'),last_ddl_time from dba_objects;

52217 rows created.

SQL> commit;

Commit complete.

SQL> explain plan for select * from xiaoyu_iot where id between 1000 and 6000;

Explained.

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2098152366

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

------

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

|

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

------

PLAN_TABLE_OUTPUT

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

| 0 | SELECT STATEMENT | | 7327 | 14M| 6 (0)| 00:0

0:01 |

|* 1 | INDEX RANGE SCAN| SYS_IOT_TOP_63115 | 7327 | 14M| 2 (0)| 00:0

0:01 |

SQL> insert into xiaoyu_no_iot select rownum,object_type,rpad(object_Name,4000,'x'),last_ddl_time from dba_objects;

52217 rows created.

SQL> commit;

Commit complete.

SQL> explain plan for select * from xiaoyu_no_iot where id between 1000 and 6000;

Explained.

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1520865053

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

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

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

)| Time |

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

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

PLAN_TABLE_OUTPUT

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

| 0 | SELECT STATEMENT | | 4144 | 8401K| 70 (0

)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| XIAOYU_NO_IOT | 4144 | 8401K| 70 (0

)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | SYS_C0011219 | 4144 | | 9 (0

)| 00:00:01