再上一篇:10.2术语
上一篇:10.3堆组织表
主页
下一篇:10.5索引聚簇表
再下一篇:10.6散列聚簇表
文章列表

10.4索引组织表

Oracle 9i 10g编程艺术:深入数据库体系结构

索引组织表(index organized table,IOT) 就是存储在一个索引结构中的表。存储在堆中的表是 无组织的(也就是说,只要有可用的空间,数据可以放在任何地方),IOT中的数据则按主键存储和排序。 对 你的应用来说,IOT表现得与一个“常规”表并无二致;还是要使用SQL正常地访问这些表。IOT对信 息获取、空间应用和OLAP应用特别有用。
IOT有 什么意义?实际上,可以反过来问:堆组织表有什么意义?由于一般认为关系数据库中的所 有表都有一个主键,堆组织表难道不是在浪费空间吗?使用堆组织表时, 我们必须为表和表主键上的索引 分别留出空间。而IOT则不存在主键的空间开销,因为索引就是数据,数据就是索引,两者已经合二为一。 事实上,索引是一个复 杂的数据结构,需要大量的工作来管理和维护,而且随着存储的行宽度有所增加, 维护的需求也会增加。另一方面,相比之下,堆管理起来则很容易。对组织表在某 些方面的效率要比IOT 高。一般认为,比起堆组织表来说,IOT有一些突出的优点。例如,记得曾经有一次,我在一些文本数据 上建立一个反向表索引(那时还 没有引入interMedia和相关的技术)。我有一个表,其中放满了文档, 并发现其中的单词。我的表如下所示:

create table keywords
( word varchar2(50), position int, doc_id int,
primary key(word,position,doc_id)
);
在 此,我的表完全由主键组成。因此有超过100%的(主键索引)开销;表的大小与主键索引的大小 相当(实际上,主键索引更大,因为它物理地存储了所指向的行 的rowid;而表中并不存储rowid,表中 的行ID是推断出来的)。使用这个表时,WHERE子句只选择了WORD列或WORD和POSITION 列。也就是说, 我并没有使用表,而只是使用了表上的索引,表本身完全是开销。我想找出包含某个给定单词的所有文档
(或者满足“接近”每个词等匹配条件)。 此时,堆表是没有用的,它只会在维护KEYWORDS表时让应用 变慢,并使存储空间的需求加倍。这个应用就非常适合采用IOT。
另一个适于使用IOT的实现是代码查找表。例如,可能要从ZIP_CODE查找STATE。此时可以不要堆 表,而只使用IOT本身。如果你只会通过主键来访问一个表,这个表就非常适合实现为IOT。
如 果你想保证数据存储在某个位置上,或者希望数据以某种特定的顺序物理存储,IOT就是一种合 适的结构。如果是Sybase和SQL Server的用户,你可能会使用一个聚簇索引,但是IOT比聚簇索引更好。 这些数据库中的聚簇索引可能有多达110%的开销(与前面的KEYWORDS 表例子类似)。而使用IOT的话, 我们的开销则是0%,因为数据只存储一次。有些情况下,你可能希望数据像这样物理地共同存储在一处,
父/子关系就是这样 一个典型的例子。假设EMP表有一个包含地址的子表。员工最初递交求职信时,你可 能向系统中(地址比表中)输出一个家庭地址。过一段时间后,他搬家了,就 要把家庭地址修改为原地址 , 并增加一个新的家庭地址。然后,他可能还会回去读学位,此时可能还要增加一个学校地址,等等。也就 是说,这个员工有3~4个 (或者更多)的(地址)详细记录,但是这些详细记录是随机到来的。在一个 普通的基于堆的表中,这些记录可以放在任何地方。两个或更多地址记录放在堆表的同 一个数据库块上的 概率接近于0.不过,你查询员工的信息时,总会把所有地址详细记录都取出来。在一段时间内分别到达的 这些行总会被一并获取得到。为了让这 种获取更为高效,可以对子表使用IOT,使得子表将对应某个给定 员工的所有记录都插入到相互“靠近”的地方,这样在反复获取这些记录时,就可以减少工作 量。
使用一个IOT将子表信息物理地存储在同一个位置上有什么作用?这一点通过一个例子就能很容易 地说明。下面创建并填充一个EMP表:

