索引组织表 (IOT)(index-organized table)

一 索引组织表定义：

IOT 就是类似一个全是索引的表，表中的所有字段都放在索引上，所以就等于是约定了数据存放的时候是按照严格规定的，在数据插入以前其实就已经确定了其位置。这样在进行查询的时候就 可以少访问很多 blocks，但是插入的时候，速度就比普通的表要慢一些。

适用于信息检索、空间和OLAP 程序。

二 索引组织表的适用情况

    1、 代码查找表。

    2、 经常通过主码访问的表。

    3、 构建自己的索引结构。

    4、 加强数据的共同定位，要数据按特定顺序物理存储。

    5、 经常用between…and…对主码或唯一码进行查询。

三 索引组织表的非适用情况

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

    2、不是经常使用主键访问表。

四 IOT 的好处

1、由于索引项和数据存储在一起，所以无论是基于主键的等值查询还是范围查询都能大大节省磁盘访问时间。

2、为了能够更快地访问那些频繁访问的列，可以使用溢出存储选项将那些访问不频繁的列放在B树叶结点数据块之外的溢出堆空间中。这样一来便可以得到更小的B树，以及包含更多行的叶结点

3、和堆组织表和索引不同，主键不需要被存储两次。

4、ROWID伪列是基于主键值的逻辑rowid，而不是物理rowid，即使表被重新组织过，造成了基表行的迁移，二级索引仍然可用，不需要重建。

注：

1、  Oracle使用rowid数据类型存储行地址,rowid可以分成两种，分别适于不同的对象：

(1)     Physical rowids：存储ordinary table,clustered table,table partition and subpartition,indexe,index partition and subpartition；

(2)     Logical rowids :存储IOT的行地址

五 索引组织表属性

 1、OVERFLOW子句(行溢出)

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

 溢出段的设置有两种格式：

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

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

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

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

    create table t1(

       ID varchar2 ( 10 ),

       NAME varchar2 ( 20 ),

       constraint pk_id primary key ( ID )

      )

    organization index

      PCTTHRESHOLD 20

      overflow tablespace users

      INCLUDING name ;

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

六 索引组织表的维护  

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

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

altertable t88 addoverflow; --新增一个overflow

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

alter table t88 pctthreshold15includingname; --调整overflow的参数

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

七 创建索引段与溢出段

SQL> create table t1

     (a int,

      b varchar2(4000) default rpad('*',4000,'*'),

      c varchar2(3000) default rpad('*',3000,'*'),

          constraint pk_aaa primary key(a)

     )

         organization index; 

create table t1

*

ERROR at line 1:

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

SQL> create table t1

     (a int,

      b varchar2(4000) default rpad('*',4000,'*'),

      c varchar2(3000) default rpad('*',3000,'*'),

          constraint pk_aaa primary key(a)

     )

         organization index

                 PCTTHRESHOLD 20

         overflow tablespace users;  

Table created.

SQL> insert into t1(a) values(1);

1 row created.

SQL> insert into t1(a) values(2);

1 row created.

SQL> insert into t1(a) values(3);

1 row created.

SQL> select a from t1;

         A

----------

         1

         2

         3

SQL> delete t1 where a=2;

1 row deleted.

SQL> insert into t1(a) values(4);

1 row created.

SQL> select a from t1;

         A

----------

         1

         3

         4

SQL> set autotrace on

SQL> select a from t1 where a=3;

         A

----------

         3

Execution Plan

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

Plan hash value: 2488573284

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

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

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

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

|*  1 |  INDEX UNIQUE SCAN| PK_AAA |     1 |    13 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("A"=3)

Statistics

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

          0  recursive calls

          0  db block gets

          1  consistent gets

          0  physical reads

          0  redo size

        519  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

IOT就是存储在一个索引结构中的表（没有数据段）

1、叶块不保存ROWID，而直接保存数据（必须有主键、没有回表的概念）。

2、存储表的索引列和其它所有列的值。

3、必须指定主键——插入数据时，会根据主键列进行B树索引排序，存储在索引中。

SQL> select count(*) from dba_segments where segment_name='T1' and owner='SYS';

  COUNT(*)

----------

         0

SQL> col owner for a5

SQL> col index_name for a8

SQL> col index_type for a12

SQL> col table_name for a5

SQL> select owner,index_name,index_type,table_name from dba_indexes where table_name='T1';

OWNER INDEX_NA INDEX_TYPE   TABLE

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

SYS   PK_AAA   IOT - TOP    T1

SQL> analyze table t1 compute statistics;

SQL> select owner,table_name,num_rows from dba_tables where table_name='T1';

     OWNER    TABLE_NAME  NUM_ROWS

1       GGATE      T1    

2       CHEN        T1    

3       SYS       T1              4

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！