1、索引组织表
索引组织表(index organized table, IOT)就是存储在一个索引结构中的表。存储在堆中的表是无组织的(也就是说,只要有可用的空间,数据可以放在任何地方),IOT中的数据则按主键存储和排序。对你的应用来说,IOT表和一个“常规”表并无二致。
IOT有什么意义呢?使用堆组织表时,我们必须为表和表主键上的索引分别留出空间。而IOT不存在主键的空间开销,因为索引就是数据,数据就是索引,二者已经合二为一。
但是,IOT带来的好处并不止于节约了磁盘空间的占用,更重要的是大幅度降低了I/O,减少了访问缓冲区缓存(尽管从缓冲区缓存获取数据比从硬盘读要快得多,但缓冲区缓存并不免费,而且也绝对不是廉价的。每个缓冲区缓存获取都需要缓冲区缓存的多个闩,而闩是串行化设备,会限制应用的扩展能力)
IOT适用的场合有:
1、完全由主键组成的表。这样的表如果采用堆组织表,则表本身完全是多余的开销,因为所有的数据全部同样也保存在索引里,此时,堆表是没用的。
2、代码查找表。如果你只会通过一个主键来访问一个表,这个表就非常适合实现为IOT.
3、如果你想保证数据存储在某个位置上,或者希望数据以某种特定的顺序物理存储,IOT就是一种合适的结构。
IOT提供如下的好处:
·提高缓冲区缓存效率,因为给定查询在缓存中需要的块更少。
·减少缓冲区缓存访问,这会改善可扩缩性。
·获取数据的工作总量更少,因为获取数据更快。
·每个查询完成的物理I/O更少。
如果经常在一个主键或唯一键上使用between查询,也是如此。如果数据有序地物理存储,就能提升这些查询的性能。
索引组织表(IOT)不仅可以存储数据,还可以存储为表建立的索引。索引组织表的数据是根据主键排序后的顺序进行排列的,这样就提高了访问的速度。但是这是由牺牲插入和更新性能为代价的(每次写入和更新后都要重新进行重新排序)。索引组织表的创建格式如下:
create table indexTable(
ID varchar2 ( 10 ),
NAME varchar2 ( 20 ),
constraint pk_id primary key ( ID )
) organization index ;
式注意两点:
● 创建IOT时,必须要设定主键,否则报错。
● 索引组织表实际上将所有数据都放入了索引中。
IOT表的rowid是逻辑上的,因为IOT表中的行的位置是在不断变化的(例如插入新的行,有可能带来其它行的位置移动)
Heap Table 就是一般的表,获取表中的数据是按命中率来得到的。没有明确的先后之分,在进行全表扫描的时候,并不是先插入的数据就先获取。数据的存放也是随机的,当然根据可用空闲的空间来决定。
IOT 就是类似一个全是索引的表,表中的所有字段都放在索引上,所以就等于是约定了数据存放的时候是按照严格规定的,在数据插入以前其实就已经确定了其位置,所以不管插入的先后顺序,它在那个物理上的那个位置与插入的先后顺序无关。这样在进行查询的时候就可以少访问很多blocks,但是插入的时候,速度就比普通的表要慢一些。适用于信息检索、空间和OLAP程序。
索引组织表的适用情况:
1、 代码查找表。
2、 经常通过主码访问的表。
3、 构建自己的索引结构。
4、 加强数据的共同定位,要数据按特定顺序物理存储。
5、 经常用between…and…对主码或唯一码进行查询。
数据物理上分类查询。如一张订单表,按日期装载数据,想查单个客户不同时期的订货和统计情况。经常更新的表当然不适合IOT,因为oracle需要不断维护索引,而且由于字段多索引成本就大。如果不是经常使用主键访问表,就不要使用IOT 。
下面看一个实验:
SQL> create table t1
2 (
3 owner varchar2(30) not null,
4 object_name varchar2(30) not null,
5 subobject_name varchar2(30),
6 object_id number not null,
7 data_object_id number,
8 object_type varchar2(19),
9 created date not null,
10 last_ddl_time date not null,
11 timestamp varchar2(19),
12 status varchar2(7),
13 temporary varchar2(1),
14 generated varchar2(1),
15 secondary varchar2(1),
16 namespace number not null,
17 edition_name varchar2(30),
18 primary key (object_id)
19 );
表已创建。
SQL> create table t2
2 (
3 owner varchar2(30) not null,
4 object_name varchar2(30) not null,
5 subobject_name varchar2(30),
6 object_id number not null,
7 data_object_id number,
8 object_type varchar2(19),
9 created date not null,
10 last_ddl_time date not null,
11 timestamp varchar2(19),
12 status varchar2(7),
13 temporary varchar2(1),
14 generated varchar2(1),
15 secondary varchar2(1),
16 namespace number not null,
17 edition_name varchar2(30),
18 primary key (object_id)
19 )organization index;
表已创建。
SQL> insert into t1 select * from all_objects;
已创建71046行。
SQL> insert into t2 select * from all_objects;
已创建71046行。
SQL> commit;
提交完成。
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true);
PL/SQL 过程已成功完成。
创建了两个表t1和t2,t1是普通的堆表,t2是索引表。
SQL> select * from t1 where object_id=100;
执行计划
----------------------------------------------------------
Plan hash value: 2716337747
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 97 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0021132 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
统计信息
----------------------------------------------------------
660 recursive calls
0 db block gets
148 consistent gets
17 physical reads
0 redo size
1300 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from t2 where object_id=100;
执行计划
----------------------------------------------------------
Plan hash value: 444554239
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_102114 | 1 | 97 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=100)
统计信息
----------------------------------------------------------
636 recursive calls
0 db block gets
142 consistent gets
3 physical reads
0 redo size
1393 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
可以看见,普通表的执行计划中有“TABLE ACCESS BY INDEX ROWID”,表示回表了的。而且普通表的consistent gets比索引表多。
2、索引组织表属性
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