ops$tkyte@ORA10GR1> create table emp
2 as
3 select object_id empno,
4 object_name ename,
5 created hiredate,
6 owner job
7 from all_objects
8 /
Table created.
ops$tkyte@ORA10GR1> alter table emp add constraint emp_pk primary key(empno)
2 /
Table altered. ops$tkyte@ORA10GR1> begin
2 dbms_stats.gather_table_stats( user, 'EMP', cascade=>true );
3 end;
4 /

PL/SQL procedure successfully completed. 接下来,将这个子表实现两次:一次作为传统的堆表,另一次实现为IOT: ops$tkyte@ORA10GR1> create table heap_addresses
2 ( empno references emp(empno) on delete cascade,

3 addr_type varchar2(10),
4 street varchar2(20),
5 city varchar2(20),
6 state varchar2(2),
7 zip number,
8 primary key (empno,addr_type)
9 )
10 /
Table created.
ops$tkyte@ORA10GR1> create table iot_addresses
2 ( empno references emp(empno) on delete cascade,
3 addr_type varchar2(10),
4 street varchar2(20),
5 city varchar2(20),
6 state varchar2(2),
7 zip number,
8 primary key (empno,addr_type)
9 )
10 ORGANIZATION INDEX
11 /
Table created.
我 如下填充这些表,首先为每个员工插入一个工作地址,其次插入一个家庭地址,再次是原地址, 最后是一个学校地址。堆表很可能把数据放在表的“最后”;数据到 来时,堆表只是把它增加到最后,因 为此时只有数据到来,而没有数据被删除。过一段时间后,如果有地址被删除,插入就开始变得更为随机, 会随机地插入到整个 表中的每个位置上。不过,有一点是肯定的,堆表中员工的工作地址与家庭地址同在 一个块上的机率几乎为0.不过,对于IOT,由于键在EMPNO, ADDR_TYPE上,完全可以相信:对应一个给 定EMPNO的所有地址都会放在同一个(或者两个)索引块上。填充这些数据的插入语句如下:

ops$tkyte@ORA10GR1> insert into heap_addresses

2 select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
3 from emp;
48250 rows created.
ops$tkyte@ORA10GR1> insert into iot_addresses
2 select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
3 from emp;
48250 rows created.
我把这个插入又做了3次,依次将WORK分别改为HOME、PREV和SCHOOL。然后收集统计信息:

ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats( user, 'HEAP_ADDRESSES' ); PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR1> exec dbms_stats.gather_table_stats( user, 'IOT_ADDRESSES' ); PL/SQL procedure successfully completed.
现在可以看看我们预料到的显著差别。通过使用AUTOTRACE,可以了解到改变有多大:

ops$tkyte@ORA10GR1> set autotrace traceonly ops$tkyte@ORA10GR1> select *
2 from emp, heap_addresses
3 where emp.empno = heap_addresses.empno
4 and emp.empno = 42;
Execution Plan
----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=4 Bytes=336)

1 0 NESTED LOOPS (Cost=8 Card=4 Bytes=336)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=1...

3 2 INDEX (UNIQUE SCAN) OF 'EMP_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'HEAP_ADDRESSES' (TABLE) (Cost=6...

5 4 INDEX (RANGE SCAN) OF 'SYS_C008078' (INDEX (UNIQUE)) (Cost=2 Card=4)

Statistics
----------------------------------------------------------
...
...
11 consistent gets
4 rows processed

这是一个相对常见的计划:按主键访问EMP表;得到行;然后使用这个EMPNO访问地址表;接下来使 用索引找出子记录。获取这个数据执行了11次I/O。下面再运行同样的查询,不过这一次地址表实现为IOT:

ops$tkyte@ORA10GR1> select *
2 from emp, iot_addresses
3 where emp.empno = iot_addresses.empno
4 and emp.empno = 42;
Execution Plan
----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=4 Bytes=336)

