索引组织表

因为表的数据分布跟索引不匹配，造成了很高的CLUSTERING_FACTOR
那我们有什么办法能让表的数据和索引的数据分布上变得完全一致呢？
1.我们可以将表进行重构，按索引键值的顺序。
2.那就是IOT，索引组织表：
索引组织表将索引和表存放在一起

如上图所示，仍然是20个索引值，每个值对应的表数据行，都放在了一起，组织了一种新的结构，叫索引组织表。
也就是既有索引特征，有包含了表中所有的数据
这样的表结构，表的数据块分布跟索引是不是安全一致？
我们要访问索引1到20
访问4个数据块？还是可能20个数据块？
只要访问4次就可以了吧
这里我们看到IOT把索引段和表段整合到一个IOT段。
这样不需要两份索引KEY的值，节约了空间，特别是索引列所占空间占表的所有列所占空间的绝大多数的时候非常有效果。
（索引列A，B，C占100字节，非索引列D，E占20字节），那索引列（KEY）占所有列空间１００／（２０+１００）＝５／６，那么IOT空间将大大小于索引加表占用的空间。
o 表 + 索引 = 100+100+20
o IOT = 100 + 20
那如果索引列长度<IOT可以把它溢出到溢出段来节约索引列的访问效率。
溢出相关参数
⊙ PCTTHRESHOLD
⊙ INCLUDING
溢出的效果图：

 
而IOT段有一个POINTER，也就是指针，指向溢出段
注意IOT表是按主键索引顺序存放的，所以聚簇因子等于块数，而不是行数。
所以在IOT，通过索引来访问会非常快，一般用在数据仓库。

【转】Oracle 索引组织表（IOT）
索引组织表（IOT）有一种类B树的存储组织方法。普通的堆组织表是以一种无序的集合存储。而IOT中的数据是按主键有序的存储在B树索引结构中。与一般B树索引不同的的是，在IOT中每个叶结点即有每行的主键列值，又有那些非主键列值。

     在IOT所对应的B树结构中，每个索引项包括而不是ROWID，对于普通堆组织表，oracle会有对应的索引与之对应，且分开存储。换句话说，IOT即是索引，又是实际的数据。

    索引组织表(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

 

 

索引组织表的应用

    Heap Table 就是一般的表，获取表中的数据是按命中率来得到的。没有明确的先后之分，在进行全表扫描的时候，并不是先插入的数据就先获取。数据的存放也是随机的，当然根据可用空闲的空间来决定。

 

     IOT 就 是类似一个全是索引的表，表中的所有字段都放在索引上，所以就等于是约定了数据存放的时候是按照严格规定的，在数据插入以前其实就已经确定了其位置，所以 不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。这样在进行查询的时候就可以少访问很多blocks，但是插入的时候，速度就比普通 的表要慢一些。
适用于信息检索、空间和OLAP程序。

 

    索引组织表的适用情况：
    1、 代码查找表。
    2、 经常通过主码访问的表。
    3、 构建自己的索引结构。
    4、 加强数据的共同定位，要数据按特定顺序物理存储。
    5、 经常用between…and…对主码或唯一码进行查询。数据物理上分类查询。如一张订单表，按日期装载数据，想查单个客户不同时期的订货和统计情况。

 

    经常更新的表当然不适合IOT，因为oracle需要不断维护索引，而且由于字段多索引成本就大。

 

    如果不是经常使用主键访问表，就不要使用IOT

     IOT和普通表对于应用程序来说，例如sql查询语句，是没有区别的。也就是说oracle中对表的组织形式对应用来说是透明的。
使用IOT的好处：
1、由于索引项和数据存储在一起，所以无论是基于主键的等值查询还是范围查询都能大大节省磁盘访问时间。
2、为了能够更快地访问那些频繁访问的列，可以使用溢出存储选项将那些访问不频繁的列放在B树叶结点数据块之外的溢出堆空间中。这样一来便可以得到更小的B树，以及包含更多行的叶结点
3、和堆组织表和索引不同，主键不需要被存储两次。
4、ROWID伪列是基于主键值的逻辑rowid，而不是物理rowid，即使表被重新组织过，造成了基表行的迁移，二级索引仍然可用，不需要重建。

注：
1、 Oracle使用rowid数据类型存储行地址,rowid可以分成两种，分别适于不同的对象，Physical rowids：存储ordinary table,clustered table,table partition and subpartition,indexe,index partition and subpartition；Logical rowids :存储IOT的行地址
2、每个表在oracle内部都有一个ROWID伪列,它在所有sql中无法显示，不占存储空间;它用于从表中查询行的地址或者在where中进行参照,一个例子如下：
    SELECT ROWID, last_name FROM employees;          Oracle内部使用保留在ROWID伪列中的值构建索引结构,rowid伪列不存储在数据库中,它不是数据库 表的数据,（从database及table的逻辑结构来说）。事实上,在物理结构上,每行由一个或多个row pieces组成,每个row piece的头部包含了这个piece的address,即rowid.从这个意义上来说，rowid还是占了磁盘空间的.
3、二级索引：也可理解为聚集索引，好比是我们人查字典时自已会使用的索引。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20576390/viewspace-709340/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20576390/viewspace-709340/