1. 基本概念
索引组织表(index organized table)简称IOT。
IOT中,数据要根据主键有序地存储。
适合使用IOT的几种情况:
· 表完全由主键组成或者只通过主键来访问一个表。使用IOT,表就是索引,可以节约空间,提高效率。
· 通过外键访问子表,子表使用IOT。通过IOT将相同外键的子表数据物理的存储在同一个位置,查询所需要的物理I/O更少,因为数据都在同一个(几个)块上。
· 经常在主键或者或惟一键上使用BETWEEN查询。数据以某种特定的顺序物理存储,所以获取这些数据时所需的物理I/O更少。
查看创建IOT时候的参数选项。
- tony@ORA11GR2> create table t(x int primary key, y clob) organization index;
- Table created.
- tony@ORA11GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;
- DBMS_METADATA.GET_DDL('TABLE','T')
- --------------------------------------------------------------------------------
- CREATE TABLE "TONY"."T"
- ( "X" NUMBER(*,0),
- "Y" CLOB,
- PRIMARY KEY ("X") ENABLE
- ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
- STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
- FAULT CELL_FLASH_CACHE DEFAULT)
- TABLESPACE "USERS"
- PCTTHRESHOLD 50
- LOB ("Y") STORE AS BASICFILE (
- TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 8192 RETENTION
- NOCACHE LOGGING
- STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC
- HE DEFAULT))
IOT没有PCTUSED子句,但是有PCTFREE。这是因为IOT中数据放在哪个块上不是根据未用空间大小,而是根据主键索引决定的。
但是对于溢出段(下面会提到),PCTFREE和PCTUSED对于IOT的意义将与对于堆表一样。
索引压缩
选项NOCOMPRESS对索引一般都可用,它和表压缩不同,它告诉Oracle把构成主键的每个值分别存储在各个索引条目中(也就是不压缩)。
例如,主键在A、B和C列上,则A、B和C列中每一次出现的值都会物理地存储。
NOCOMPRESS反过来就是COMPRESS N,在此N是一个整数,表示要压缩的列数。这样可以避免重复值,并在块级提取“公因子”(factor out)。例如如果使用COMPRESS 2,那么A和B列上如果有重复的值,就只会存储1次。
使用索引压缩会减少物理I/O,但是占用更多的CPU时间来处理索引,需要在两者之间权衡。
可以使用ANALYZE INDEX VALIDATE STRUCTURE命令来得到最优的压缩方案。这个命令会填写一个名为INDEX_STATS的动态性能视图,其中最多只包含一行,即这个ANALYZE命令最后一次执行的信息。例如:
- create table iot (owner, object_type, object_name,
- constraint iot_pk primary key(owner, object_type, object_name))
- organization index nocompress
- as select distinct owner, object_type, object_name from all_objects;
- analyze index iot_pk validate structure;
- select lf_blks, br_blks, used_space, opt_cmpr_count,
- opt_cmpr_pctsave from index_stats;
得到结果:
- LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
- ------- ---------- ---------- -------------- ----------------
- 348 3 2496647 2 33
LF_BLKS表示索引使用了348个叶子块(即数据所在的块);
BR_BLKS表示索引使用了3个分支块(在索引结构中导航所用的块)来找到这些叶子块;
USED_SPACE表示使用的空间大约是2496647字节;
OPT_CMPR_COUNT(最优压缩数)表示最优的压缩设置为COMPRESS 2;
OPT_CMPR_PCTSAVE(最优的节省压缩百分比)表示如果设置为COMPRESS 2,就能节省大约33%的存储空间。
可以用alter table iot move compress 2;来重建IOT,之后ANALYZE INDEX,可以看到压缩后的效果如下:
- LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
- ---------- ---------- ---------- -------------- ----------------
- 233 1 1671914 2 0
溢出段(OVERFLOW子句)
索引一般在表的一个列子集上。通常索引块上的行数比表块上的行数会多很多,这对索引是有利的,否则Oracle需要花费大量的时间来维护索引,因为每个INSERT或UPDATE都可能导致索引块分解,以容纳新数据。
OVERFLOW子句允许你建立另一个段(这就使得IOT成为一个多段对象),根据我们的设定,IOT的行数据的一部分可以溢出到这个段中,这样可以让索引叶子块(包含具体索引数据的块)能够高效地存储数据。
需要注意,构成主键的列不能溢出,它们必须直接放在叶子块上。
建立IOT时,最关键的是适当地分配数据,即哪些数据存储在索引块上,哪些数据存储在溢出段上。
使用溢出段的条件可以采用两种方式来指定:
· PCTTHRESHOLD
行中的数据量超过块的这个百分比时,行中余下的列将存储在溢出段中。
例如,如果PCTTHRESHOLD是10%,而块大小是8KB,长度大于800字节的行就会把其中一部分存储在溢出段中。
Oracle会从最后一列开始向前查找,直到不包括主键 的最后一列,得出哪些列需要存储在溢出段中。
例如:
create table iot1 (x int primary key, y date, z varchar2(2000))
organization index pctthreshold 10 overflow;
· INCLUDING
行中从第一列直到INCLUDING子句所指定列(也包括这一列)的所有列都存储在索引块上,余下的列存储在溢出段中。
例如:
create table iot2 (x int primary key, y date, z varchar2(2000))
organization index including y overflow;
IOT上建立索引
IOT本身可以再建索引,就像在索引之上再加索引,这称为二次索引(secondary index)。 IOT上的索引可以使用非主键列提供IOT数据的快速、高效访问。
正常情况下,索引包含了所指向的行的物理地址,即rowid。而IOT二次索引无法做到这一点。因为IOT中的行可以大量移动,而且它不像堆组织表中的行那样“迁移”。IOT中的行肯定在索引结构中的每个位置上,这取决于它的主键值;索引本身的大小和形状发生改变时行就会移动。
因此,Oracle引入了一个逻辑rowid(logical rowid),这些逻辑rowid根据IOT主键建立。因此,与常规表相比,IOT上的索引效率稍低。
2.