1 0 NESTED LOOPS (Cost=4 Card=4 Bytes=336)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=1...

3 2 INDEX (UNIQUE SCAN) OF 'EMP_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)

4 1 INDEX (RANGE SCAN) OF 'SYS_IOT_TOP_59615' (INDEX (UNIQUE)) (Cost=2...

Statistics
----------------------------------------------------------
...
7 consistent gets
...
4 rows processed
ops$tkyte@ORA10GR1> set autotrace off
这里少做了4次I/O(这个4 应该能推测出来);我们跳过了4个TABLE ACCESS (BY INDEX ROWID) 步骤。子表记录越多,所能跳过的I/O就越多。
那 么,这4个I/O是什么呢?在这个例子中,这是查询所完成I/O的1/3还多,如果反复执行这个 查询,这就会累积起来。每个I/O和每个一致获取需要访问 缓冲区缓存,尽管从缓存区缓存读数据要比从 磁盘读快得多,但是要知道,缓存区缓存获取并不是“免费”的,而且也绝对不是“廉价”的。每个缓冲 区缓存获取都 需要缓冲区缓存的多个闩,而闩是串行化设备,会限制我们的扩展能力。通过运行以下PL/SQL 块,可以测量出I/O和闩定的减少:

ops$tkyte@ORA10GR1> begin
2 for x in ( select empno from emp )
3 loop
4 for y in ( select emp.ename, a.street, a.city, a.state, a.zip
5 from emp, heap_addresses a
6 where emp.empno = a.empno
7 and emp.empno = x.empno )
8 loop
9 null;
10 end loop;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed.
这里只是模拟我们很忙,在此将查询运行大约45,000次,对应各个EMPNO运行一次。如果对 HEAP_ADRESSES和IOT_ADDRESSES表分别运行这个代码,TKPROF会显示如下结果:

SELECT EMP.ENAME, A.STREET, A.CITY, A.STATE, A.ZIP FROM EMP, HEAP_ADDRESSES A
WHERE EMP.EMPNO = A.EMPNO AND EMP.EMPNO = :B1

call count cpu elapsed disk query cur rent rows

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

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

Parse 1 0.00 0.00 0 0

0 0

Execute 48244 7.66 7.42 0 0

0 0

Fetch 48244 6.29 6.56 0 483393

0 192976

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

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

total 96489 13.95 13.98 0 483393

0 192976

Rows Row Source Operation

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

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

192976 NESTED LOOPS (cr=483393 pr=0 pw=0 time=5730335 us)

48244 TABLE ACCESS BY INDEX ROWID EMP (cr=144732 pr=0 pw=0 time=1594981 us)

48244 INDEX UNIQUE SCAN EMP_PK (cr=96488 pr=0 pw=0 time=926147 us)...

192976 TABLE ACCESS BY INDEX ROWID HEAP_ADDRESSES (cr=338661 pr=0 pw=0 time=...

192976 INDEX RANGE SCAN SYS_C008073 (cr=145685 pr=0 pw=0 time=1105135 us)...

******************************************************************************** SELECT EMP.ENAME, A.STREET, A.CITY, A.STATE, A.ZIP
FROM EMP, IOT_ADDRESSES A
WHERE EMP.EMPNO = A.EMPNO AND EMP.EMPNO = :B1

call count cpu elapsed disk query cur rent rows

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

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

Parse 1 0.00 0.00 0 0

0 0

Execute 48244 8.17 8.81 0 0

0 0

Fetch 48244 4.31 4.12 0 292918

0 192976

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

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

total 96489 12.48 12.93 0 292918

0 192976

Rows Row Source Operation

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

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

192976 NESTED LOOPS (cr=292918 pr=0 pw=0 time=3429753 us)

48244 TABLE ACCESS BY INDEX ROWID EMP (cr=144732 pr=0 pw=0 time=1615024 us)

48244 INDEX UNIQUE SCAN EMP_PK (cr=96488 pr=0 pw=0 time=930931 us)...

192976 INDEX RANGE SCAN SYS_IOT_TOP_59607 (cr=148186 pr=0 pw=0 time=1417238 us)...


两 个查询获取的行数同样多,但是HEAP表完成的逻辑I/O显著增加。随着系统并发度的增加,可以 想见,堆表使用的CPU时间也会增长得更快,而查询耗费 CPU时间的原因可能只是在等待缓冲区缓存的闩 。 使用runstats(我自己设计的一个工具),可以测量出两种实现的闩定之差。在我的系统上,观察到的 结 果是:

STAT...consistent gets 484,065 293,566 -190,499
STAT...no work - consistent re 194,546 4,047 -190,499
STAT...consistent gets from ca 484,065 293,566 -190,499
STAT...session logical reads 484,787 294,275 -190,512
STAT...table fetch by rowid 241,260 48,260 -193,000
STAT...buffer is not pinned co 337,770 96,520 -241,250
LATCH.cache buffers chains 732,960 349,380 -383,580
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct

990,344 598,750 -391,594 165.40%
在此Run1是HEAP_ADDRESSES表,Run2是IOT_ADDRESSES表。可以看到,在发生的闩定方面,存在 显著的下降,而且这种下降可以重复验证,这主要是因为缓冲区缓存存在闩的串链(即保护缓冲区缓存的 闩)。在这种情况下,IOT提供了以下好处:
q 提供缓冲区缓存效率,因为给定查询的缓存中需要的块更少。
q 减少缓冲区缓存访问,这会改善可扩缩性。
q 获取数据的工作总量更少,因为获取数据更快。
q 每个查询完成的物理I/O更少,因为对于任何给定的查询,需要的块更少,而且对地址记 录的一个物理I/O很可能可以获取所有地址(而不只是其中一个地址,但堆表实现就只是获取 一个地址)。
如 果经常在一个主键或惟一键上使用BETWEEN查询,也是如此。如果数据有序地物理存储,就能提 升这些查询的性能。例如,我在数据库中维护了一个股价表。 每天我要收集数百支股票的股价记录、日期 、 收盘价、当日最高价、当日最低价、买入卖出量和其他相关信息。这个表如下所示:

ops$tkyte@ORA10GR1> create table stocks
2 ( ticker varchar2(10),
3 day date,
4 value number,
5 change number,
6 high number,
7 low number,
8 vol number,
9 primary key(ticker,day)
10 )
11 organization index
12 /
Table created.
我 经常一次查看一支股票几天内的表现(例如,计算移动平均数)。如果我使用一个堆组织表,那 么对于股票记录ORCL的两行在同一个数据库块上的可能性几乎为 0.这是因为,每天晚上我都会插入当天 所有股票的记录。这至少会填满一个数据库块(实际上,可能会填满多个数据库块)。因此,每天我都会 增加一个新的 ORCL记录,但是它总在另一个块上,与表中已有的其他 ORCL记录不在同一个块上。如果执 行如下查询:

Select * from stocks where ticker = 'ORCL'
and day between sysdate-100 and sysdate;
Oracle会 读取索引,然后按rowid来访问表,得到余下的行数据。由于我加载表所采用的方式,获 取的每100行会在一个不同的数据库块上,所有每获取100行可能 就是一个物理I/O。下面考虑IOT中有 同样的数据。这是这个查询,不过现在只需要读取相关的索引块,这个索引块中已经有所有的数据。在此 不仅不存在表访 问,而且一段时期内对于ORCL的所有行物理存储在相互“邻近”的位置。因此引入的逻 辑I/O和物理I/O都更少。
现在你已经知道了什么时候想使用IOT,以及如何使用IOT。接下来需要了解这些表有哪些选项。有 哪些需要告诫的方面?IOT的选项与堆组织表的选项非常相似。我们还是使用DBMS_METADATA来显示详细 选项。先从IOT的3 个基本变体开始:

ops$tkyte@ORA10GR1> create table t1
2 ( x int primary key,
3 y varchar2(25),
4 z date
5 )
6 organization index; Table created.
ops$tkyte@ORA10GR1> create table t2
2 ( x int primary key,
3 y varchar2(25),
4 z date
5 )
6 organization index
7 OVERFLOW; Table created.
ops$tkyte@ORA10GR1> create table t3
2 ( x int primary key,
3 y varchar2(25),
4 z date
5 )
6 organization index
7 overflow INCLUDING y;

le created.
后面会介绍OVERFLOW和INCLUDING会为我们做什么,不过首先来看第一个所需的详细SQL:

ops$tkyte@ORA10GR1> select dbms_metadata.get_ddl( 'TABLE', 'T1' ) from dual;
S_METADATA.GET_DDL('TABLE','T1')
----------------------------------------------------------------------------- CREATE TABLE "OPS$TKYTE"."T1"
"X" NUMBER(*,0), "Y" VARCHAR2(25), "Z" DATE,
PRIMARY KEY ("X") ENABLE
ANIZATION INDEX OMPRESS
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) LESPACE "USERS"
THRESHOLD 50
这个表引入了两个新的选项:NOCOMPRESS和PCTTHRESHOLD,稍后将介绍它们。你可能已经注意到了,
与前面的CREATE TABLE语 法相比,这里好像少了点什么:没有PCTUSED子句,但是这里有一个PCTFREE。 这是因为,索引是一个复杂的数据结构,它不像堆那样随机组织,所以 数据必须按部就班地存放到它该去 的地方去。在堆中,块只是有时能插入新行,而索引则不同,块总是可以插入新的索引条目。如果每个数 据(根据它的值)属于一 个给定块,在总会放在那个块上,而不论这个块多满或者多空。另外,只是在索 引结构中创建对象和填充数据时才会使用PCTFREE。其用法与堆组织表中的用 法不同。PCTFREE会在新创 建的索引上预留空间,但是对于以后对索引的操作不预留空间,这与不使用PCTUSED的原因是一样的。堆 组织表上关于 freelist的考虑同样完全适用于IOT。
现 在来讨论新发现的选项NOCOMPRESS。这个选项对索引一般都可用。它告诉Oracle把每个值分别 存储在各个索引条目中(也就是不压缩)。如果对象 的主键在A、B和C列上,A、B和C的每一次出现都 会物理地存储。NOCOMPRESS反过来就是COMPRESS N,在此N是一个整数,表示要压缩的列数。这样可以避 免重复值,并在块级提取“公因子”(factor out)。这样在A的值(以及B的值)重复出现时,将不再 物理地存储它们。例如,请考虑如下创建的一个表:

ops$tkyte@ORA10GR1> create table iot
2 ( owner, object_type, object_name,
3 primary key(owner,object_type,object_name)

)
organization index
NOCOMPRESS
as
8 select owner, object_type, object_name from all_objects
/
le created.
可 以想想看,每个模式(作为OWNER)都拥有大量对象,所有OWNER 值可能会重复数百次。甚至OWNER, OBJECT_TYPE值对也会重复多次,因 为给定模式可能有数十个表、数十个包等。只是这3列合在一起不会 重复。可以让Oracle压缩这些重复的值。索引块不是包含表10-1所示的值,而是可以 使用COMPRESS 2
(提取前两列),包含表10-2所示的值。
表10-1索引叶子块,NOCOMPRESS
Sys,table,t1 Sys,table,t2 Sys,table,t3 Sys,table,t4
Sys,table,t5 Sys,table,t6 Sys,table,t7 Sys,table,t8
. . . . . . . . . .
. .
Sys,table,t100 Sys,table,t101 Sys,table,t102 Sys,table,t103
表10-2索引叶子块,COMPRESS 2
Sys,table t1 t2 t3
t4 t5 . . . . . .
. . . t103 t104 . . . t300 t301 t302 t303
也 就是说,值SYS和TABLE只出现一次,然后存储第三列。采用这种方式,每个索引块可以有更多 的条目(否则这是不可能的)。这不会降低并发性,因为我们 仍在行级操作;另外也不会影响功能。它可 能会稍微多占用一些CPU时间,因为Oracle必须做更多的工作将键合并在一起。另一方面,这可能会显著 地减少 I/O,并允许更多的数据在缓冲区缓存中缓存,原因是每个块上能有更多的数据。这笔交易很划得 来。

下面做一个快速的测试,对前面CREATE TABLE 的SELECT分别采用NOCOMPRESS、COMPRESS 1 和 COMPRESS 2选项,来展示能节省多少空间。先来创建IOT,但不进行压缩:
ops$tkyte@ORA10GR1> create table iot
2 ( owner, object_type, object_name,

3 constraint iot_pk primary key(owner,object_type,object_name)
4 )
5 organization index
6 NOCOMPRESS
7 as
8 select distinct owner, object_type, object_name
9 from all_objects
10 /
le created.
现在可以测量所用的空间。为此我们将使用ANALYZE INDEX VALIDATE STRUCTURE命令。这个命令会
填写一个名为INDEX_STATS的动态性能视图,其中最多只包含一行,即这个ANALYZE命令最后一次执行的 信息:
ops$tkyte@ORA10GR1> analyze index iot_pk validate structure;
ex analyzed.
ops$tkyte@ORA10GR1> select lf_blks, br_blks, used_space,
2 opt_cmpr_count, opt_cmpr_pctsave
3 from index_stats;
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- -------------------- -----------------------
----- --------------------------------

284

3

2037248

2

33


由 此显示出,我们的索引目前使用了284个叶子块(即数据所在的块),并使用了3个分支块(Oracle
在索引结构中导航所用的块)来找到这些叶子块。使用 的空间大约是2MB(2,038,248字节)。另外两列 名字有些奇怪,这两列是要告诉我们一些信息。OPT_CMPR_COUNT(最优压缩数)列要说 的是:“如果你把 这个索引置为COMPRESS 2,就会得到最佳的压缩”。OPT_CMPR_PCTSAVE(最优的节省压缩百分比)则是说 , 如果执行COMPRESS 2,就能节省大约1/3的存储空间,索引只会使用现在2/3的磁盘空间。
注意 下一章将更详细地介绍索引结构。 为了测试上述理论,我们先用COMPRESS 1重建这个IOT:

ops$tkyte@ORA10GR1> alter table iot move compress 1;
le altered.
ops$tkyte@ORA10GR1> analyze index iot_pk validate structure;
ex analyzed.
ops$tkyte@ORA10GR1> select lf_blks, br_blks, used_space,
2 opt_cmpr_count, opt_cmpr_pctsave
3 from index_stats;
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

----------

----------

----------

-

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

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

247

1

1772767

2

23

可以看到,索引确实更小了:大约1.7MB,叶子块和分支块都更少。但是,现在它说“你还能再节省
另外23%的空间”,因为我们没有充分地压缩。下面用COMPRESS 2再来重建IOT:

ops$tkyte@ORA10GR1> alter table iot move compress 2;
le altered.
ops$tkyte@ORA10GR1> analyze index iot_pk validate structure;
ex analyzed.
ops$tkyte@ORA10GR1> select lf_blks, br_blks, used_space,
2 opt_cmpr_count, opt_cmpr_pctsave
3 from index_stats;
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

----------

----------

----------

-

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

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


190 1 1359357
2 0 现在大小有了显著减少,不论是叶子块数还是总的使用空间都大幅下降,现在使用的空间大约是
1.3MB。再来看原来的数字:

ops$tkyte@ORA10GR1> select (2/3) * 2037497 from dual;
3)*2037497
----------
8331.33
可以看到OPT_CMPR_PCTSAVE真是精准无比。上一个例子指出,关于IOT有一点很有意思:IOT是表, 但是只是有其名而无其实。IOT段实际上是一个索引段。
现 在我先不讨论PCTTHRESHOLD选项,因为它与IOT的下面两个选项有关:OVERFLOW和INCLUDING。 如果查看以下两组表(T2和 T3)的完整SQL,可以看到如下内容(这里我使用了一个DBMS_METADATA例程 来避免STORAGE子句,因为它们对这个例子没有意义):

