oracle 索引组织表的深入详解
程序员文章站
2024-01-27 18:29:34
索引组织表(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 t88_pk_id primary key (id)
)
organization index
pctthreshold 20
overflow tablespace users
including name ;
如上例所示，name及之后的列必然被放入溢出列，而其他列根据 pctthreshold 规则。
2、compress子句(键压缩)
与普通的索引一样，索引组织表也可以使用compress子句进行键压缩以消除重复值。
具体的操作是，在organization index之后加上compress n子句
用于压缩索引列，在块级提取公因子，避免重复值。
如：
create table iot(
owner varchar2(8),
object_type varchar2(8),
object_name varchar2(8),
constraint iot_pk primary key(owner, object_type,object_name))
organization index
nocompress;
表示对于每个主键组合都会物理地存储。倘若使用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)进行压缩
索引组织表的维护
(1)、索引组织表可以和普通堆表一样进行insert、update、delete、select操作。
(2)、可使用alter table ... overflow语句来更改溢出段的属性。
alter table indextable add overflow;
(3)、要alter任何overview的属性，都必须先定义overflow，若建表时没有可以新增
alter table t88 pctthreshold 15 including name; --调整overflow的参数
alter table t88 initrans 2 overflow initrans 4; --修改数据块和溢出段的initrans特性
索引组织表的应用
heap table 就是一般的表，获取表中的数据是按命中率来得到的。没有明确的先后之分，在进行全表扫描的时候，并不是先插入的数据就先获取。数据的存放也是随机的，当然根据可用空闲的空间来决定。
iot 就是类似一个全是索引的表，表中的所有字段都放在索引上，所以就等于是约定了数据存放的时候是按照严格规定的，在数据插入以前其实就已经确定了其位置，所以不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。这样在进行查询的时候就可以少访问很多blocks，但是插入的时候，速度就比普通的表要慢一些。索引组织表主要适用于信息检索、空间和olap程序。
索引组织表的适用情况：
1、 代码查找表。
2、 经常通过主码访问的表。
3、 构建自己的索引结构。
4、 加强数据的共同定位，要数据按特定顺序物理存储。
5、 经常用between…and…对主码或唯一码进行查询。
经常更新的表当然不适合iot，因为oracle需要不断维护索引，而且由于字段多索引成本就大；如果不是经常使用主键访问表，就不要使用iot。
iot提供如下的好处：
·提高缓冲区缓存效率，因为给定查询在缓存中需要的块更少。
·减少缓冲区缓存访问，这会改善可扩缩性。
·获取数据的工作总量更少，因为获取数据更快。
·每个查询完成的物理i/o更少。
索引组织表的创建格式如下：
复制代码 代码如下:
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 t88_pk_id primary key (id)
)
organization index
pctthreshold 20
overflow tablespace users
including name ;
如上例所示，name及之后的列必然被放入溢出列，而其他列根据 pctthreshold 规则。
2、compress子句(键压缩)
与普通的索引一样，索引组织表也可以使用compress子句进行键压缩以消除重复值。
具体的操作是，在organization index之后加上compress n子句
用于压缩索引列，在块级提取公因子，避免重复值。
如：
复制代码 代码如下:
create table iot(
owner varchar2(8),
object_type varchar2(8),
object_name varchar2(8),
constraint iot_pk primary key(owner, object_type,object_name))
organization index
nocompress;
表示对于每个主键组合都会物理地存储。倘若使用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)进行压缩
索引组织表的维护
(1)、索引组织表可以和普通堆表一样进行insert、update、delete、select操作。
(2)、可使用alter table ... overflow语句来更改溢出段的属性。
复制代码 代码如下:
alter table indextable add overflow;
(3)、要alter任何overview的属性，都必须先定义overflow，若建表时没有可以新增
复制代码 代码如下:
alter table t88 pctthreshold 15 including name; --调整overflow的参数
alter table t88 initrans 2 overflow initrans 4; --修改数据块和溢出段的initrans特性
索引组织表的应用
heap table 就是一般的表，获取表中的数据是按命中率来得到的。没有明确的先后之分，在进行全表扫描的时候，并不是先插入的数据就先获取。数据的存放也是随机的，当然根据可用空闲的空间来决定。
iot 就是类似一个全是索引的表，表中的所有字段都放在索引上，所以就等于是约定了数据存放的时候是按照严格规定的，在数据插入以前其实就已经确定了其位置，所以不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。这样在进行查询的时候就可以少访问很多blocks，但是插入的时候，速度就比普通的表要慢一些。索引组织表主要适用于信息检索、空间和olap程序。
索引组织表的适用情况：
1、 代码查找表。
2、 经常通过主码访问的表。
3、 构建自己的索引结构。
4、 加强数据的共同定位，要数据按特定顺序物理存储。
5、 经常用between…and…对主码或唯一码进行查询。
经常更新的表当然不适合iot，因为oracle需要不断维护索引，而且由于字段多索引成本就大；如果不是经常使用主键访问表，就不要使用iot。
iot提供如下的好处：
·提高缓冲区缓存效率，因为给定查询在缓存中需要的块更少。
·减少缓冲区缓存访问，这会改善可扩缩性。
·获取数据的工作总量更少，因为获取数据更快。
·每个查询完成的物理i/o更少。