Oracle之索引组织表

假设表A，有5个字段，其中有一个名为ID的字段为主键。日常业务中，经常需要根据ID字段的值，查找该表上其它字段（一个或多个字段，甚至全部字段）上的值。如果我们只在ID列上创建索引，当执行
select id,字段2，字段3，...
from 表A
where id=(>,<,>=,<=等逻辑操作）xxx;
时，如果结果集是少数记录，那么极可能是通过ID列上的索引获取ROWID，然后回表去读取相应ROWID的行记录，以获取其它字段上的信息。如果这种业务很频繁，为了这个回表的操作就可能带来不小的性能提升。所以，我们很自然的会想到用复合索引，把ID列以外的其它列都编进去，这样就不需要回表了，因为索引中这些列都已经有了。但这样一来，索引的大小几乎与表一样大了，相当于在数据库里同时拥有两张表A，只不过一个是根据ID排了序的，一个是未排序的。这时，如果我们用索引组织表，那么数据库就会以索引的方式来保存表A，数据库中只有一张表的空间使用，不仅节省了空间，而且查询的性能上与创建复合索引的方法有近似的提升。
但是，索引组织表也有弱点，由于这种表是以索引的方式来存储表中的数据，所以，索引叶子块中会保存相应行中所有字段的值。若这些字段的长度累计较大时，每个叶子块可以存储的行数就会很少，就会需要更多的叶子块来存储行记录，而叶子块越多，索引的深度（或层级）就越高，每次访问时需要访问的分支块就越多，即一次访问需要访问的总的数据块也就越多。当过多时，就会把消除回表操作所带来的好处抵消，基至有过之而无不及，性能反而会更差。同时，如果索引组织表的主键值经常更改，意味着相应的行就需要在不同的块之间进行移动。如果索引组织表频繁进行增删改，也会像索引那样，引起索引结构的维护工作（比如分支块的分裂，索引层级的增加，以及索引碎片的出现等）增加，也会引起性能的下降。

总结：

索引组织表：以索引的方式保存该表，类似复合索引的用法

缺点：索引块保存所有的字段的信息，如果字段长度过大，每个块的存放数量有限，就需要更多的叶子来保存数据，造成访问效率降低。

如果主键频繁修改，对应的行业就需要盘频繁修改，行需要在不同的块之间相互移动。

综上，一般情况下，只有在满足以下条件时，才应该考虑使用索引组织表：
1、表的宽度（即一行的数据长度）有限。
2、表的主键不会或极少更改。
3、表主要用于查询，DML操作较少。

4、大部分的业务需求是根据主键查询行中其它列上的信息。

    索引组织表(IOT)不仅可以存储数据，还可以存储为表建立的索引。索引组织表的数据是根据主键排序后的顺序进行排列的，这样就提高了访问的速度。但是这是由牺牲插入和更新性能为代价的(每次写入和更新后都要重新进行重新排序)。

 

    索引组织表的创建格式如下：

    create table indexTable(

       ID varchar2 ( 10 ),

       NAME varchar2 ( 20 ),

       constraint pk_id primary key ( ID )

      )

     organization index ;式

 

    注意两点：

    ● 创建IOT时，必须要设定主键，否则报错。

    ● 索引组织表实际上将所有数据都放入了索引中。

 

 

索引组织表属性

 

    1、OVERFLOW子句(行溢出)

 

    因为所有数据都放入索引，所以当表的数据量很大时，会降低索引组织表的查询性能。此时设置溢出段将主键和溢出数据分开来存储以提高效率。溢出段的设置有两种格式：

 

      PCTTHRESHOLD n ：制定一个数据块的百分比，当行数据占用大小超出时，该行的其他列数据放入溢出段

      INCLUDING column_name ：指定列之前的列都放入索引块，之后的列都放到溢出段

 

      ● 当行中某字段的数据量无法确定时使用PCTTHRESHOLD。

      ● 若所有行均超出PCTTHRESHOLD规定大小，则考虑使用INCLUDING。

     

    create table t88(

       ID varchar2 ( 10 ),

       NAME varchar2 ( 20 ),

       constraint pk_id primary key ( ID )

      )

    organization index

      PCTTHRESHOLD 20

      overflow tablespace users

      INCLUDING name ;

 

    ● 如上例所示，name及之后的列必然被放入溢出列，而其他列根据 PCTTHRESHOLD 规则。

 

 

    2、COMPRESS子句(键压缩)

 

    与普通的索引一样，索引组织表也可以使用COMPRESS子句进行键压缩以消除重复值。

    具体的操作是，在organization index之后加上COMPRESS n子句

 

    ● n的意义在于：指定压缩的列数。默认为无穷大。

 

    例如对于数据(1,2,3)、(1,2,4)、(1,2,5)、(1,3,4)、(1,3,5)时

    若使用COMPRESS则会将重复出现的(1,2)、(1,3)进行压缩

    若使用COMPRESS 1时，只对数据(1)进行压缩

 

 

索引组织表的维护

 

    索引组织表可以和普通堆表一样进行INSERT、UPDATE、DELETE、SELECT操作。

    可使用ALTER TABLE ... OVERFLOW语句来更改溢出段的属性。

 

    altertable t88 addoverflow; --新增一个overflow

 

    ● 要ALTER任何OVERVIEW的属性，都必须先定义overflow，若建表时没有可以新增

 

    altertable t88 pctthreshold15includingname; --调整overflow的参数

    altertable t88 initrans2overflowinitrans4; --修改数据块和溢出段的initrans特性

 

    ● 关于initrans的概念参考 http://space.itpub.net/265709/viewspace-166534