$tkyte@ORA10GR1> begin
2 dbms_metadata.set_transform_param
3 ( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
4 end;
ops$tkyte@ORA10GR1> select dbms_metadata.get_ddl( 'TABLE', 'T2' ) from dual;

S_METADATA.GET_DDL('TABLE','T2')

----------------------------------------------------------------------------- CREATE TABLE "OPS$TKYTE"."T2"

"X" NUMBER(*,0), "Y" VARCHAR2(25), "Z" DATE,

PRIMARY KEY ("X") ENABLE

) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING TABLESPACE "USERS"

THRESHOLD 50 OVERFLOW

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "USERS"

ops$tkyte@ORA10GR1> select dbms_metadata.get_ddl( 'TABLE', 'T3' ) from dual;

S_METADATA.GET_DDL('TABLE','T3')

----------------------------------------------------------------------------- CREATE TABLE "OPS$TKYTE"."T3"

"X" NUMBER(*,0), "Y" VARCHAR2(25), "Z" DATE,

PRIMARY KEY ("X") ENABLE

) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING TABLESPACE "USERS"

PCTTHRESHOLD 50 INCLUDING "Y" OVERFLOW

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "USERS"


所 以,现在只剩下PCTTHRESHOLD、OVERFLOW和INCLUDING还没有讨论。这三个选项有点“绕”,其 目标是让索引叶子块(包含具体索引 数据的块)能够高效地存储数据。索引一般在一个列子集上。通常索 引块上的行数比堆表块上的行数会多出几倍。索引指望这每块能得到多行。否则,Oracle 会花费大量的时 间来维护索引,因为每个INSERT或UPDATE都可能导致索引块分解,以容纳新数据。
OVERFLOW子句允许你建立另一个段(这就使得IOT成为一个多段对象,就像有一个CLOB列一样), 如果IOT的行数据变得太大,就可以溢出到这个段中。
注意 构成主键的列不能溢出,它们必须直接放在叶子块上。
注意,使用 MSSM时 ,OVERFLOW再次为IOT引入了PCTUSED子句。对于 OVERFLOW段和堆表来说,PCTFREE 和PCTUSED的含义都相同。使用溢出段的条件可以采用两种方式来指定:
q PCTTHRESHOLD:行中的数据量超过块的这个百分比时,行中余下的列将存储在溢出段中。 所以,如果PCTTHRESHOLD是10%,而块大小是8KB,长度大于800字节的行就会把其中一部分 存储在别处,而不能在索引块上存储。
q INCLUDING:行中从第一列直到INCLUDING子句所指定列(也包括这一列)的所有列都存 储在索引块上,余下的列存储在溢出段中。
假设有以下表,块大小为2KB:

