Oracle编程艺术学习比较--索引组织表

来源：互联网发布：淘宝返利购买编辑：程序博客网时间：2024/05/23 22:49

1. 基本概念

索引组织表（index organized table）简称IOT。
IOT中，数据要根据主键有序地存储。
适合使用IOT的几种情况：
· 表完全由主键组成或者只通过主键来访问一个表。使用IOT，表就是索引，可以节约空间，提高效率。
· 通过外键访问子表，子表使用IOT。通过IOT将相同外键的子表数据物理的存储在同一个位置，查询所需要的物理I/O更少，因为数据都在同一个（几个）块上。
· 经常在主键或者或惟一键上使用BETWEEN查询。数据以某种特定的顺序物理存储，所以获取这些数据时所需的物理I/O更少。

查看创建IOT时候的参数选项。

view plain
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命令最后一次执行的信息。例如：

view plain
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;  

得到结果：

view plain
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，可以看到压缩后的效果如下：

view plain
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上的索引效率稍低。