堆组织表
应用中99%（或者更多）的情况下使用的可能都是堆组织表，不过随着IOT的出现，这种状况以后可能会有所改观，因为IOT本身就可以加索引。执行 CREATE TABLE语句时，默认得到的表类型就是堆组织表。如果想要任何其他类型的表结构，就需要在CREATE语句本身中指定它。
堆 （heap）是计算机科学领域中得到深入研究的一种经典数据结构。它实际上就是一个很大的空间、磁盘或内存区，会以一种显然随机的方式管理。数据会放在最合适的地方，而不是以某种特定顺序来放置。许多人希望能按数据放入表中的顺序从表中取出数据，但是对于堆，这是无法保证的。
在以下的例子中，将建立一个表，使得在数据库中每块刚好能放一个整行（我使用的块大小是8KB）,利用这一点来展示一种可预测的事务序列。不论数据库使用多大的块大小，也不论表的大小如何，都可以观察到以下行为（行没有次序）：
scott@ORCL>show parameters db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
scott@ORCL>create table t
2 ( a int,
3 b varchar2(4000) default rpad('*',4000,'*'),
4 c varchar2(3000) default rpad('*',3000,'*')
5 )
6 /
表已创建。
scott@ORCL>insert into t (a) values ( 1);
已创建 1 行。
scott@ORCL>insert into t (a) values ( 2);
已创建 1 行。
scott@ORCL>insert into t (a) values ( 3);
已创建 1 行。
scott@ORCL>delete from t where a = 2 ;
已删除 1 行。
scott@ORCL>insert into t (a) values ( 4);
已创建 1 行。
scott@ORCL>select a from t;
A
----------
1
4
3可以根据块大小来调整B和C列。例如，如果你的块大小为2KB，则不需要C列，而且B列应该是一个VARCHAR2 (1500)，默认有1,500个星号。在这样一个表中，由于数据在堆中管理，只要有空间变为可用，就会重用这个空间。
使用ASSM或MSSM时，行最后会在“不同的位置上”。底层的空间管理例程有很大差别，在ASSM和MSSM中，对同一个表执行同样的操作很可能得到不同的物理顺序。尽管数据逻辑是相同的，但是它们会以不同的方式存储。
全部扫描时，会按命中的顺序来获取数据，而不是以插入的顺序。一般来讲，数据库表本质上是无序的数据集合。还应该注意到，要观察到这种效果，不必在INSERT后接下来再使用DELETE；只需使用INSERT就可以得到同样的结果。如果我插入一个小行，那么观察到的 结果很可能是：取出行时默认的顺序为“小行、小行、大行”。这些行并不按插入的顺序获取。Oracle会把数据放在能放下的任何地方，而不是按照日期或事 务的某种顺序来存放。
如果你的查询需要按插入的顺序来获取数据，就必须向表中增加一列，以便获取数据时使用这个列对数据排序。
应该把堆组织表看作一个很大的无序行集合。这些行会以一种看来随机的顺序取出，而且取出的顺序还取决于所用的其他选项（并行查询、不同的优化器模式，等待），同一个查询可能会以不同的顺序取出数据。不要过分依赖查询得到的顺序，除非查询中有一个ORDER BY语句！
要了解一个给定表的CREATE TABLE语句中主要有哪些可用的选项，我用了一个技巧。首先，尽可能简单地创建表，例如：
scott@ORCL>create table t
2 ( x int primary key,
3 y date,
4 z clob
5 )
6 /
表已创建。然后，使用标准内置包DBMS_METADATA，查询这个表的定义，并查看详细语法：
scott@ORCL>select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T')
------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "X" NUMBER(*,0),
"Y" DATE,
"Z" CLOB,
PRIMARY KEY ("X")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "TOOLS" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "TOOLS"
LOB ("Z") STORE AS BASICFILE (
TABLESPACE "TOOLS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING )
它显示了CREATE TABLE语句的许多选项。只需要提供数据类型，Oracle就会为我生成详细的“版本”（CREATE TABLE版本）。现在我可以定制这个详细的版本，可能把ENABLE STORAGE IN ROW改 成DISABLE STORAGE IN ROW，这样会禁用随结构化数据在行中存储LOB数据，而把LOB数据存储在另外一个段中。
那么对于堆表来说，需要注意 重要选项有4个：
FREELISTS： 仅适用于MSSM。每个表都会在一个freelist上管理堆中分配的块。一个表可以有多个freelist。如果你认定会有多个并发用户对表执行大量的 插入，配置多个freelist可能会大大地改善性能（可能要以额外的存储空间为代价）。
PCTFREE：ASSM和MSSM都适用。在INSERT过程中，会测量块的充满程度。根据块当前充满的程度，这个参数用于控制能否将一行增加到一个块上。这个选项还可以控制因后续更新所导致的行迁移，要根据将如何使用表来适当地设置。
PCTUSED：仅适用于MSSM。度量一个块必须为多空才允许再次插入行。如果块中已用的空间小于PCTUSED，就可以插入新行了。
INITRANS：ASSM 和MSSM都适合。为块初始分配的事务槽数。如果这个选项设置得太低（默认值为2，这也是最小值），可能导致多个用户访问的一个块上出现并发问题。如果一 个数据块机会已满，而且事务表无法动态扩展，会话就会排队等待这个块，因为每个并发事务都需要一个事务槽。如果会对同样的块完成多个并发更新，就应该考虑增大这个值。
单独存储在LOB段中的LOB数据并不使用表的PCTFREE/PCTUSED参数设置。这些LOB块以不同的方式管理：它们总是会填入，直至达到最大容量，而且仅当完全为空时才返回freelist。
索引组织表
索引组织表（index organized table，IOT） 就是存储在一个索引结构中的表。存储在堆中的表是无组织的（也就是说，只要有可用的空间，数据可以放在任何地方），IOT中的数据则按主键存储和排序。对应用来说，IOT表现得与一个“常规”表并无二致；还是要使用SQL正常地访问这些表。IOT对信息获取、空间应用和OLAP应用特别有用。
由于一般认为关系数据库中的所有表都有一个主键，使用堆组织表时， 我们必须为表和表主键上的索引分别留出空间。而IOT则不存在主键的空间开销，因为索引就是数据，数据就是索引，两者已经合二为一。事实上，索引是一个复杂的数据结构，需要大量的工作来管理和维护，而且随着存储的行宽度有所增加，维护的需求也会增加。另一方面，相比之下，堆管理起来则很容易。对组织表在某些方面的效率要比IOT高。一般认为，比起堆组织表来说，IOT有一些突出的优点。
我的表如下所示：
scott@ORCL>create table keywords
2 ( word varchar2(50),
3 position int,
4 doc_id int,
5 primary key(word,position,doc_id)
6 );
表已创建。在此，我的表完全由主键组成。因此有超过100%的（主键索引）开销；表的大小与主键索引的大小相当（实际上，主键索引更大，因为它物理地存储了所指向的行 的rowid；而表中并不存储rowid，表中的行ID是推断出来的）。使用这个表时，WHERE子句只选择了WORD列或WORD和POSITION 列。也就是说，并没有使用表，而只是使用了表上的索引，表本身完全是开销。我想找出包含某个给定单词的所有文档（或者满足“接近”每个词等匹配条件）。 此时，堆表是没有用的，它只会在维护KEYWORDS表时让应用变慢，并使存储空间的需求加倍。这个应用就非常适合采用IOT。
另一个适于使用IOT的实现是代码查找表。例如，可能要从ZIP_CODE查找STATE。此时可以不要堆表，而只使用IOT本身。如果你只会通过主键来访问一个表，这个表就非常适合实现为IOT。
如果你想保证数据存储在某个位置上，或者希望数据以某种特定的顺序物理存储，IOT就是一种合适的结构。而使用IOT的话，我们的开销则是0%，因为数据只存储一次。有些情况下，你可能希望数据像这样物理地共同存储在一处，父/子关系就是这样 一个典型的例子。假设EMP表有一个包含地址的子表。每个员工有3～4个 （或者更多）的（地址）详细记录，但是这些详细记录是随机到来的。在一个普通的基于堆的表中，这些记录可以放在任何地方。两个或更多地址记录放在堆表的同一个数据库块上的概率接近于0.不过，你查询员工的信息时，总会把所有地址详细记录都取出来。在一段时间内分别到达的这些行总会被一并获取得到。为了让这 种获取更为高效，可以对子表使用IOT，使得子表将对应某个给定员工的所有记录都插入到相互“靠近”的地方，这样在反复获取这些记录时，就可以减少工作量。
下面创建并填充一个EMP表：
scott@ORCL>create table emp_0605
2 as
3 select object_id empno,
4 object_name ename,
5 created hiredate,
6 owner job
7 from all_objects
8 /
表已创建。
scott@ORCL>alter table emp_0605 add constraint emp_pk primary key(empno)
2 /
表已更改。
scott@ORCL>begin
2 dbms_stats.gather_table_stats( user, 'emp_0605', cascade=>true );
3 end;
4 /
PL/SQL 过程已成功完成。接下来，将这个子表实现两次：一次作为传统的堆表，另一次实现为IOT：
scott@ORCL>create table heap_addresses
2 ( empno references emp_0605(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 /
表已创建。
scott@ORCL>create table iot_addresses
2 ( empno references emp_0605(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 /
表已创建。如下填充这些表，首先为每个员工插入一个工作地址，其次插入一个家庭地址，再次是原地址，最后是一个学校地址。堆表很可能把数据放在表的“最后”；数据到来时，堆表只是把它增加到最后，因为此时只有数据到来，而没有数据被删除。过一段时间后，如果有地址被删除，插入就开始变得更为随机，会随机地插入到整个表中的每个位置上。堆表中员工的工作地址与家庭地址同在一个块上的机率几乎为0.不过，对于IOT，由于键在EMPNO， ADDR_TYPE上，对应一个给定EMPNO的所有地址都会放在同一个（或者两个）索引块上。填充这些数据的插入语句如下：
scott@ORCL>insert into heap_addresses
2 select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
3 from emp_0605;
已创建71966行。
scott@ORCL>insert into iot_addresses
2 select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
3 from emp_0605;
已创建71966行。把这个插入又做了3次，依次将WORK分别改为HOME、PREV和SCHOOL。然后收集统计信息：
scott@ORCL>exec dbms_stats.gather_table_stats( user, 'HEAP_ADDRESSES' );
PL/SQL 过程已成功完成。
scott@ORCL>exec dbms_stats.gather_table_stats( user, 'IOT_ADDRESSES' );
PL/SQL 过程已成功完成。现在可以看看我们预料到的显著差别。通过使用AUTOTRACE，可以了解到改变有多大：
scott@ORCL>set autotrace traceonly
scott@ORCL>select *
2 from emp_0605, heap_addresses
3 where emp_0605.empno = heap_addresses.empno
4 and emp_0605.empno = 42;
执行计划
----------------------------------------------------------
Plan hash value: 2297000100
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 4 | 356 | 8
(0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 356 | 8
(0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP_0605 | 1 | 43 | 2
(0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1
(0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| HEAP_ADDRESSES | 4 | 184 | 6
(0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SYS_C0016709 | 4 | | 2
(0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP_0605"."EMPNO"=42)
5 - access("HEAP_ADDRESSES"."EMPNO"=42)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
11 consistent gets
5 physical reads
0 redo size
1346 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed这是一个相对常见的计划：按主键访问EMP表；得到行；然后使用这个EMPNO访问地址表；接下来使用索引找出子记录。获取这个数据执行了11次I/O。下面再运行同样的查询，不过这一次地址表实现为IOT：
scott@ORCL>select *
2 from emp_0605, iot_addresses
3 where emp_0605.empno = iot_addresses.empno
4 and emp_0605.empno = 42;
执行计划
----------------------------------------------------------
Plan hash value: 297296770
--------------------------------------------------------------------------------
------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
------------------
| 0 | SELECT STATEMENT | | 4 | 356 | 4
(0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 356 | 4
(0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP_0605 | 1 | 43 | 2
(0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1
(0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | SYS_IOT_TOP_79842 | 4 | 184 | 2
(0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP_0605"."EMPNO"=42)
4 - access("IOT_ADDRESSES"."EMPNO"=42)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1346 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed这里少做了4次I/O；我们跳过了4个TABLE ACCESS (BY INDEX ROWID)步骤。子表记录越多，所能跳过的I/O就越多。
如果反复执行这个查询，I/O 就会累积起来。每个I/O和每个一致获取需要访问 缓冲区缓存，尽管从缓存区缓存读数据要比从磁盘读快得多，每个缓冲区缓存获取都需要缓冲区缓存的多个闩，而闩是串行化设备，会限制我们的扩展能力。通过运行以下PL/SQL块，可以测量出I/O和闩定的减少：
scott@ORCL>begin
2 for x in ( select empno from emp_0605 )
3 loop
4 for y in ( select emp_0605.ename, a.street, a.city, a.state, a.zip
5 from emp_0605, heap_addresses a
6 where emp_0605.empno = a.empno
7 and emp_0605.empno = x.empno )
8 loop
9 null;
10 end loop;
11 end loop;
12 end;
13 /
PL/SQL 过程已成功完成。这里只是模拟我们很忙，在此将查询运行大约45,000次，对应各个EMPNO运行一次。如果对HEAP_ADRESSES和IOT_ADDRESSES表分别运行这个代码，两个查询获取的行数同样多，但是HEAP表完成的逻辑I/O显著增加。随着系统并发度的增加，可以想见，堆表使用的CPU时间也会增长得更快，而查询耗费 CPU时间的原因可能只是在等待缓冲区缓存的闩。
在这种情况下，IOT提供了以下好处：
q 提供缓冲区缓存效率，因为给定查询的缓存中需要的块更少。
q 减少缓冲区缓存访问，这会改善可扩缩性。
q 获取数据的工作总量更少，因为获取数据更快。
q 每个查询完成的物理I/O更少，因为对于任何给定的查询，需要的块更少，而且对地址记录的一个物理I/O很可能可以获取所有地址（而不只是其中一个地址，但堆表实现就只是获取一个地址）。
如果经常在一个主键或惟一键上使用BETWEEN查询，也是如此。如果数据有序地物理存储，就能提升这些查询的性能。例如，我在数据库中维护了一个股价表。 每天我要收集数百支股票的股价记录、日期、收盘价、当日最高价、当日最低价、买入卖出量和其他相关信息。这个表如下所示：
scott@ORCL>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 /
表已创建。经常一次查看一支股票几天内的表现（例如，计算移动平均数）。如果使用一个堆组织表，那么对于股票记录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的选项与堆组织表的选项非常相似。
scott@ORCL>create table t1
2 ( x int primary key,
3 y varchar2(25),
4 z date
5 )
6 organization index;
表已创建。
scott@ORCL>create table t2
2 ( x int primary key,
3 y varchar2(25),
4 z date
5 )
6 organization index
7 OVERFLOW;
表已创建。
scott@ORCL>create table t3
2 ( x int primary key,
3 y varchar2(25),
4 z date
5 )
6 organization index
7 overflow INCLUDING y;
表已创建。首先来看第一个所需的详细SQL：
scott@ORCL>select dbms_metadata.get_ddl( 'TABLE', 'T1' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T1')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T1"
( "X" NUMBER(*,0),
"Y" VARCHAR2(25),
"Z" DATE,
PRIMARY KEY ("X") ENABLE
) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_C
ACHE DEFAULT)
TABLESPACE "TOOLS"
PCTTHRESHOLD 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的值）重复出现时，将不再物理地存储它们。例如，请考虑如下创建的一个表：
create table iot
( owner, object_type, object_name,
primary key(owner,object_type,object_name)
)
organization index
NOCOMPRESS
as
select owner, object_type, object_name from all_objects
/可以想想看，每个模式（作为OWNER）都拥有大量对象，所有OWNER值可能会重复数百次。甚至OWNER，OBJECT_TYPE值对也会重复多次，因为给定模式可能有数十个表、数十个包等。只是这3列合在一起不会重复。可以让Oracle压缩这些重复的值。
每个索引块可以有更多的条目（否则这是不可能的）。这不会降低并发性，因为我们 仍在行级操作；另外也不会影响功能。它可能会稍微多占用一些CPU时间，因为Oracle必须做更多的工作将键合并在一起。另一方面，这可能会显著地减少 I/O，并允许更多的数据在缓冲区缓存中缓存，原因是每个块上能有更多的数据。
下面做一个快速的测试，对前面CREATE TABLE 的SELECT分别采用NOCOMPRESS、COMPRESS 1和COMPRESS 2选项，来展示能节省多少空间。先来创建IOT，但不进行压缩：
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 VALIDATE STRUCTURE命令。这个命令会填写一个名为INDEX_STATS的动态性能视图，其中最多只包含一行，即这个ANALYZE命令最后一次执行的信息：
scott@ORCL>analyze index iot_pk validate structure;
索引已分析
scott@ORCL>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
---------- ---------- ---------- -------------- ----------------
429 3 3081946 2 33
由 此显示出，我们的索引目前使用了429个叶子块（即数据所在的块），并使用了3个分支块（Oracle在索引结构中导航所用的块）来找到这些叶子块。使用 的空间大约是3MB（3,081,946字节）。OPT_CMPR_COUNT（最优压缩数）列要说 的是：“如果你把这个索引置为COMPRESS 2，就会得到最佳的压缩”。OPT_CMPR_PCTSAVE（最优的节省压缩百分比）则是说，如果执行COMPRESS 2，就能节省大约1/3的存储空间，索引只会使用现在2/3的磁盘空间。
我们先用COMPRESS 1重建这个IOT：
scott@ORCL>alter table iot move compress 1;
表已更改。
scott@ORCL>analyze index iot_pk validate structure;
索引已分析
scott@ORCL>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
---------- ---------- ---------- -------------- ----------------
371 3 2667609 2 22可以看到，索引确实更小了：大约2.6MB，叶子块更少。但是，现在它说“你还能再节省另外22%的空间”，因为我们没有充分地压缩。下面用COMPRESS 2再来重建IOT：
scott@ORCL>alter table iot move compress 2;
表已更改。
scott@ORCL>analyze index iot_pk validate structure;
索引已分析
scott@ORCL>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
---------- ---------- ---------- -------------- ----------------
286 3 2053195 2 0现在大小有了显著减少，不论是叶子块数还是总的使用空间都大幅下降，现在使用的空间大约是2MB。再来看原来的数字：
scott@ORCL>select (2/3) *3081946 from dual;
(2/3)*3081946
-------------
2054630.67可以看到OPT_CMPR_PCTSAVE真是精准无比。IOT是表，但是只是有其名而无其实。IOT段实际上是一个索引段。
现在我先不讨论PCTTHRESHOLD选项，因为它与IOT的下面两个选项有关：OVERFLOW和INCLUDING。如果查看以下两组表（T2和 T3）的完整SQL，可以看到如下内容（这里我使用了一个DBMS_METADATA例程来避免STORAGE子句，因为它们对这个例子没有意义）：
scott@ORCL>begin
2 dbms_metadata.set_transform_param
3 ( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
4 end;
5 /
PL/SQL 过程已成功完成。
scott@ORCL>select dbms_metadata.get_ddl( 'TABLE', 'T2' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T2')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T2"
( "X" NUMBER(*,0),
"Y" VARCHAR2(25),
"Z" DATE,
PRIMARY KEY ("X") ENABLE
) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
TABLESPACE "TOOLS"
PCTTHRESHOLD 50 OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "TOOLS"
scott@ORCL>select dbms_metadata.get_ddl( 'TABLE', 'T3' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T3')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T3"
( "X" NUMBER(*,0),
"Y" VARCHAR2(25),
"Z" DATE,
PRIMARY KEY ("X") ENABLE
) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
TABLESPACE "TOOLS"
PCTTHRESHOLD 50 INCLUDING "Y" OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "TOOLS"
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：
scott@ORCL>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 /
表已创建。简单地说，索引结构是一棵树，叶子块 （存储数据的块）实际上构成一个双向链表，这样一来，一旦我们发现想从索引中的哪个位置开始，就能更容易地按顺序遍历这些节点。超出PCTTHRESHOLD设置的数据就会存储在这里。Oracle会从最后一列开始向前查找，直到主键的最后一列（但不包括主键 的最后一列），得出哪些列需要存储在溢出段中。在这个例子中，数字列X和日期列Y在索引块中总能放下。最后一列Z的长度不定。如果它小于大约190字节 （2KB块的10%是大约200字节；再减去7字节的日期和3～5字节的数字），就会存储在索引块上。如果超过了190字节，Oracle将把Z的数据存 储在溢出段中，并建立一个指向它的指针（实际上是一个rowid）。
另一种做法是使用INCLUDING子句。在此要明确地说明希望把哪些列存储在索引块上，而哪些列要存储在溢出段中。给出以下的CREATE TABLE语句：
scott@ORCL>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 /
表已创建。在这种情况下，不论Z中存储的数据大小如何，Z都会“另行”存储在溢出段中。
如果你的应用总是（或者几 乎总是）使用表的前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的意义将与对于堆表一样重大；要采用与堆表相同的逻辑为溢出段设置这两个参数。
扫一扫
1318
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