ops$tkyte@ORA10GR1> create table iot
2 ( x int,
3 y date,

4 z varchar2(2000),
5 constraint iot_pk primary key (x)
6 )
7 organization index
8 pctthreshold 10
9 overflow
10 /
le created.
用图来说明,则如图10-6所示。

图10-6 有溢出段的IOT,使用PCTTHRESHOLD子句
灰 框是索引条目,这是一个更大索引结构的一部分(在第11章中,你将看到一个更大的图,其中会 展示索引是什么样子)。简单地说,索引结构是一棵树,叶子块 (存储数据的块)实际上构成一个双向链 表,这样一来,一旦我们发现想从索引中的哪个位置开始,就能更容易地按顺序遍历这些节点。白框表示 一个 OVERFLOW段。超出PCTTHRESHOLD设置的数据就会存储在这里。Oracle会从最后一列开始向前查找, 直到主键的最后一列(但不包括主键 的最后一列),得出哪些列需要存储在溢出段中。在这个例子中,数 字列X 和日期列Y在索引块中总能放下。最后一列Z的长度不定。如果它小于大约190字节(2KB块的10% 是大约200字节;再减去7字节的日期和3~5字节的数字),就会存储在索引块上。如果超过了190字节 , Oracle将把Z的数据存 储在溢出段中,并建立一个指向它的指针(实际上是一个rowid)。
另一种做法是使用INCLUDING子句。在此要明确地说明希望把哪些列存储在索引块上,而哪些列要存 储在溢出段中。给出以下的CREATE TABLE语句:

ops$tkyte@ORA10GR1> create table iot
2 ( x int,
3 y date,
4 z varchar2(2000),
5 constraint iot_pk primary key (x)

6 )
7 organization index
8 including y
9 overflow
10 /
le created.
我们可能看到图10-7所示的情况。

图10-7有OVERFLOW段的IOT,使用INCLUDING子句 在这种情况下,不论Z中存储的数据大小如何,Z都会“另行”存储在溢出段中。
那 么究竟使用PCTTHRESHOLD、INCLUDING还是二者的某种组合呢?这些方法中哪一个更好?这取决 于你的实际需求。如果你的应用总是(或者几 乎总是)使用表的前4列,而很少访问后5列,使用 INCLUDING 会更合适。可以包含至第4列,而让另外5列另行存储。运行时,如果需要这 5列,可以采 用行迁移或串 链的方式获取这些列。Oracle将读取行的“首部”,找到行余下部分的指针,然后读取这些部分。另一方 面,如果无法清楚地指出哪些列总会被 访问而哪些列一般不会被访问,就可以考虑使用PCTTHRESHOLD。 一旦确定了平均每个索引块上可能存储多少行,设置PCTTHRESHOLD就会很 容易。假设你希望每个索引块 上存储20行。那好,这说明每行应该是1/20(5%)。你的PCTTHRESHOLD就是5,而且索引叶子块上的每 个行块都 不能占用对于块中5%的空间。
对于IOT最后要考虑的是建立索引。IOT本身可以有一个索引,就像在索引之上再加索引,这称为二 次索引(secondary index)。 正常情况下,索引包含了所指向的行的物理地址,即rowid。而IOT二次 索引无法做到这一点;它必须使用另外某种方法来指示行的地址。这是因为IOT中 的行可以大量移动,而 且它不像堆组织表中的行那样“迁移”。IOT中的行肯定在索引结构中的每个位置上,这取决于它的主键 值;只有当索引本身的大小和形状 发生改变时行才会移动(下一章将更详细地讨论索引结构如何维护)。 为了适应这种情况,Oracle引入了一个逻辑rowid(logical rowid)。 这些逻辑rowid根据IOT主键建 立。对于行的当前位置还可以包含一个“猜测”,不过这个猜测几乎是错的,因为稍过一段时间后,IOT 中的数据可能就会 移动。这个猜测是行第一次置于二次索引结构中时在IOT中的物理地址。如果IOT中的 行必须移动到另外一个块上,二次索引中的猜测就会变得“过时”。因 此,与常规表相比,IOT上的索引 效率稍低。在一个常规表上,索引访问通常需要完成一个I/O来扫描索引结构,然后需要一个读来读取表 数据。对于IOT, 通常要完成两个扫描;一次扫描二次结构,另一次扫描IOT本身。除此之外,IOT上的 索引可以使用非主键列提供IOT数据的快速、高效访问。
索引组织表小结
在 建立IOT时,最关键的是适当地分配数据,即哪些数据存储在索引块上,哪些数据存储在溢出段 上。对溢出条件不同的各种场景进行基准测试,查看对 INSERT、UPDATE、DELETE和SELECT分别有怎样的 影响。如果结构只建立一次,而且要频繁读取,就应该尽可能地把数据放在索引块上(最 合适获取),要 么频繁地组织索引中的数据(不适于修改)。堆表的freelist相关考虑对IOT也同样适用。PCTFREE和 PCTUSED在IOT中 是两个重要的角色。不过,PCTFREE对于IOT不像对于堆表那么重要,另外PCTUSED 一 般不起作用。不过,考虑OVERFLOW段时, PCTFREE和PCTUSED对于IOT的意义将与对于堆表一样重大; 要采用与堆表相同的逻辑为溢出段设置这两个参数。