Oracle 9i & 10g编程艺术-深入数据库体系结构——第10章：数据库表

第10章    数据库表

在这一章中，我们将讨论各种类型的数据库表，并介绍什么情况下想用哪种类型的数据库表（也就是说，在哪些情况下某种类型的表比其他类型更适用）。我们会强调表的物理存储特征：即数据如何组织和存储。

从前只有一种类型的表，这千真万确，原先确实只有一种“普通”表。管理这种表就像管理“一个堆”一样（下一节会给出有关的定义）。后来，Oracle又增加了几类更复杂的表。如今，除了堆组织表外，还有聚簇表（共有3种类型的聚簇表）、索引组织表、嵌套表、临时表和对象表。每种类型的表都有不同的特征，因此分别适用于不同的应用领域。

10.1   表类型

在深入讨论细节之前，我们先对各种类型的表给出定义。Oracle中主要有9种表类型：

q         堆组织表（heap organized table）：这些就是“普通”的标准数据库表。数据以堆的方式管理。增加数据时，会使用段中找到的第一个能放下此数据的自由空间。从表中删除数据时，则允许以后的INSERT和UPDATE重用这部分空间。这就是这种表类型中的“堆”这个名词的由来。堆（heap）是一组空间，以一种有些随机的方式使用。

q         索引组织表（index organized table）：这些表按索引结构存储。这就强制要求行本身有某种物理顺序。在堆中，只要放得下，数据可以放在任何位置；而索引组织表（IOT）有所不同，在IOT中，数据要根据主键有序地存储。

q         索引聚簇表（index clustered table）：聚簇（cluster）是指一个或多个表组成的组，这些表物理地存储在相同的数据库块上，有相同聚簇键值的所有行会相邻地物理存储。这种结构可以实现两个目标。首先，多个表可以物理地存储在一起。一般而言，你可能认为一个表的数据就在一个数据库块上，但是对于聚簇表，可能把多个表的数据存储在同一个块上。其次，包含相同聚簇键值（如DEPTNO=10）的所有数据会物理地存储在一起。这些数据按聚簇键值“聚簇”在一起。聚簇键使用B*树索引建立。

q         散列聚簇表（hash clustered table）：这些表类似于聚簇表，但是不使用B*树索引聚簇键来定位数据，散列聚簇将键散列到聚簇上，从而找到数据应该在哪个数据库块上。在散列聚簇中，数据就是索引（这是隐喻的说法）。如果需要频繁地通过键的相等性比较来读取数据，散列聚簇表就很适用。

q         有序散列聚簇表（sorted hash clustered table）：这种表类型是Oracle 10g中新增的，它结合了散列聚簇表的某些方面，同时兼有IOT的一些方面。其概念如下：你的行按某个键值（如CUSTOMER_ID）散列，而与该键相关的一系列记录以某种有序顺序到达（因此这些记录是基于时间戳的记录），并按这种有序顺序处理。例如，客户在你的订单输入系统中下订单，这些订单会按先进先出（first in, first out, FIFO）的方式获取和处理。在这样一个系统中，有序散列聚簇就是适用的数据结构。

q         嵌套表（nested table）：嵌套表是Oracle对象关系扩展的一部分。它们实际上就是系统生成和维护的父/子关系中的子表。嵌套表的工作类似于SCOTT模式中的EMP和DEPT。可以认为EMP是DEPT表的子表，因为EMP表有一个指向DEPT的外键DEPTNO。嵌套表与子表的主要区别是：嵌套表不像子表（如EMP）那样是“独立”表。

q         临时表（temporary table）：这些表存储的是事务期间或会话期间的“草稿”数据。临时表要根据需要从当前用户的临时表空间分配临时区段。每个会话只能看到这个会话分配的区段；它从不会看到其他任何会话中创建的任何数据。

q         对象表（object table）：对象表基于某种对象类型创建。它们拥有非对象表所没有的特殊属性，如系统会为对象表的每一行生成REF（对象标识符）。对象表实际上是堆组织表、索引组织表和临时表的特例，还可以包含嵌套表作为其结构的一部分。

q         外部表（external table）：这些表并不存储在数据库本身中，而是放在数据库之外，即放在平常的操作系统文件中。在Oracle9i及以上版本中，利用外部表可以查询数据库之外的一个文件，就好像这个文件也是数据库中平常的表一样。外部表对于向数据库加载数据最有用（外部表是非常强大的数据加载工具）。Oracle 10g则更进一步，还引入了一个外部表卸载功能，在不使用数据库链接的情况下，这为在Oracle数据库之间移动数据提供了一种简单的方法。我们将在第15章更详细地讨论外部表。

q         不论哪种类型的表，都有以下一般信息：

q         一个表最多可以有1000列，不过我不鼓励设计中真的包含这么多列，除非存在某个硬性需求。表中的列数远远少于1000列时才最有效。Oracle在内部会把列数大于254的行存储在多个单独的行段（row piece）中，这些行段相互指向，而且必须重新组装为完整的行影像。

q         表的行数几乎是无限的，不过你可能会遇到另外某个限制，使得这种“无限”并不实际。例如，一般来讲，一个表空间最多有1022个文件（不过，Oracle 10g中有一些新的BIGFILE表空间，这些表空间可以超出上述文件大小限制）。假设你有一些32GB的文件，也就是说，每个表空间有32,704GB，就会有2,143,289,344个块，每个块大小为16KB。你可能在每个块上放160行（每行大约80～100字节）。这样就会有342,926,295,040行。不过，如果对这个表分区，这个行数还能很容易地翻倍。例如，假设一个表有1024个散列分区，则能有1024×342,926,295,040行。确实存在着上限，但是在接近这些上限之前，你肯定会遇到另外某个实际限制。

q         表中的列有多少种排列（以及这些列的函数有多少种排列），表就可以有多少个索引。随着基于函数的索引的出现，理论上讲，说能创建的索引数是无限的！不过，同样由于存在一些实际的限制，这会影响真正能创建和维护的索引数。

q         即使在一个数据库中也可以有无限多个表。不过，还是同样的道理，实际的限制会使数据库中的表数在一个合理的范围内。不可能有数百万个表（这么多表对于创建和管理来说都是不实际的），但是有数千个表还是允许的。

在下一节中，我们将讨论与表相关的一些参数和术语。在此之后，我们再转而讨论基本的堆组织表，然后介绍其他类型的表。

10.2   术语

在这一节中，我们将介绍与表相关的各种存储参数和术语。并非每种表类型都会用到所有参数。例如，PCTUSED参数在IOT环境中就没有意义。具体讨论各种表类型时还会分别介绍与之相关的参数。这一节的目标时介绍这些术语，并给出定义。在后面几节中，还会在适当的时候介绍使用特定参数的更多信息。

10.2.1             段

Oracle 中的段（segment）是占用磁盘上存储空间的一个对象。尽管有多种类型，不过最常见的段类型如下：

q         聚簇（cluster）：这种段类型能存储表。有两种类型的聚簇：B*树聚簇和散列聚簇。聚簇通常用于存储多个表上的相关数据，将其“预联结”存储到同一个数据库块上；还可以用于存储一个表的相关信息。“聚簇”这个词是指这个段能把相关的信息物理的聚在一起。

q         表（table）：表段保存一个数据库表的数据，这可能是最常用的段类型，通常与索引段联合使用。

q         表分区（table partition）或子分区（subpartition）：这种段类型用于分区，与表段很相似。分区表由一个或多个分区段（table partition segment）组成，组合分区表则由一个或多个表子分区段（table subpartition segment）组成。

q         索引（index）：这种段类型可以保存索引结构。

q         索引分区（index partition）：类似与表分区，这种段类型包含一个索引的某个片。分区索引由一个或多个索引分区段（index partition segment）组成。

q         Lob分区（lob partition）、lob子分区（lob subpartition）、lob索引（lobindex）和lob段（lobsegment）：lobindex和lobsegment段保存大对象（large object或LOB）的结构。对包含LOB的表分区时，lobsegment也会分区，lob分区段（lob partition segment）正是用于此。有意思的是，并没有一种lobindex分区段（lobindex partition segment）类型——不论出于什么原因，Oracle将分区lobindex标记为一个索引分区（有人很奇怪为什么要另外给lobindex取一个特殊的名字！）。

q         嵌套表（nested table）：这是为嵌套表指定的段类型，它是主/明细关系中一种特殊类型的“子”表，这种关系随后将详细讨论。

q         回滚段（rollback）和Type2 undo段：undo数据就存储在这里。回滚段是DBA手动创建的段。Type2 undo段由Oracle自动创建和管理。

举例来说，一个表可以是一个段。索引有可能是一个段。这里我强调了“可能”，这是因为，我们可以把一个索引划分到不同的段中。所以，索引对象本身只是一个定义，而不是一个物理段，索引可能由多个索引分区组成，而每个索引分区（index partition）是一个段。表可能是一个段，也可能不是。由于同样的原因，由于表分区，一个表可以有多个表段：或者可以在一个称为聚簇的段中创建一个表，此时这个表可能与其他表同在一个聚簇段中。

不过，最常见的情况是，表是一个段，索引也是一个段。对现在来说，这样考虑最简单。创建一个表时，通常就是创建一个新的表段，而且如第3章所述，这个段包含区段，区段则包含块。这是平常的存储层次结构。但是要指出重要的一点，只在“通常”情况下才有这种一对一的关系。例如，考虑以下这个简单的CREATE TABLE语句：

Create table t ( x int primary key, y clob, z blob );

这个语句创建6个段。如果在一个初始为空（什么也没有）的模式中发出下面的CREATE TABLE语句，会观察到以下结果：

ops$tkyte@ORA10G> select segment_name, segment_type          2 from user_segments; no rows selected   ops$tkyte@ORA10G> create table t ( x int primary key, y clob, z blob ); Table created.   ops$tkyte@ORA10G> select segment_name, segment_type          2 from user_segments; SEGMENT_NAME                           SEGMENT_TYPE ------------------------------                     ------------------ SYS_IL0000063631C00002$$    LOBINDEX SYS_LOB0000063631C00003$$  LOBSEGMENT SYS_C009783                                 INDEX SYS_IL0000063631C00003$$    LOBINDEX SYS_LOB0000063631C00002$$  LOBSEGMENT T                                                          TABLE 6 rows selected.

在这个例子中，表本身创建了一个段：如输出中最后一行所示。这里主键约束创建了一个索引段，以保证惟一性。

注意      惟一约束或主键可能创建一个新索引，也可能不创建。如果约束列上已经有一个索引，而且这些列处于索引的前几列，这个约束就会（而且将会）使用这些列（而不再创建创建新索引）。

另外，每个LOB列分别创建了两个段：一个段用于存储字符大对象（character large object, CLOB）或二进制大对象（binary large object, BLOB）所指的实际数据块，另一个段用于“组织”这些数据块。LOB为非常大块的信息提供了支持，可以多达几GB。LOB存储在lobsegment的块中，lobindex用于跟踪这些LOB块在哪里，以及应该以何种顺序来访问它们。

10.2.2             段空间管理

从Oracle 9i开始，管理段空间有两种方法：

q         手动段空间管理（Manual Segment Space Management）：由你设置FREELISTS、FREELIST GROUPS、PCTUSED和其他参数来控制如何分配、使用和重用段中的空间。在这一章中我会把这种空间管理方法称为MSSM，但是要记住，这是一个我自造的缩写，Oracle文档中没有这个缩写。

q         自动段空间管理（Automatic Segment Space Management, ASSM）：你只需控制与空间使用相关的一个参数：PCTFREE。创建段时也可以接受其他参数，但是这些参数将被忽略。

MSSM是Oracle的遗留实现。它已经存在多年，许多版本都支持MSSM。ASSM则在Oracle 9i Release 1中才首次引入。原先用于控制空间分配和提供高并发性的参数数不胜数，并且需要对这些参数进行调整，人们不希望还要这么做，这正是设计ASSM的出发点。例如，倘若将FREELISTS参数设置为默认值1，可能会出现，如果你的段是插入/更新新密集的（有大量插入/更新操作），对自由空间的分配就会存在竞争。Oracle要在表中插入一行，或更新一个索引键条目，或者由于更新一行而导致这一行迁移时（稍后还会更多地介绍这方面的内容），可能需要从与这个段相关的自由块列表中得到一个块。如果只有一个自由块列表，一次就只有一个事务能查看和修改这个列表，事务之间必须相互等待。在这种情况下，如果有多个FREELISTS和FREELIST GROUPS，就能提高并发性，因为事务可以分别查看不同的列表，而不会相互竞争。

稍后讨论存储设置时，我还会提到哪些参数用于手工段空间管理，而哪些参数用于自动段空间管理，不过需要指出，在存储/段特征这方面，应用于ASSM段的存储设置只有：

q         BUFFER_POOL

q         PCTFREE

q         INITRANS

q         MAXTRANS（仅用于9i；在10g中，所有段都会忽略这个参数）

其他存储和物理属性参数都不适用于ASSM段。

段空间管理是从段的表空间（而且段从不会跨表空间）继承来的一个属性。段要使用ASSM，就必须位于支持ASSM空间管理的表空间中。

10.2.3             高水位线

存储在数据库中的表段使用了这个术语。例如，如果把表想象成一个“平面”结构，或者想象成从左到右依次排开的一系列块，高水平线（high-water mark，HWM）就是包含了数据的最右边的块，如图10－1所示。

图10-1     HWM 示意图

图10－1显示了HWM首先位于新创建表的第一个块中。过一段时间后，随着在这个表中放入数据，而且使用了越来越多的块，HWM会升高。如果我们删除了表中的一些（甚至全部）行，可能就会有许多块不再包含数据，但是它们仍在HWM之下，而且这些块会一直保持在HWM之下，直到重建、截除或收缩这个对象（将段收缩是Oracle 10g的一个新特性，只有当段在一个ASSM表空间中时才支持这个特性）。

HWM很重要，因为Oracle在全面扫描段时会扫描HWM之下的所有块，即使其中不包含任何数据。这会影响全面扫描的性能，特别是当HWM之下的大多数块都为空时。要查看这种情况，只需创建一个有1,000,000行的表（或者创建其他有大量行的表），然后对这个表执行一个SELECT COUNT(*)。下面再删除（DELETE）这个表中的每一行，你会发现尽管SELECT COUNT(*)统计出0行，但是它与统计出1,000,000所花的时间一样长（如果需要完成块清除，时间可能还会更长：有关内容请参加9.5.5节）。这是因为Oracle在忙于读取HWM之下的所有块，查看其中是否包含数据。如果对这个表使用TRUNCATE而不是删除其中的每一行，你可以比较一下结果有什么不同。TRUNCATE会把表的HWM重置回“0”，还会截除表上的相关索引。由于以上原因，如果你打算删除表中的所有行，就应该选择使用TRUNCATE（如果可以使用的话）。

在一个MSSM表空间中，段只有一个HWM。不过，在ASSM表空间中，除了一个HWM外，还有一个低HWM（见图10－2）。在MSSM中，HWM推进时（例如，插入行时），所有块都会并立即有效，Oracle可以安全地读取这些块。不过，对于ASSM，HWM推进时，Oracle并不会立即格式化所有块，只有在第一次使用这些块时才会完成格式化，以便安全地读取。所以，全面扫描一个段时，必须知道要读取的块是否“安全”或是否格式化，（这说明，其中不包含有意义的信息，不能对其进行处理）。为了避免表中每一个块都必须经过这种安全/不安全检查，Oracle同时维护了一个低HWM和一个HWM。Oracle会全表扫描至HWM，对于低HWM以下的所有块会直接读取并加以处理。而对介于低HWM和HWM之间的块，则必须更加小心，需要参考管理这些块所用的ASSM位图信息来查看应该读取哪些块，而哪些块应该被忽略。

图10-2    低HWM示意图

10.2.4             freelists

使用MSSM表空间时，Oracle会在自由列表（freelist）中为有自由空间的对象维护HWM一些的块。

注意      freelists 组和freelist组在ASSM表空间中根本就没有；仅MSSM表空间使用这个技术。

每个对象都至少有一个相关的freelist，使用块时，可能会根据需要把块放在freelist上或者从freelist删除。需要说明的重要一点是，只有位于HWM以下的对象块才会出现在freelist中。仅当freelist为空时才会使用HWM之上的块，此时Oracle会推进HWM，并把这些块增加到freelist中，采用这种方式，Oracle会延迟到不得已时才增加对象的HWM。

一个对象可以有多个freelist。如果预计到会有多个并发用户在一个对象上执行大量的INSERT或UPDATE活动，就可以配置多个freelist，这对性能提升很有好处（但是可能要以额外的存储空间为代价）。根据需要配置足够多的freelist非常重要。

如果存在多个并发的插入和更新，在这样一个环境中，FREELISTS可能对性能产生巨大的影响（可能是促进，也可能是妨碍）。通过一个极其简单的测试就能看出正确地设置FREELISTS有什么好处。请考虑下面这个相对简单的表：

ops$tkyte@ORA10GR1> create table t ( x int, y char(50) ) tablespace MSSM; Table created.

接下来使用5个并发会话，开始“疯狂地”对这个表执行插入。如果分别测量插入前和插入后与块相关的系统级等待事件，就会发现长时间的等待，特别是对数据块的等待（试图插入数据）。这通常是因为表（以及索引）上的freelist不足造成的（不过关于索引的有关内容将在下一章更详细介绍）。为此我使用了Statspack，首先取一个statspace.snap，接下来执行一个脚本开始5个并发的SQL*Plus会话，等这些会话退出后再取另一个statspace.snap。这些会话运行的脚本很简单，如下：

begin          for i in 1 .. 100000          loop                    insert into t values ( i, 'x' );          end loop;          commit; end; / exit;

这是一个非常简单的代码块，此时我是数据库中惟一的用户。按理说，应该得到最佳的性能，因为我配置了充足的缓冲区缓存，重做日志大小很合适，另外索引也不会减慢速度，而且这是在有两个超线程Xeon CPU的主机上运行，这个主机应该能运行得很快。不过，我看到的结果却是：

Snap Id Snap Time                    Sessions Curs/Sess Comment                            ---------    ------------------               --------        ---------         ------------------- Begin Snap:  793         29-Apr-05 13:45:36    15              3.9 End Snap:       794         29-Apr-05 13:46:34    15              5.7 Elapsed:                           0.97 (mins) Top 5 Timed Events ~~~~~~~~~~~~~~~~~~                                                                            % Total Event                                                                Waits        Time (s)      Call Time --------------------------------------------          ------------        -----------      ------------- CPU time                                                                                    165             53.19 buffer busy waits                                       368,698                119             38.43 log file parallel write                                      1,323                  21               6.86 latch: cache buffers chains                              355                     2                 .67 enq: HW - contention                                    2,828                     1                 .24

对buffer busy waits总共等待了119秒，也就是每个会话大约24秒。导致这些等待的原因完全是：表中没有配置足够的freelist来应付发生的这种并发活动。不过，只需将表创建为有多个freelist，就能轻松地消除大部分等待时间：

ops$tkyte@ORA10GR1> create table t ( x int, y char(50) )          2 storage( freelists 5 ) tablespace MSSM; Table created.

或者也可以通过修改对象达到目的：

ops$tkyteORA10GR1> alter table t storage ( FREELISTS 5 ); Table altered.

你会看到，buffer busy waits大幅下降，而且所需的CPU时间也随着耗用时间的下降而减少（因为这里做的工作更少；对闩定数据结构的竞争确实会让CPU焦头烂额）：

Snap Id Snap Time                    Sessions Curs/Sess Comment                            ---------    ------------------               --------        ---------         ------------------- Begin Snap:  809         29-Apr-05 14:04:07    15              4.0 End Snap:       810         29-Apr-05 14:04:41    14              6.0 Elapsed:                           0.57 (mins)   Top 5 Timed Events ~~~~~~~~~~~~~~~~~~                                                                          % Total Event                                                                Waits        Time (s)      Call Time --------------------------------------------          ------------        -----------           --------- CPU time                                                            122                                     74.66 buffer busy waits                                          76,538                  24             14.94 log file parallel write                                         722                  14               8.45 latch: cache buffers chains                              144                     1                 .63 enq: HW - contention                                        678                     1                 .46

对于一个表来说，你可能想确定最多能有多少个真正的并发插入或更新（这需要更多空间）。这里我所说的“真正的并发”是指，你认为两个人在同一时刻请求表中一个自由块的情况是否频繁。这不是对重叠事务的一种量度；而是量度多少个会话在同时完成插入，而不论事务边界是什么。你可能希望对表的并发插入有多少，freelist就有多少，以此来提高并发性。

只需把freelist设置得相当高，然后就万事大吉了，是这样吗？当然不是，哪有这么容易。使用多个freelist时，有一个主freelist，还有一些进程freelist。如果一个段只有一个freelist，那么主freelist和进程freelist就是这同一个自由列表。如果你有两个freelist，实际上将有一个主freelist和两个进程freelist。对于一个给定的会话，会根据其会话ID的散列值为之指定一个进程freelist。目前，每个进程freelist都只有很少的块，余下的自由块都在主freelist上。使用一个进程freelist时，它会根据需要从主freelist拉出一些块。如果主freelist无法满足空间需求，Oracle就会推进HWM，并向主freelist中增加空块。过一段时间后，主freelist会把其存储空间分配多个进程freelist（再次说明，每个进程freelist都只有为数不多的块）。因此，每个进程会使用一个进程freelist。它不会从一个进程freelist到另一个进程freelist上寻找空间。这说明，如果一个表上有10个进程freelist，而且你的进程所用的进程freelist已经用尽了该列表中的自由缓冲区，它不会到另一个进程freelist上寻找空间，即使另外9个进程freelist都分别有5块（总共有45个块），此时它还是会去求助主freelist。假设主freelist上的空间无法满足这样一个自由块请求，就会导致表推进HWM，或者如果表的HWM无法推进（所有空间都已用），就要扩展表的空间（得到另一个区段）。然后这个进程仍然只使用其freelist上的空间（现在不再为空）。使用多个freelist时要有所权衡。一方面，使用多个freelist可以大幅度提升性能。另一方面，有可能导致表不太必要地使用稍多的磁盘空间。你必须想清楚在你的环境中哪种做法麻烦比较小。

不要低估了FREELISTS参数的用处，特别是在Oracle 8.1.6及以后版本中，你可以根据意愿自由地将其改大或改小。可以把它修改为一个大数，从而与采用传统路径模式的SQL*Loader并行完成数据的加载。这样可以获得高度并发的加载，而只有最少的等待。加载之后，可以再把这个值降低为某个更合理的平常的数。将空间改小时，现有的多个freelist上的块要合并为一个主freelist。

要解决前面提到的缓冲区忙等待问题，还有一种方法，这就是使用一个ASSM管理的表空间。还是前面的例子，但在ASSM管理的表空间中要如下创建表T：

ops$tkyte@ORA10GR1> create tablespace assm 2 datafile size 1m autoextend on next 1m 3 segment space management auto; Tablespace created.   ops$tkyte@ORA10GR1> create table t ( x int, y char(50) ) tablespace ASSM; Table created.

你会看到，在这种情况下，缓冲区忙等待、CPU时间和耗用时间都会下降，在此不必确定最好要有多少个freelist：

Snap Id Snap Time                    Sessions Curs/Sess Comment                            ---------    ------------------               --------        ---------         ------------------- Begin Snap:  812         29-Apr-05 14:12:37    15              3.9 End Snap:       813         29-Apr-05 14:13:07    15              5.6 Elapsed:                           0.50 (mins)   Top 5 Timed Events ~~~~~~~~~~~~~~~~~~                                                                          % Total Event                                                                Waits        Time (s)      Call Time --------------------------------------------          ------------        -----------           --------- CPU time                                                            107                                     78.54 log file parallel write                                         705                  12               9.13 buffer busy waits                                          12,485                  12               8.52 latch: library cache                                              68                     1                 .70 LGWR wait for redo copy                             3,794                     1                 .47

这就是ASSM的主要作用之一：不必手动地确定许多关键存储参数的正确设置。

10.2.5             PCTFREE和PCTUSED

一般而言，PCTFREE参数用来告诉Oracle应该在块上保留多少空间来完成将来的更新。默认情况下，这个值是10％。如果自由空间的百分比高于PCTFREE中的指定值，这个块就认为是“自由的”。PCTUSED则告诉Oracle当前不“自由”的一个块上自由空间百分比需要达到多大才能使它再次变为自由的。默认值是40％¹。

如前所述，对于一个表（而不是一个IOT，有关内容稍后再介绍），PCTFREE会告诉Oracle：块上应该保留多大的空间来完成将来的更新。这说明，如果我们使用的块大小为8KB，只要向块中增加一个新行，就会导致块上的自由空间下降大约800字节，Oracle会使用FREELIST的另一个块，而不是现有的块。块上这10％的数据空间会预留出来，以便更新该块上的行。

1．                  实际上PCTUSED的含义是，如果块上不自由的空间到达或小于PCTUSED参数指定的百分比时，这个块将重新变为自由，如倘若PCTUSED为40％，那么块上不自由的空间小于40％时，即自由空间达到60％时，这个块就重新变为自由。——译者注。

注意       对于不同的表类型，PCTFREE和PCTUSED的实现有所不同。对于某些表类型，这两个参数都要使用，而另外一些表类型只使用PCTFREE，而且对于这些表类型，仅当创建对象时才会使用PCTFREE。IOT在创建时可以使用PCTFREE在表中预览空间来完成将来的更新，但是在其他方面并不使用PCTFREE，例如，PCTFREE不用于决定何时停止向一个给定块中插入行。

根据你使用的是ASSM表空间还是MSSM表空间，这两个参数的实际作用会有所不同。使用MSSM时，这些参数设置控制着块何时放入freelist中，以及何时从freelist中取出。如果使用默认值：PCTFREE为10，PCTUSED为40，那么在块到达90%满之前（有10%以上的自由空间），这个块会一直在freelist上。一旦到底90%，就会从freelist中取出，而且直到块上的自由空间超过了块的60%时，才会重新回到freelist上，在此之前，这个块一直不在freelist上。

使用ASSM时，PCTFREE仍然会限制能否将一个新行插入到一个块中，但是它不会控制一个块是否在freelist上，因为ASSM根本不使用freelist。在ASSM中，PCTUSED将被忽略。

PCTFREE 有3种设置：太高、太低好刚好。如果把块的PCTFREE设置得过高，就会浪费空间。如果把PCTFREE设置为50%，而你从未更新数据，那么每个块都会浪费50%的空间。不过，在另一个表上，50%可能非常合理。如果行初始很小，现在想将行的大小加倍，但是倘若PCTFREE设置得太小，更新行时就会导致行迁移。

1.      行迁移

到底什么是行迁移？行迁移（row migration）是指由于某一行变得太大，无法再与其余的行一同放在创建这一行的块中（块中已经放不下这一行），这就要求这一行离开原来的块。这一节将分析行迁移。首先来看一个块，如同10-3所示。

图10-3 更新前的数据块

这个块上大约1/7是自由空间。不过，我们想通过一个UPDATE将第4行所有的空间加倍（第4行现在占用了块上1/7的空间）。在这种情况下，即使Oracle合并了块上的空间（如同10-4所示），还是没有足够的空间将第4行的大小加倍，因为自由空间小于第4行的当前大小。

图10-4     合并自由空间之后可能得到的数据块

如果这一行能在合并的空间中放下，自然就会这么做。不过，在此Oracle没有完成这个合并，块还是保持原样。因为第4行如果还呆在这个块上，它就必须跨块，所以Oracle会移动或迁移这一行。不过，Oracle不能简单地移动这一行，它必须留下一个“转发地址”。可能有一些索引物理地指向第4行的这个地址。简单的更新不会同时修改这些索引（注意对于分区表则有一个特例：更新分区表时，rowid即行地址会改变。这种情况将在第13章介绍）。因此，Oracle迁移这一行时，它会留下一个指针，指示这一行实际上在什么位置。更新之后，块可能如图10-5所示。

图10-5     迁移行示意图

因此，迁移行（migrated row）就是这一行从最初所插入的块上移到另外的某个块上。为什么这会带来问题？你的应用绝对不会知道存在行迁移；你使用的SQL也没有任何不同。行迁移只会影响性能。如果你通过一个索引来读这一行，索引会指向原来的块，那个块再指向这个新块。要得到具体的行数据，一般并不是执行两个左右的I/O就可以得到行数据。单独来看，这不是大问题，甚至根本注意不到。不过，如果这种行所占的比例相当大，而且有大量用户在访问这些行，你就会注意到这种副作用了。访问这些数据的速度开始变慢（额外的I/O以及与I/O相关的闩定都会增加访问时间），缓冲区缓存的效率开始下降（需要缓存两个块，而如果行没有迁移就只需要缓存一个块），另外表的大小好复杂性都有所增加。由于这些原因，你可能不希望迁移行。

有意思的是，如果一行从左边的块迁移到右边的块，如同10-5所示，而且它在将来某个时间点还要再迁移，Oracle会这样做呢？造成这种又一次迁移的原因可能是：在这一行迁移到的“目标”块上又增加了其他的行，然后这一行再次更新，变得更大。Oracle实际上会把这一行迁移回原来的块，如果有足够的空间，仍放回原地（这么一来，这一行可能变得“未迁移”）。如果没有足够的空间，Oracle会把这一行迁移到另外的某个块上，并修改原来块上的转发地址。因此，行迁移总是涉及一层间接性。

所以，现在我们再回到PCTFREE，来说明这个参数的作用：如果设置得当，这个参数可以帮助你尽量减少行串链。

2.      设置PCTFREE和PCTUSED值

设置PCTFREE和PCTUSED是一个很重要的主题，不过往往被忽视。总的来说，使用MSSM时，PCTUSED和PCTFREE都很重要；对于ASSM，只有PCTFREE是重要的。一方面，你要使用这些参数来避免迁移过多的行。另一方面，要使用这些参数避免浪费太多的空间。你需要查看对象，描述这些对象要如何使用，然后为设置这些值得出一个逻辑计划。设置这些参数时，如果主观地采用一般经验很可能招致失败；必须根据具体的使用设置。可以考虑以下做法（要记住，这里的“高”和“低”都是相对的；而且使用ASSM时仅PCTFREE适用）：

q         高PCTFREE，低PCTUSED：如果你插入了将要更新的大量数据，而且这些更新会频繁地增加行的大小，此时就适合采用这种设置。这种设置在插入后会在块上预留大量的空间（高PCTFREE），并使得将块放回到freelist之前必须几乎为空（低PCTUSED）。

q         低PCTFREE，高PCTUSED：如果你只想对表完成INSERT或DELETE，或者如果你确实要完成UPDATE，但UPDATE只是缩小行的大小，此时这种设置就很适合。

10.2.6             LOGGING和NOLOGGING

通常对象都采用LOGGING方式创建，这说明对象上完成的操作只要能生成redo就都会生成redo。NOLOGGING则允许该对象完成某些操作时可以不生成redo；这个内容在上一章详细介绍过。NOLOGGING只影响几个特定的操作，如对象的初始创建，或使用SQL*Loader的直接路径加载，或者重建（请参考Oracle SQL Reference手册来了解你使用的数据库对象可以应用哪些操作）。

这个选项并不会完全禁用对象的重做日志生成，只是几个特定的操作不生成日志而已。例如，如果把一个表创建为SELECT NOLOGGING，然后INSERT INTO THAT_TABLE VALUES(1)，这个INSERT就会生成日志，但是表创建可能不生成redo（DBA可以在数据库或表空间级强制生成日志）。

10.2.7             INITRANS和MAXTRANS

段中每个块都有一个块首部。这个块首部中有一个事务表。事务表中会建立一些条目来描述哪些事务将块上的哪些行/元素锁定。这个事务表的初始大小由对象的INITRANS设置指定。对于表，这个值默认为2（索引的INITRANS也默认为2）。事务表会根据需要动态扩展，最大达到MAXTRANS个条目（假设块上有足够的自由空间）。所分配的每个事务条目需要占用块首部中的23～24字节的存储空间。注意，对于Oracle 10g，MAXTRANS则会忽略，所有段的MAXTRANS都是255。

10.3   堆组织表

应用中99%（或者更多）的情况下使用的可能都是堆组织表，不过随着IOT的出现，这种状况以后可能会有所改观，因为IOT本身就可以加索引。执行CREATE TABLE语句时，默认得到的表类型就是堆组织表。如果你想要任何其他类型的表结构，就需要在CREATE语句本身中指定它。

堆（heap）是计算机科学领域中得到深入研究的一种经典数据结构。它实际上就是一个很大的空间、磁盘或内存区（当然，这里所说的磁盘是指数据库表的相应磁盘），会以一种显然随机的方式管理。数据会放在最合适的地方，而不是以某种特定顺序来放置。许多人希望能按数据放入表中的顺序从表中取出数据，但是对于堆，这是无法保证的。这一点很容易说清楚。

在以下的例子中，我将建立一个表，使得在我的数据库中每块刚好能放一个整行（我使用的块大小是8KB）。不一定非得每块上有一行，我只是想利用这一点来展示一种可预测的事务序列。不论数据库使用多大的块大小，也不论表的大小如何，都可以观察到以下行为（行没有次序）：

ops$tkyte@ORA10GR1> create table t 2 ( a int, 3 b varchar2(4000) default rpad('*',4000,'*'), 4 c varchar2(3000) default rpad('*',3000,'*') 5 ) 6 / Table created.   ops$tkyte@ORA10GR1> insert into t (a) values ( 1); 1 row created.   ops$tkyte@ORA10GR1> insert into t (a) values ( 2); 1 row created.   ops$tkyte@ORA10GR1> insert into t (a) values ( 3); 1 row created.   ops$tkyte@ORA10GR1> delete from t where a = 2 ; 1 row deleted.   ops$tkyte@ORA10GR1> insert into t (a) values ( 4); 1 row created.   ops$tkyte@ORA10GR1> 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会把数据放在能放下的任何地方，而不是按照日期或事务的某种顺序来存放。

如果你的查询需要按插入的顺序来获取数据，就必须向表中增加一列，以便获取数据时使用这个列对数据排序。例如，这可以是一个数字列，有一个递增的序列（使用Oracle SEQUENCE对象）。只需使用一个SELECT，其ORDER BY子句对这个列完成排序，这样就可以模拟插入顺序。这个顺序可能只是近似的，因为序号为55的行很可能在序号为54的行之前提交，因此，数据库中序号为55的行可能放在前面。

应该把堆组织表看作一个很大的无序行集合。这些行会以一种看来随机的顺序取出，而且取出的顺序还取决于所用的其他选项（并行查询、不同的优化器模式，等待），同一个查询可能会以不同的顺序取出数据。不要过分依赖查询得到的顺序，除非查询中有一个ORDER BY语句！

除此之外，关于堆表还有什么重要的内容需要了解？要知道，Oracle SQL Reference手册中介绍CREATE TABLE语法时足足用了72页，所以有关的选项当然多。由于存在如此之多的选项，所以很难全部掌握。“线路图”（或“轨迹”图）本身就用了18页来介绍。要了解一个给定表的CREATE TABLE语句中主要有哪些可用的选项，我用了一个技巧。首先，尽可能简单地创建表，例如：

ops$tkyte@ORA10GR1> create table t 2 ( x int primary key, 3 y date, 4 z clob 5 ) 6 / Table created.

然后，使用标准内置包DBMS_METADATA，查询这个表的定义，并查看详细语法：

ops$tkyte@ORA10GR1> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;   DBMS_METADATA.GET_DDL('TABLE','T') ------------------------------------------------------------------------------- CREATE TABLE "OPS$TKYTE"."T" (       "X" NUMBER(*,0),          "Y" DATE,          "Z" CLOB,          PRIMARY KEY ("X") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOB ("Z") STORE AS ( TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

这个技巧的好处是，它显示了CREATE TABLE语句的许多选项。我只需要提供数据类型，Oracle就会为我生成详细的“版本”（CREATE TABLE版本）。现在我可以定制这个详细的版本，可能把ENABLE STORAGE IN ROW改成DISABLE STORAGE IN ROW，这样会禁用随结构化数据在行中存储LOB数据，而把LOB数据存储在另外一个段中。我一直都在使用这个技巧来节省我的时间，因为要从那个庞大的线路图中找出该使用哪个选项很让人犯愁，如果不采用这个技巧，可能就会为此浪费好几分钟。使用这个技术还可以了解不同的情况下CREATE TABLE语句有哪些可用的选项。

既然你知道了如何查看一个给定的CREATE TABLE语句可用的大多数选项，那么对于堆表来说，需要注意哪些重要的选项呢？在我看来，对于ASSM有两个重要选项，对于MSSM，重要选项有4个：

FREELISTS ：仅适用于MSSM。每个表都会在一个freelist上管理堆中分配的块。一个表可以有多个freelist。如果你认定会有多个并发用户对表执行大量的插入，配置多个freelist可能会大大地改善性能（可能要以额外的存储空间为代价）。这个设置对性能可能产生的影响请参见“FREELISTS”一节中的讨论和有关例子。

PCTFREE ：ASSM和MSSM都适用。在INSERT过程中，会测量块的充满程度。如前所示，根据块当前充满的程度，这个参数用于控制能否将一行增加到一个块上。这个选项还可以控制因后续更新所导致的行迁移，要根据将如何使用表来适当地设置。

PCTUSED ：仅适用于MSSM。度量一个块必须为多空才允许再次插入行。如果块中已用的空间小于PCTUSED，就可以插入新行了。同样地，类似于PCTFREE，必须考虑你将如何使用表，从而适当地设置这个选项。

INITRANS ：ASSM和MSSM都适合。为块初始分配的事务槽数。如果这个选项设置得太低（默认值为2，这也是最小值），可能导致多个用户访问的一个块上出现并发问题。如果一个数据块机会已满，而且事务表无法动态扩展，会话就会排队等待这个块，因为每个并发事务都需要一个事务槽。如果你认为会对同样的块完成多个并发更新，就应该考虑增大这个值。

注意      单独存储在LOB段中的LOB数据并不使用表的PCTFREE/PCTUSED参数设置。这些LOB块以不同的方式管理：它们总是会填入，直至达到最大容量，而且仅当完全为空时才返回freelist。

这些参数要特别注意。随着本地管理表空间的引入（这也是强烈推荐的做法），我发现其余的参数（如PCTINCREASE、NEXT等）已经没有什么意义了。

10.4   索引组织表

索引组织表（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         current            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         current            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; Table created.

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

ops$tkyte@ORA10GR1> select dbms_metadata.get_ddl( 'TABLE', 'T1' ) from dual;   DBMS_METADATA.GET_DDL('TABLE','T1') -------------------------------------------------------------------------------- CREATE TABLE "OPS$TKYTE"."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 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" 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的值）重复出现时，将不再物理地存储它们。例如，请考虑如下创建的一个表：

ops$tkyte@ORA10GR1> create table iot 2 (         owner, object_type, object_name, 3 primary key(owner,object_type,object_name) 4 ) 5 organization index 6 NOCOMPRESS 7 as 8 select owner, object_type, object_name from all_objects 9 / Table 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 / Table created.

现在可以测量所用的空间。为此我们将使用ANALYZE INDEX VALIDATE STRUCTURE命令。这个命令会填写一个名为INDEX_STATS的动态性能视图，其中最多只包含一行，即这个ANALYZE命令最后一次执行的信息：

ops$tkyte@ORA10GR1> analyze index iot_pk validate structure; Index 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; Table altered.   ops$tkyte@ORA10GR1> analyze index iot_pk validate structure; Index 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; Table altered.   ops$tkyte@ORA10GR1> analyze index iot_pk validate structure; Index 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; (2/3)*2037497 ------------- 1358331.33

可以看到OPT_CMPR_PCTSAVE真是精准无比。上一个例子指出，关于IOT有一点很有意思：IOT是表，但是只是有其名而无其实。IOT段实际上是一个索引段。

现在我先不讨论PCTTHRESHOLD选项，因为它与IOT的下面两个选项有关：OVERFLOW和INCLUDING。如果查看以下两组表（T2和T3）的完整SQL，可以看到如下内容（这里我使用了一个DBMS_METADATA例程来避免STORAGE子句，因为它们对这个例子没有意义）：

ops$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;   DBMS_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" PCTTHRESHOLD 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;   DBMS_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 / Table 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 / Table 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的意义将与对于堆表一样重大；要采用与堆表相同的逻辑为溢出段设置这两个参数。

10.5   索引聚簇表

我常常发现，人们对Oracle中聚簇的理解是不正确的。许多人都把聚簇与SQL Server或Sybase中的“聚簇索引”相混淆。但它们并不一样。聚簇（cluster）是指：如果一组表有一些共同的列，则将这样一组表存储在相同的数据库块中；聚簇还表示把相关的数据存储在同一个块上。SQL Server中的聚簇索引（clustered index）则要求行按索引键有序的方式存储，这类似于前面所述的IOT。利用聚簇，一个块可能包含多个表的数据。从概念上讲，这是将数据“预联结”地存储。聚簇还可以用于单个表，可以按某个列将数据分组存储。例如，部门10的所有员工都存储在同一个块上（或者如果一个块放不下，则存储在尽可能少的几个块上）。聚簇并不是有序地存储数据（这是IOT的工作），它是按每个键以聚簇方式存储数据，但数据存储在堆中。所以，部门100可能挨在部门1旁边，而与部门101和99离得很远（这是指磁盘上的物理位置）。

如同10-8所示，图的左边使用了传统的表，EMP会存储在它的段中。DEPT也存储在自己的段中。它们可能位于不同的文件和不同的表空间，而且绝对在单独的区段中。从图的右边可以看到将这两个表聚簇起来会是什么情况。方框表示数据库块。现在将值10抽取出来，只存储一次。这样聚簇的所有表中对应部门10的所有数据都存储在这个块上。如果部门10的所有数据在一个块上放不下，可以为原来的块串链另外的块，来包含这些溢出的部分，这与IOT的溢出块所用的方式类似。

图10-8     索引聚簇数据

因此，下面来看如何创建一个聚簇对象。在对象中创建表的一个聚簇很直接。对象的存储定义（PCTFREE、PCTUSED、INITIAL等）与CLUSTER相关，而不是与表相关。这是有道理的，因为聚簇中会有多个表，而且它们在同一个块上。有多个不同的PCTFREE没有意义。因此，CREATE CLUSTER非常类似于只有很少几个列的CREATE TABLE（只有聚簇键列）：

ops$tkyte@ORA10GR1> create cluster emp_dept_cluster 2 (          deptno number(2) ) 3           size 1024 4 / Cluster created.

在此，我们创建了一个索引聚簇（index cluster，还有一种类型是散列聚簇（hash cluster），将在下一节介绍）。这个聚簇的聚簇列是DEPTNO列。表中的列不必非得叫DEPTNO，但是必须是NUMBER(2)，这样才能与定义匹配。我们在这个聚簇定义中加一个SIZE 1024选项。这个选项原来告诉Oracle：我们希望与每个聚簇键值关联大约1024字节的数据，Oracle会在用这个数据库块上设置来计算每个块最多能放下多少个聚簇键。假设块大小为8KB，Oracle会在每个数据库块上放上最多7个聚簇键（但是如果数据比预想的更大，聚簇键可能还会少一些）。也就是说，对应部门10、20、30、40、50、60和70的数据会放在一个块上，一旦插入部门80，就会使用一个新块。这并不是说数据按一种有序的方式存储，而是说如果按这种顺序插入部门，它们会很自然地放在一起。如果按下面的顺序插入部门，即先插入10、80、20、30、40、50、60，然后插入70，那么最后一个部门（70）将放在新增的块上。稍后会看到，数据的大小以及数据插入的顺序都会影响每个块上都存储的聚簇键个数。

因此，SIZE测试控制着每块上聚簇键的最大个数。这是对聚簇空间利用率影响最大的因素。如果把这个SIZE设置得太高，那么每个块上的键就会很少，我们会不必要地使用更多的空间。如果设置得太低，又会导致数据过分串链，这又与聚簇本来的目的不符，因为聚簇原本是为了把所有相关数据都存储在一个块上。对于聚簇来说，SIZE是最重要的参数。

下面来看聚簇上的聚簇索引。向聚簇中放数据之前，需要先对聚簇建立索引。可以现在就在聚簇中创建表，但是由于我们想同时创建和填充表，而有数据之前必须有一个聚簇索引，所以我们先来建立聚簇索引。聚簇索引的任务是拿到一个聚簇键值，然后返回包含这个键的块的块地址。实际上这是一个主键，其中每个聚簇键值指向聚簇本身中的一个块。因此，我们请求部门10的数据时，Oracle会读取聚簇键，确定相应的块地址，然后读取数据。聚簇键索引如下创建：

ops$tkyte@ORA10GR1> create index emp_dept_cluster_idx 2 on cluster emp_dept_cluster 3 / Index created.

对于索引平常有的存储参数，聚簇索引都可以有，而且聚簇索引可以存储在另一个表空间中。它就像是一个常规的索引，所以同样可以在多列上建立；聚簇索引只不过恰好是一个聚簇的索引，另外可以包含对应完全null值的条目（这很有意思，之所以要指出这一点，原因将在第11章解释）。注意，在这个CREATE INDEX语句中，并没有指定列的一个列表，索引列可以由CLUSTER定义本身得出。现在我们可以在聚簇中创建表了：

ops$tkyte@ORA10GR1> create table dept 2 (         deptno number(2) primary key, 3           dname varchar2(14), 4           loc varchar2(13) 5 ) 6 cluster emp_dept_cluster(deptno) 7 / Table created.   ops$tkyte@ORA10GR1> create table emp 2 (         empno number primary key, 3           ename varchar2(10), 4           job varchar2(9), 5           mgr number, 6           hiredate date, 7           sal number, 8           comm number, 9           deptno number(2) references dept(deptno) 10 ) 11 cluster emp_dept_cluster(deptno) 12 / Table created.

在此，与“正常”表惟一的区别是，我们使用了CLUSTER关键字，并告诉Oracle基表的哪个列会映射到聚簇本身的聚簇键。要记住，这里的段是聚簇，因此这个表不会有诸如TABLESPACE、PCTFREE等段属性，它们都是聚簇段的属性，而不是我们所创建的表的属性。现在可以向这些表加载初始数据集：

ops$tkyte@ORA10GR1> begin 2   for x in ( select * from scott.dept ) 3 loop 4           insert into dept 5                     values ( x.deptno, x.dname, x.loc ); 6           insert into emp 7                     select * 8                     from scott.emp 9                     where deptno = x.deptno; 10 end loop; 11 end; 12 / PL/SQL procedure successfully completed.

你可能会奇怪，为什么不是插入所有DEPT数据，然后再插入所有EMP数据呢？或者反之，先插入所有EMP数据，然后插入所有DEPT数据？为什么要像这样按DEPTNO逐个地加载数据呢？原因就在于聚簇的设计。我们在模拟一个聚簇的大批量初始加载。如果写加载所有DEPT行，每个块上就会有7个键（根据前面指定的SIZE 1024设置），这是因为DEPT行非常小（只有几个字节）。等到加载EMP行时，可能会发现有些部门的数据远远超过了1024字节。这样就会在那些聚簇键块上导致过度的串链。Oracle会把包含这些信息的一组块串链或链接起来。如果同时加载对应一个给定聚簇键的所有数据，就能尽可能紧地塞满块，等空间用完时再开始一个新块。Oracle并不是在每个块中放最多7个聚簇键值，而是会适当地尽可能多地放入聚簇键值。

下面给出一个小例子，从中可以看出这两种方法的区别。我们将向EMP表增加一个很大的列：CHAR(1000)。加这个列是为了让EMP行远远大于现在的大小。我们将以两种方式加载聚簇表：先加载DEPT，再加载EMP。第二次加载时，则会按部门编号来加载：先是一个DEPT行，然后是与之相关的所有EMP行，然后又是一个DEPT行。我们将查看给定情况下每一行最后在哪个块上，从而得出哪种方法最好，能最好地实现将数据按DEPTNO共同存储的目标。我们的EMP表如下：

ops$tkyte@ORA10GR1> create table emp 2 (         empno number primary key, 3           ename varchar2(10), 4           job varchar2(9), 5           mgr number, 6           hiredate date, 7           sal number, 8           comm number, 9           deptno number(2) references dept(deptno), 10         data char(1000) 11 ) 12 cluster emp_dept_cluster(deptno) 13 / Table created.

向DEPT和EMP表中加载数据时，可以看到许多EMP行与DEPT行不在同一个块上（DBMS_ROWID）是一个内置包，可以用于查看行ID的内容）：

ops$tkyte@ORA10GR1> insert into dept 2          select * from scott.dept; 4 rows created.   ops$tkyte@ORA10GR1> insert into emp 2          select emp.*, '*' from scott.emp; 14 rows created.   ops$tkyte@ORA10GR1> select dept_blk, emp_blk, 2                   case when dept_blk <> emp_blk then '*' end flag, 3                   deptno 4          from ( 5                   select dbms_rowid.rowid_block_number(dept.rowid) dept_blk, 6                            dbms_rowid.rowid_block_number(emp.rowid) emp_blk, 7                            dept.deptno 8                   from emp, dept 9                   where emp.deptno = dept.deptno 10                 ) 11       order by deptno 12 /        DEPT_BLK    EMP_BLK     F      DEPTNO     ----------------    --------------      -          ----------                 4792              4788     *                  10                 4792              4788     *                  10                 4792              4791     *                  10                 4792              4788     *                  20                 4792              4788     *                  20                 4792              4792                          20                 4792              4792                          20                 4792              4791     *                  20                 4792              4788     *                  30                 4792              4792                          30                 4792              4792                          30                 4792              4792                          30                 4792              4792                          30                 4792              4788     *                  30 14 rows selected.

一半以上的EMP行与DEPT行不在同一个块上。如果使用聚簇键而不是表键来加载数据，会得到以下结果：

ops$tkyte@ORA10GR1> begin 2          for x in ( select * from scott.dept ) 3          loop 4                   insert into dept 5                            values ( x.deptno, x.dname, x.loc ); 6                   insert into emp 7                            select emp.*, 'x' 8                            from scott.emp 9                            where deptno = x.deptno; 10       end loop; 11       end; 12 / PL/SQL procedure successfully completed.   ops$tkyte@ORA10GR1> select dept_blk, emp_blk, 2          case when dept_blk <> emp_blk then '*' end flag, 3          deptno 4 from ( 5          select dbms_rowid.rowid_block_number(dept.rowid) dept_blk, 6                   dbms_rowid.rowid_block_number(emp.rowid) emp_blk, 7                   dept.deptno 8          from emp, dept 9          where emp.deptno = dept.deptno 10 ) 11 order by deptno 12 /        DEPT_BLK    EMP_BLK     F      DEPTNO             ----------          ----------      -          ----------                      12                  12                          10                      12                  12                          10                      12                  12                          10                      11                  11                          20                      11                  11                          20                      11                  11                          20                      11                  12     *                  20                      11                  11                          20                      10                  10                          30                      10                  10                          30                      10                  10                          30                      10                  10                          30                      10                  10                          30                      10                  11     *                  30 14 rows selected.

注意      你看到的结果可能与此不同，因为从SCOTT.DEPT表获取行的顺序可能会（而且将会）改变这个结果，另外使用ASSM或是MSSM也会带来影响。不过，概念应该很清楚：如果把对应DEPTNO=n的行放在一个给定块上，然后再加载对应DEPTNO=n的员工行，就能得到最佳的聚簇。

大多数EMP行都与DEPT行在同一个块上。这个例子少有些技巧，因为我故意把SIZE参数设置得很小以便得出结论，不过这里建议的方法对于聚簇的初始加载确实是正确可行的。由此可以确保，如果某些聚簇键超过了估计的SIZE，最后大多数数据都会聚簇到同一个块上。如果一次加载一个表，则做不到这一点。

这种技术只适用于聚簇的初始加载，在此之后，只有在事务认为必要的时候才应使用这个技术。你不会为了专门使用聚簇去调整应用。

这里存在一个很让人诧异的困惑。许多人错误地认为一个rowid能惟一地标识数据库中的一个行，给定一个rowid，就能得出这一行来自哪个表。实际上，这是做不到的。从聚簇可以得到（而且将得到）重复的rowid。例如，执行以上代码后，你会发现：

ops$tkyte@ORA10GR1> select rowid from emp 2 intersect 3 select rowid from dept;   ROWID ------------------ AAAOniAAJAAAAAKAAA AAAOniAAJAAAAAKAAB AAAOniAAJAAAAALAAA AAAOniAAJAAAAAMAAA

DEPT 中为各行分配的每个rowid也同时分配给了EMP中的行。这是因为，要由表和行ID共同地惟一标识一行。Rowid伪列只是在一个表中惟一。

我还发现，许多人认为聚簇对象是一种神秘的对象，以为没有人用它，所有人都只是在使用普通表。事实上，每次你使用Oracle的时候都会使用聚簇。例如，许多数据字典就存储在各个聚簇中：

sys@ORA10GR1> break on cluster_name sys@ORA10GR1> select cluster_name, table_name 2          from user_tables 3          where cluster_name is not null 4          order by 1;   CLUSTER_NAME                        TABLE_NAME ------------------------------                 ------------------------------ C_COBJ#                                      CCOL$                                                           CDEF$ C_FILE#_BLOCK#                      UET$                                                           SEG$ C_MLOG#                                     MLOG$                                                           SLOG$ C_OBJ#                                         ICOL$                                                           CLU$                                                           COL$                                                           TYPE_MISC$                                                           VIEWTRCOL$                                                           ATTRCOL$                                                           SUBCOLTYPE$                                                           COLTYPE$                                                           LOB$                                                           TAB$                                                           IND$                                                           ICOLDEP$                                                           OPQTYPE$                                                           REFCON$                                                           LIBRARY$                                                           NTAB$ C_OBJ#_INTCOL#                      HISTGRM$                                                           C_RG# RGROUP$                                                           RGCHILD$ C_TOID_VERSION#                   TYPE$                                                           COLLECTION$                                                           METHOD$                                                           RESULT$                                                           PARAMETER$                                                           ATTRIBUTE$ C_TS#                                            TS$                                                           FET$ C_USER#                                      USER$                                                           TSQ$ SMON_SCN_TO_TIME              SMON_SCN_TIME 36 rows selected.

可以看到，与对象相关的大多数数据都存储在一个聚簇（C_OBJ#聚簇）中：16个表都在同一个块中。这里存储的主要是与列相关的信息，所以关于表或索引列集的所有信息都物理地存储在同一个块上。这是有道理的：Oracle解析一个查询时，它希望访问所引用的表中所有列的数据。如果这些数据分布得到处都是，就要花一些时间才能把它们收集起来。如果数据都在一个块上，通常就能很容易地得到。

什么时候要使用聚簇呢？可能反过来回答什么时候不应该使用聚簇会更容易一些：

q         如果预料到聚簇中的表会大量修改：必须知道，索引聚簇会对DML的性能产生某种负面影响（特别是INSERT语句）。管理聚簇中的数据需要做更多的工作。

q         如果需要对聚簇中的表执行全表扫描：不只是必须对你的表中的数据执行全面扫描，还必须对（可能的）多个表中的数据进行全面扫描。由于需要扫描更多的数据，所以全表扫描耗时更久。

q         如果你认为需要频繁地TRUNCATE和加载表：聚簇中的表不能截除。这是显然的，因为聚簇在一个块上存储了多个表，必须删除聚簇表中的行。

因此，如果数据主要用于读（这并不表示“从来不写”；聚簇表完全可以修改），而且要通过索引来读（可以是聚簇键索引，也可以是聚簇表上的其他索引），另外会频繁地把这些信息联结在一起，此时聚簇就很适合。应用找出逻辑上相关而且总是一起使用的表，设计Oracle数据字典的人就是这样做的，他们把与列相关的所有信息都聚簇在一起。

索引聚簇表小结

利用聚簇表，可以物理地“预联结”数据。使用聚簇可以把多个表上的相关数据存储在同一个数据库块上。聚簇有助于完成总是把数据联结在一起或者访问相关数据集（例如，部门10中的每一个人）的读密集型操作。

聚簇表可以减少Oracle必须缓存的块数，从而提供缓存区缓存的效率。不好的一面是，除非你能正确地计算出SIZE参数设置，否则聚簇在空间利用方面可能效率低下，而且可能会使有大量DML的操作变慢。

10.6   散列聚簇表

散列聚簇表（Hash clustered table）在概念上与前面介绍的索引聚簇表非常相似，只有一个主要区别：聚簇键索引被一个散列函数所取代。表中的数据就是索引；这里没有物理索引。Oracle会取得一行的键值，使用每个内部函数或者你提供的每个函数对其计算散列，然后使用这个散列值得出数据应该在磁盘上的哪个位置。不过，使用散列算法来定位数据有一个副作用，如果不向表增加一个传统的索引，将无法对散列聚簇中的表完成区间扫描。在一个索引聚簇中，如果有以下查询：

select * from emp where deptno between 10 and 20

它就能利用聚簇键索引来找到这些行。在一个散列聚簇中，这个查询会导致一个全表扫描，除非DEPTNO列上已经有一个索引。如果没有使用一个支持区间扫描的索引，就只能在散列键上完成精确搜索（包括列表和子查询）。

理想情况下，散列键值均匀分布，并且有一个散列函数可以将这些散列键值均匀地分布到为散列聚簇分配的所有块上，从查询利用一个I/O就能直接找到数据。但在实际中，最后可能会有多个散列键值散列到同一个数据库块地址，而且这个块上放不下这么多散列键值。这就会导致块串链，Oracle必须用一个链表把块串起来，来保存散列到这个块的所有行。现在，当需要获取与某个散列键匹配的行时，可能必须访问多个块。

类似于编程语言中的散列表，数据库中的散列表有固定的“大小”。创建表时，必须确定这个表中将有多少个散列键（而且这个数永远不变）。但散列表的大小并不限制其中能放的行数。

图10-9是一个散列聚簇的图形表示，这里创建了表EMP。客户发出一个查询，其中的谓词条件中使用了散列聚簇键，Oracle会应用散列函数确定数据应该在哪个块中。然后读这个块来找到数据。如果存在许多冲突，或者CREATE CLUSTER的SIZE参数估计过低，Oracle会分配溢出块与原来的块串链起来。

图10-9 散列聚簇示意图

创建散列聚簇时，还是使用创建索引聚簇时所用的同样的CREATE CLUSTER语句，不过选项不同。这里只是要增加一个HASHKEYS选项来指定散列表的大小。Oracle得到你的HASHKEYS值，将其“舍入”为与之最接近的质数（散列键数总是一个质数）。然后Oracle再将SIZE参数乘以修改后的HASHKEYS值，计算出一个值。再根据这个值为聚簇分配空间，也就是说，至少要分配这么多字节的空间。这与前面的索引聚簇有很大差别，索引聚簇会在需要时动态地分配空间，散列聚簇则要预留足够的空间来保存（HASHKEYS/trunc(blocksize/SIZE)）字节的数据。例如，如果将SIZE设置为1,500字节，而且块大小为4KB，Oracle会在每个块上存储两个键。如果你计划有1,000个HASHKEY，Oracle就分配500个块。

有意思的是，不同于计算机语言中的传统散列表，这里允许有散列冲突，实际上，许多情况下还需要有冲突。还是用前面的DEPT/EMP例子，可以根据DEPTNO列建立一个散列聚簇。显然，多个行会散列到同一个值，这正是你希望的（因为它们有相同的DEPTNO）。这就反映了聚簇某些方面的特点：要把类似的数据聚簇在一起。正是由于这个原因，所以Oracle要求你指定HASHKEY（你预计一段时间会有多少个部门号）和SIZE（与各个部门号相关联的数据量）。Oracle会分配一个散列表来保存HASHKEY个部门，每个部门有SIZE字节的数据。你想避免的是无意的散列冲突。显而易见，如果就散列表的大小设置为1,000（实际上是1,099，因为散列表的大小总是质数，而且Oracle会为你找出与之最接近的质数），而你在表中放入了1,010个部门，就至少会存在一个冲突（两个不同部门散列到同一个值）。无意的散列冲突是要避免的，因为它们会增加开销，使块串链的可能性增加。

要查看散列聚簇会用哪种空间，下面就使用一个小工具——存储过程SHOW_SPACE（有关这个过程的详细介绍，请参见本书最前面的“环境配置”一节），这一章和下一章都就使用这个小工具。这个例程只是使用DBMS_SPACE提供的包来得到数据库中段所用存储空间的详细信息。

如果发出以下CREATE CLUSTER语句，可以看到它分配的存储空间如下：

ops$tkyte@ORA10GR1> create cluster hash_cluster 2 ( hash_key number ) 3 hashkeys 1000 4 size 8192 5 tablespace mssm 6 / Cluster created.   ops$tkyte@ORA10GR1> exec show_space( 'HASH_CLUSTER', user, 'CLUSTER' ) Free Blocks.............................                            0 Total Blocks............................                    1,024 Total Bytes.............................             8,388,608 Total MBytes............................                           8 Unused Blocks...........................                      14 Unused Bytes............................            114,688 Last Used Ext FileId....................                      9 Last Used Ext BlockId...................            1,033 Last Used Block.........................                   114 PL/SQL procedure successfully completed.

可以看到，为表分配的总块数为1,024。其中14个块未用（空闲）。另外有1个块用于维护表开销，以管理区段。因此，有1,099个块在这个对象的HWM之下，这些是聚簇使用的块。1,099是一个质数，而且正好是大于1000的最小质数，由于块大小为8KB，可以看到，Oracle实际上会分配（8,192×1,099）字节。由于区段的”舍入“而且/或者通过使用本地管理的表空间（区段的大小一致），实际分配的空间（8,388,608）比这个数稍高一些。

这个例子指出，关于散列聚簇需要注意以下问题。一般地，如果创建一个空表，该表在HWM下的块数为0.如果对它执行全表扫描，达到HWM就会停止。对于一个散列聚簇，表一开始就很大，需要花更长的时间创建，因为Oracle必须初始化各个块（而对于一般的表，这个动作通常在数据增加到表时才发生）。散列聚簇表有可能把数据放在第一个块和最后一个块中，而中间什么都没有。对一个几乎为空的散列聚簇进行前面扫描与全面扫描一个满的散列聚簇所花的时间是一样的。这不一定是件坏事：建立散列聚簇的本来目的是为了根据散列键查找从而非常快地访问数据。而不是为了频繁地对它进行全面扫描。

现在可以开始把表放在散列聚簇中，仍采用前面索引聚簇所用的方法：

Ops$tkyte@ORA10GR1> create table hashed_table 2 ( x number, data1 varchar2(4000), data2 varchar2(4000) ) 3 cluster hash_cluster(x); Table created.

为了看出散列聚簇可能有哪些区别，我建立了一个小测试。首先创建一个散列聚簇，在其中加载一些数据，再将这些数据复制到一个有传统索引的“常规“表中，然后对各个表完成一些随机读（对每个完成的随机读是一样的）。通过使用runstats、SQL_TRACE和TKPPOF，可以确定各个表的特征。以下先完成散列聚簇和表的建立，其后是分析：

ops$tkyte@ORA10GR1> create cluster hash_cluster 2 ( hash_key number ) 3 hashkeys 75000 4 size 150 5 / Cluster created.   ops$tkyte@ORA10GR1> create table t_hashed 2 cluster hash_cluster(object_id) 3 as 4 select * 5 from all_objects 6 / Table created.   ops$tkyte@ORA10GR1> alter table t_hashed add constraint 2 t_hashed_pk primary key(object_id) 3 / Table altered.   ops$tkyte@ORA10GR1> begin 2 dbms_stats.gather_table_stats( user, 'T_HASHED', cascade=>true ); 3 end; 4 / PL/SQL procedure successfully completed.

在此创建了一个SIZE为150字节的散列聚簇。这是因为，我认为我的表中一行的平均大小大约是100字节，但是根据实际数据，具体的行大小可能会上下浮动。然后在这个聚簇中创建并填充一个表，作为ALL_OBJECTS的一个副本。

接下来，创建这个表的传统版本的“克隆“（即相应的堆组织表）：

ops$tkyte@ORA10GR1> create table t_heap 2 as 3 select * 4 from t_hashed 5 / Table created.   ops$tkyte@ORA10GR1> alter table t_heap add constraint 2 t_heap_pk primary key(object_id) 3 / Table altered.   ops$tkyte@ORA10GR1> begin 2 dbms_stats.gather_table_stats( user, 'T_HEAP', cascade=>true ); 3 end; 4 / PL/SQL procedure successfully completed.

现在，我需要一些“随机“的数据，用来从各个表中抽取行。为此，我只是把所有OBJECT_ID选择到一个数组中，然后随机地排序，从而以一种分散的方式命中表的各个块。我使用了一个PL/SQL包来定义和声明这个数组，并使用一些PL/SQL代码来”准备“这个数组，填入随机数据：

ops$tkyte@ORA10GR1> create or replace package state_pkg 2 as 3           type array is table of t_hashed.object_id%type; 4           g_data array; 5 end; 6 / Package created.   ops$tkyte@ORA10GR1> begin 2           select object_id bulk collect into state_pkg.g_data 3           from t_hashed 4           order by dbms_random.random; 5 end; 6 / PL/SQL procedure successfully completed.

要看到各个表完成的工作，我使用了以下代码块（如果把这里出现的HASHED都代之以HEAP，就可以得到另一个要测试的代码块）：

ops$tkyte@ORA10GR1> declare 2           l_rec t_hashed%rowtype; 3 begin 4           for i in 1 .. state_pkg.g_data.count 5           loop 6                     select * into l_rec from t_hashed 7                     where object_id = state_pkg.g_data(i); 8           end loop; 9 end; 10 / PL/SQL procedure successfully completed.

接下来，就前面的代码块（以及用HEAP取代HASHED得到的代码块）运行3次。第一次运行是系统“热身“，以避免以后再完成硬解析。第二次运行这个代码块时，我使用runstats来查看二者的主要差别：先运行散列实现，然后运行堆实现。第三次运行代码块时，我启用了SQL_TRACE，从而能看到一个TKPPOF报告。runstats运行的报告如下：

ops$tkyte@ORA10GR1> exec runstats_pkg.rs_stop(10000); Run1 ran in 263 hsecs Run2 ran in 268 hsecs run 1 ran in 98.13% of the time   Name                                                                Run1             Run2             Diff LATCH.cache buffers chains                    99,891        148,031      48,140 STAT...Cached Commit SCN refer           48,144                     0     -48,144 STAT...no work - consistent re                  48,176                     0     -48,176 STAT...cluster key scans                            48,176                     0     -48,176 STAT...cluster key scan block                   48,176                     0     -48,176 STAT...table fetch by rowid                                  0          48,176      48,176 STAT...rows fetched via callba                            0          48,176      48,176 STAT...buffer is not pinned co                   48,176          96,352      48,176 STAT...index fetch by key                                     0          48,176      48,176 STAT...session logical reads                     48,901        145,239      96,338 STAT...consistent gets                                48,178        144,530      96,352 STAT...consistent gets from ca                  48,178        144,530      96,352 STAT...consistent gets - exami                            1        144,529    144,528   Run1 latches total versus runs -- difference and pct  Run1         Run2             Diff                 Pct  347,515    401,961        54,446          86.45%

现在，从墙上的时钟来看，两个仿真运行的时间是一样的。因为我有一个足够大的缓存区缓存来缓存这些结果，所以这在预料之中。不过，要注意一个重要差别，缓存区缓存链的闩大幅减少。第一个实现（散列实现）使用的闩少得多，这说明在一个读密集型环境中，散列实现应该能更好地扩缩，因为它需要的串行化资源（这些资源要求某种程度的串行化）更少。其原因完全在于，与HEAP表相比，散列实现需要的I/O显著减少，可以看到，报告中的一致获取统计就能反映出这点。TKPPOF反映得更清楚：

SELECT * FROM T_HASHED WHERE OBJECT_ID = :B1 call                count            cpu    elapsed            disk         query      current       rows -------               ------        --------      ----------      ----------      ----------      ---------- ---------- Parse                    1           0.00           0.00                 0                 0                 0             0 Execute      48174           4.77           4.83                 0                 2                 0             0 Fetch           48174           1.50           1.46    0 48174                 0       48174 -------               ------        --------      ----------      ----------      ----------      ---------- ---------- total             96349           6.27           6.30                 0       48176                 0    48174   Rows       Row Source Operation -------        --------------------------------------------------- 48174      TABLE ACCESS HASH T_HASHED (cr=48174 pr=0 pw=0 time=899962 us) ******************************************************************************** SELECT * FROM T_HEAP WHERE OBJECT_ID = :B1 call                count            cpu    elapsed            disk         query      current       rows -------               ------        --------      ----------      ----------      ----------      ---------- ---------- Parse                    1           0.00           0.00                 0                 0                 0             0 Execute      48174           5.37           5.02                 0                 0                 0             0 Fetch           48174           1.36           1.32                 0     144522                 0    48174 -------               ------        --------      ----------      ----------      ----------      ---------- ---------- total             96349           6.73           6.34                 0     144522                 0    48174 Rows       Row Source Operation -------        --------------------------------------------------- 48174      TABLE ACCESS BY INDEX ROWID T_HEAP (cr=144522 pr=0 pw=0 time=1266695 us) 48174      INDEX UNIQUE SCAN T_HEAP_PK (cr=96348 pr=0 pw=0 time=700017 us)(object ...

HASHED 实现只是把传递到查询的OBJECT_ID转换为要读取的一个FILE/BLOCK，并且直接读，这里没有索引。不过，HEAP表则不同，它必须对每一行在索引上完成两个I/O。TKPROF Row Source Operation行中的cr=96348清楚地显示了对索引做了多少次一致读。每次查找OBJECT_ID = ：B1时，Oracle必须得到索引的根块，然后找出包含该行位置的叶子块。接下来必须得到叶子块信息，其中包含行的ROWID，再利用第3个I/O在表中访问这个行。HEAP表完成的I/O是HASHED实现的3倍。

这里的要点是：

q         散列聚簇完成的I/O（查询列）少得多。这正是我们期望的。查询只是取随机的OBJECT_ID，对其完成散列，然后找到块。散列聚簇至少要做一次I/O来得到数据。有索引的传统表则必须完成索引扫描，然后要根据rowid访问表，才能得到同样的答案。在这个例子中，索引表必须至少完成3个I/O才能得到数据。

q         不论用于什么目的，散列聚簇查询与索引查询所用的CPU是一样的，尽管它访问缓存区缓存的次数只是后者的1/3。同样，这也在预料之中。执行散列是一个CPU相当密集的操作，执行索引查询则是一个I/O密集的操作，这里要做个权衡。不过，随着用户数的增加，可以想见，散列聚簇查询能更好地扩缩，因为要想很好地扩缩，就不能太过频繁地访问缓存区缓存。

最后一点很重要。使用计算机时，我们所关心的就是资源及其利用。如果存在大量I/O，并且像这里一样，所执行的查询要根据键做大量的读操作，此时散列聚簇就能提供性能。如果已经占用了大量CPU时间，再采用散列聚簇反而会降低性能，因为它需要更多的CPU时间来执行散列。不过，如果耗用更多CPU时间的原因是缓冲区缓存的闩存在自旋，那么散列聚簇就能显著减少所需的CPU时间。这就说明了为什么有些经验在实际系统中不适用；有些经验对于你来说也许很合适，但是在类似但不同的条件下，这些经验可能并不可行。

散列聚簇有一个特例，称为单表散列聚簇（single table hash cluster）。这是前面介绍的一般散列聚簇的优化版本。它一次只支持聚簇中的一个表（必须DROP（删除）单表散列聚簇中现有的表，才能在其中创建另一个表）。另外，如果散列键和数据行之间存在一对一的映射，访问行还会更快一些。这种散列聚簇是为以下情况设计的：如果你想按主键来访问一个表，但是不关心其他表是否与这个表聚簇在一起存储。如果你需要按EMPNO快速地访问员工记录，可能就需要一个单表散列聚簇。我在一个单表散列聚簇上执行了前面的测试，发现性能比一般的散列聚簇还要好。不过，甚至还可以将这个例子更进一步，由于Oracle允许你编写自己的散列函数（而不是使用Oracle提供的默认散列函数），所以能利用这一点。不过，你只能使用表中可用的列，而且编写自己的散列函数时只能使用Oracle的内置函数（例如，不能有PL/SQL代码）。由于上例中OBJECT_ID是一个介于1～75,000之间的数，充分利用这一点，我建立了自己的“散列函数”：就是OBJECT_ID本身。采用这种方式，可以保证绝对不会有散列冲突。综合在一起，我如下创建一个单表散列聚簇（有我自己的散列函数）：

ops$tkyte@ORA10GR1> create cluster hash_cluster 2 ( hash_key number(10) ) 3 hashkeys 75000 4 size 150 5 single table 6 hash is HASH_KEY 7 / Cluster created.

这里只是增加了关键字SINGLE TABLE，使之作为一个单步散列聚簇。在这种情况下，我的散列函数就是HASH_KEY聚簇键本身。这是一个SQL函数，所以如果我愿意，也可以使用trunc(mod(hash_key/324+278,555)/abs(hash_key+1))（并不是说这是一个好的散列函数，这只是说明，只要我们愿意，完全可以使用一个复杂的函数）。我使用了NUMBER(10)而不是NUMBER，这是因为散列值必须是一个整数，所以不能有任何小数部分。下面，在这个表单散列聚簇中创建表：

ops$tkyte@ORA10GR1> create table t_hashed 2          cluster hash_cluster(object_id) 3          as 4           select OWNER, OBJECT_NAME, SUBOBJECT_NAME, 5              cast( OBJECT_ID as number(10) ) object_id, 6                   DATA_OBJECT_ID, OBJECT_TYPE, CREATED, 7                   LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, 8                   GENERATED, SECONDARY 9           from all_objects 10 / Table created.

以上建立了散列表。注意这里使用了CAST内置函数将OBJECT_ID强制转换为它本来的数据类型。像前面一样运行测试（每个代码块运行3次），这一次runstats的输出表明情况更好了：

Run1 ran in 224 hsecs Run2 ran in 269 hsecs run 1 ran in 83.27% of the time   Name                                                    Run1              Run2            Diff STAT...index fetch by key                         0           48,178     48,178 STAT...buffer is not pinned co       48,178           96,356     48,178 STAT...table fetch by rowid                       0           48,178     48,178 STAT...cluster key scans                48,178                      0    -48,178 STAT...session logical reads         48,889         145,245     96,356 STAT...consistent gets                    48,184         144,540     96,356 STAT...consistent gets from ca      48,184         144,540     96,356 STAT...consistent gets - exami      48,184         144,540     96,356 LATCH.cache buffers chains         51,663         148,019     96,356   Run1 latches total versus runs -- difference and pct Run1        Run2               Diff                    Pct 298,987  402,085          103,098           74.36% PL/SQL procedure successfully completed.

这个单表散列聚簇需要更少的缓冲区缓存闩来完成处理（它能更快地结束数据查找，而且能得到更多的信息）。因此，TKPROF报告显示出这一次的CPU使用量大幅减少：

SELECT * FROM T_HASHED WHERE OBJECT_ID = :B1   call                count            cpu    elapsed            disk         query      current       rows -------               ------        --------      ----------      ----------      ----------      ---------- ---------- Parse                    1           0.00           0.00                 0                 0                 0             0 Execute      48178           4.45           4.52                 0                 2                 0             0 Fetch           48178           0.67           0.82                 0       48178                 0    48178 -------               ------        --------      ----------      ----------      ----------      ---------- ---------- total             96357           5.12           5.35                 0       48180                 0    48178   Rows       Row Source Operation -------        --------------------------------------------------- 48178      TABLE ACCESS HASH T_HASHED (cr=48178 pr=0 pw=0 time=551123 us) ******************************************************************************** SELECT * FROM T_HEAP WHERE OBJECT_ID = :B1   call                count            cpu    elapsed            disk         query      current       rows -------               ------        --------      ----------      ----------      ----------      ---------- ---------- Parse                    1           0.00           0.00                 0                 0                 0             0 Execute      48178           5.38           4.99                 0                 0                 0             0 Fetch           48178           1.25           1.65                 0     144534                 0    48178 -------               ------        --------      ----------      ----------      ----------      ---------- ---------- total             96357           6.63           6.65                 0     144534                 0    48178   Rows       Row Source Operation -------        --------------------------------------------------- 48178      TABLE ACCESS BY INDEX ROWID T_HEAP (cr=144534 pr=0 pw=0 time=1331049 us) 48178      INDEX UNIQUE SCAN T_HEAP_PK (cr=96356 pr=0 pw=0 time=710295 us)(object...

散列聚簇表小结

以上就是散列聚簇的核心。散列聚簇在概念上与索引聚簇很相似，只不过没有使用聚簇索引。在这里，数据就是索引。聚簇键散列到一个块地址上，数据应该就在那个位置上。关于散列聚簇，需要了解以下要点：

q         散列聚簇一开始就要分配空间。Oracle根据你的HASHKEYS和SIZE来计算HASHKEYS/trunc（blocksize/SIZE），立即分配空间，并完成格式化，一旦将第一个表放入这个聚簇中，任何全面扫描都会命中每一个已分配的块。在这方面，它与其他的所有表都不同。

q         散列聚簇中的HASHKEY数是固定大小的。除非重新聚簇，否则不能改变散列表的大小。这并不会限制聚簇中能存储的数据量，它只是限制了能为这个聚簇生成的惟一散列键的个数。如果HASHKEY值设置得太低，可能因为无意的散列冲突影响性能。

q         不能在聚簇键上完成区间扫描。诸如WHERE cluster_key BETWEEN 50 AND 60谓词条件不能使用散列算法。介于50～60之间的可能值有无限多个，服务器必须生成所有可能的值，并分别计算散列，来查看相应位置是否有数据。这是不可能的。如果你在一个聚簇键上使用区间扫描，而且没有使用传统索引，实际上会全面扫描这个聚簇。

散列聚簇适用于以下情况：

你很清楚表中会有多少行，或者你知道一个合理的上界。HASHKEY和SIZE参数的大小要正确，这对于避免聚簇重建至关重要。

与获取操作相比，DML（特别是插入）很轻。这说明必须在数据获取的优化和新数据的创建之间有所权衡。有多少个插入算轻，对此没有定论，对某些人来说，每个单位时间有100,000个插入就算轻，而在另一个人来看，可能每单位时间100个插入才算轻——这取决于具体的数据获取模式。更新不会引入严重的开销，除非更新了HASHKEY（不过这可不是一个好主意，因为更新HASHKEY会导致行迁移）。

经常按HASHKEY值访问数据。例如，假如有一个零件表，并按零件号来访问这些零件。查找表特别适合采用散列聚簇。

10.7   有序散列聚簇表

有序散列聚簇是Oracle 10g中新增的。其中不仅有前面所述的散列聚簇的有关性质，还结合了IOT的一些性质。如果经常使用类似于以下的查询来获取数据，有序散列聚簇就最适合：

Select * From t Where KEY=:x Order by SORTED_COLUMN

也就是说，要按某个键获取数据，但要求这些数据按另外每个列排序。通过使用有序散列聚簇，Oracle可以返回数据而根据不用执行排序。这是通过插入时按键的有序物理存储数据做到的。假设有一个客户订单表：

ops$tkyte@ORA10G> select cust_id, order_dt, order_number 2 from cust_orders 3 order by cust_id, order_dt;       CUST_ID  ORDER_DT                                          ORDER_NUMBER     -------------  --------------------------------------------      --------------------------                    1  31-MAR-05 09.13.57.000000 PM                            21453                          11-APR-05 08.30.45.000000 AM                            21454                          28-APR-05 06.21.09.000000 AM                            21455                    2  08-APR-05 03.42.45.000000 AM                            21456                          19-APR-05 08.59.33.000000 AM                            21457                          27-APR-05 06.35.34.000000 AM                            21458                          30-APR-05 01.47.34.000000 AM                            21459 7 rows selected.

这个表存储在一个有序散列聚簇中，在此HASH键是CUST_ID，并按ORDER_DT字段排序。如图10-10所示，这里1、2、3、4、…分别表示每个块上依序存储的记录。

图10-10   有序散列聚簇示意图

创建有序散列聚簇与创建其他聚簇基本相同。要建立一个能存储以上数据的有序散列聚簇，可以使用下面的语句：

ops$tkyte@ORA10G> CREATE CLUSTER shc 2 ( 3          cust_id NUMBER, 4          order_dt timestamp SORT 5 ) 6 HASHKEYS 10000 7 HASH IS cust_id 8 SIZE 8192 9 / Cluster created.

这里引入了一个新的关键字：SORT。创建聚簇时，我们标识了HASH IS CUST_ID，而且用关键字SORT增加了一个时间戳（timestamp）类型的ORDER_DT。这说明，数据将按CUST_ID查找（查找条件是CUST_ID = :X），而按ORDER_DT物理地获取和排序。从技术上讲，实际上这表示我们存储的数据将通过一个NUMBER列获取，但按一个TIMESTAMP列排序。这里的列名并不重要，因为列名并不出现在B*树或HASH聚簇中，不过一般约定是根据所表示的内容来命名。

这个CUST_ORDERS的相应CREATE TABLE语句如下所示：

ops$tkyte@ORA10G> CREATE TABLE cust_orders 2 (       cust_id number, 3          order_dt timestamp SORT, 4          order_number number, 5          username varchar2(30), 6          ship_addr number, 7          bill_addr number, 8          invoice_num number 9 ) 10 CLUSTER shc ( cust_id, order_dt ) 11 / Table created.

我们就这个表的CUST_ID列映射到有序散列聚簇的散列键，并把ORDER_DT列映射到SORT列。使用SQL*Plus中的AUTOTRACE，可以观察到，访问有序散列聚簇时，原本以为有的正常排序操作不见了：

ops$tkyte@ORA10G> set autotrace traceonly explain ops$tkyte@ORA10G> variable x number ops$tkyte@ORA10G> select cust_id, order_dt, order_number 2          from cust_orders 3          where cust_id = :x 4          order by order_dt;   Execution Plan ---------------------------------------------------------- 0               SELECT STATEMENT Optimizer=ALL_ROWS (Cost=0 Card=4 Bytes=76) 1      0               TABLE ACCESS (HASH) OF 'CUST_ORDERS' (CLUSTER (HASH))   ops$tkyte@ORA10G> select job, hiredate, empno 2          from scott.emp 3          where job = 'CLERK' 4          order by hiredate;   Execution Plan ---------------------------------------------------------- 0               SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=3 Bytes=60) 1      0               SORT (ORDER BY) (Cost=3 Card=3 Bytes=60) 2      1                         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=3 ... 3      2                                  INDEX (RANGE SCAN) OF 'JOB_IDX' (INDEX) (Cost=1 Card=3)   ops$tkyte@ORA10G> set autotrace off

我对平常的SCOTT.EMP表做了一个查询（为了便于说明，这里现在JOB列上加了索引），这样可以看到我们预想的情况，以便做个比较：在上面的例子中，后面展示的是SCOTT.EMP查询计划，前面则展示了希望以FIFO模式（像队列一样）访问数据时有序散列聚簇会为我们做什么。可以看到，有序散列聚簇只完成了一步：它取得CUST_ID = :X，对输入执行散列，找到第一行，如何开始读取这一行，因为有序散列聚簇中行已经是有序的。常规表则有很大不同：它会查找所有JOB=‘CLERK’行（可能分布在堆表中的各个地方），对它们进行排序，再返回第一行。

所以，有序散列聚簇具备散列聚簇在获取方面的所有特点，因为它能得到数据而不必遍历索引；另外有序散列聚簇还拥有IOT的许多特性，因为数据可以按你选择的每个字段（键）有序地存储。当输入数据按排序字段（键）的顺序到达时，这种数据结构就能很好地工作。也就是说，在一段时间内，数据按某个给定键值的递增有序顺序到达。股票信息就满足这个要求。每天晚上你会得到一个新文件，其中填满了股票代码、日期以及相关的信息（日期是排序键，股票代码是散列键）。你按排序键顺序地接收好加载这些数据。对于股票代码ORCL，昨天的股票数据不会在今天之后才到达，你会先加载昨天的值，然后才是今天的值，之后是明天的值。如果信息随机地到达（不按有序的顺序到来），插入过程中，这个数据结构很快就会受不了，因为必须移动大量的数据使得这些行在磁盘上物理有序。在这种情况下，不建议采用有序散列聚簇（此时采用IOT可能很合适）。

使用这个结构时，应当考虑到散列聚簇同样的问题，另外还要考虑到一个约束条件，即数据应该按键值的有序顺序到达。

10.8   嵌套表

嵌套表（nested table）是Oracle对象关系扩展的一部分。嵌套表是Oracle中的两种集合类型之一，它与关系模型中传统的“父/子表对”里的子表很相似。这是数据元素的一个无序集，所有数据元素的数据类型都相同，可以是一个内置数据类型，也可以是一个对象数据类型。不过，还不仅如此，因为设计嵌套表是为了制造一个假象，好像父表中的每一行都有其自己的子表。如果父表中有100行，那么就有100个虚拟的嵌套表。但实际来讲，物理上只有一个父表和一个子表。在嵌套表和父/子表之间还存在一些显著的语法和语义差别，这一节就会详细介绍这些内容。

使用嵌套表有两种方法。一种方法是在PL/SQL代码中使用，用来扩展PL/SQL语言。另一种方法作为一种物理存储机制，持久地存储集合。我个人总是在PL/SQL中使用嵌套表，而从未将嵌套表用作持久存储机制。

在这一节中，我将简要地介绍创建、查询和修改嵌套表的语法。然后介绍一些实现细节，并说明关于Oracle如何存储嵌套表有哪些要点需要知道。

10.8.1             嵌套表语法

要创建一个有嵌套表的表相当简单，只是管理这个表的语法有点复杂。下面使用简单的EMP和DEPT表来说明。我们对用关系模式实现的这个小数据模型已经很熟悉了，如下：

ops$tkyte@ORA10GR1> create table dept 2          (deptno number(2) primary key, 3          dname varchar2(14), 4          loc varchar2(13) 5          ); Table created.   ops$tkyte@ORA10GR1> create table emp 2          (empno number(4) primary key, 3          ename varchar2(10), 4          job varchar2(9), 5          mgr number(4) references emp, 6          hiredate date, 7          sal number(7, 2), 8          comm number(7, 2), 9          deptno number(2) references dept 10       ); Table created.

这里有主键和外键。下面建立与之对应的等价实现，不过这里EMP表实现为嵌套表：

ops$tkyte@ORA10GR1> create or replace type emp_type 2     as object 3    (empno number(4), 4    ename varchar2(10), 5    job varchar2(9), 6    mgr number(4), 7    hiredate date, 8    sal number(7, 2), 9    comm number(7, 2) 10 ); 11 / Type created.   ops$tkyte@ORA10GR1> create or replace type emp_tab_type 2 as table of emp_type 3 / Type created.

要创建一个带嵌套表的表，需要有一个嵌套表类型。以上代码创建了一个复杂的对象类型EMP_TYPE，并创建了它的一个嵌套表类型EMP_TAB_TYPE。在PL/SQL中，会像处理数组一样处理这种类型。在SQL中，这会导致创建一个物理的嵌套表。以下简单的CREATE TABLE语句使用了这个类型：

ops$tkyte@ORA10G> create table dept_and_emp 2    (deptno number(2) primary key, 3    dname varchar2(14), 4    loc varchar2(13), 5    emps emp_tab_type 6 ) 7 nested table emps store as emps_nt; Table created. ops$tkyte@ORA10G> alter table emps_nt add constraint 2    emps_empno_unique unique(empno) 3 / Table altered.

这个CREATE TABLE语句中重要的是：其中包括列EMPS（类型为EMP_TAB_TYPE），还有相应的NESTED TABLE EMPS STORE AS EMPS_NT。这样除了表DEPT_AND_EMP之外，还会创建一个真正的物理表EMPS_NT，这个表与DEPT_AND_EMP是分开的。我们的嵌套表的EMPNO列上直接加了一个约束，使EMPNO像在原来的关系模型中一样是惟一的。利用嵌套表无法实现前面完整的数据模型：关系模型中存在自引用约束，倘若在嵌套表上增加同样的约束，则有：

ops$tkyte@ORA10G> alter table emps_nt add constraint mgr_fk 2 foreign key(mgr) references emps_nt(empno); alter table emps_nt add constraint mgr_fk * ERROR at line 1: ORA-30730: referential constraint not allowed on nested table column

这是不行的。嵌套表不支持引用完整性约束，因为它们不能引用任何表，甚至它们自己。因此，现在先不管它。接下来，用现有的EMP和DEPT数据来填充这个表：

ops$tkyte@ORA10G> insert into dept_and_emp 2    select dept.*, 3          CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm 4                   from SCOTT.EMP 5                   where emp.deptno = dept.deptno ) AS emp_tab_type ) 6    from SCOTT.DEPT 7 / 4 rows created.

这里有两点要注意：

q         只创建了“4”行。DEPT_AND_EMP表中确实只有4行。没有独立地存在14个EMP行。

q         这个语法开始有点奇怪了。CAST和MULTISET是大多数人从来没有用过的语法。处理数据库中的对象关系组件时，你会看到很多奇怪的语法。MULTISET关键字用于告诉Oracle：这个子查询返回多行（SELECT列表中的子查询先前限制为只返回一行）。CAST用于指示Oracle：要把返回的结果集处理为一个集合类型，在这里，我们将MULTISET强制转换（CAST）为一个EMP_TAB_TYPE。CAST是一个通用的例程， 并不仅限于在集合中使用。例如，如果想从EMP中将EMPNO列获取为VARCHAR2(20)而不是NUMBER(4)类型，可以使用以下查询：SELECT CAST(EMPNO AS VARCHAR2(20)) E FROM EMP。

现在可以查询数据了。下面来看一行是什么样子：

ops$tkyte@ORA10G> select deptno, dname, loc, d.emps AS employees 2 from dept_and_emp d 3 where deptno = 10 4 /   DEPTNO    DNAME                LOC                EMPLOYEES(EMPNO, ENAME, JOB, ----------       --------------            ----------------  --------------------------------------------------- 10                ACCOUNTING    NEW YORK EMP_TAB_TYPE(EMP_TYPE(7782,                                                                                'CLARK', 'MANAGER', 7839, '0                                                                                9-JUN-81', 2450, NULL), EMP_                                                                                TYPE(7839, 'KING', 'PRESIDEN                                                                                T', NULL, '17-NOV-81', 5000,                                                                                NULL), EMP_TYPE(7934, 'MILL                                                                                ER', 'CLERK', 7782, '23-JAN-                                                                                82', 1300, NULL))

现有数据都在这里，都放在一个利中。大多数应用都不能处理这个特殊的列，除非是专门针对对象关系特性编写的。例如，ODBC没有办法处理嵌套表（JDBC、OCI、Pro*C、PL/SQL和大多数其他API和语言则可以）。针对这些情况，Oracle提供了一种方法，可以取消集合的嵌套，把它当成一个关系表来处理：

ops$tkyte@ORA10G> select d.deptno, d.dname, emp.* 2 from dept_and_emp D, table(d.emps) emp 3 /   DEPTNO       DNAME           EMPNO  ENAME  JOB                 MGR  HIREDATE        SAL     COMM ------------         --------------------  ------------  ----------     ----------------          -----  ---------------        --------     ----------       10           ACCOUNTING     7782  CLARK    MANAGER      7839  09-JUN-81       2450       10           ACCOUNTING     7839  KING       PRESIDENT                17-NOV-81      5000       10           ACCOUNTING     7934  MILLER  CLERK            7782  23-JAN-82       1300       20           RESEARCH         7369  SMITH     CLERK            7902  17-DEC-80        800       20           RESEARCH         7566  JONES    MANAGER      7839  02-APR-81      2975       20           RESEARCH         7788  SCOTT    ANALYST        7566  09-DEC-82      3000       20           RESEARCH         7876  ADAMS  CLERK            7788  12-JAN-83       1100       20           RESEARCH         7902  FORD      ANALYST        7566  03-DEC-81      3000       30           SALES                7499  ALLEN    SALESMAN     7698  20-FEB-81       1600         300       30           SALES                7521  WARD     SALESMAN     7698  22-FEB-81       1250         500       30           SALES                7654  MARTIN  SALESMAN     7698  28-SEP-81      1250       1400       30           SALES                7698  BLAKE    MANAGER      7839  01-MAY-81      2850       30           SALES                7844  TURNER SALESMAN     7698  08-SEP-81      1500             0       30           SALES                7900  JAMES    CLERK            7698  03-DEC-81        950 14 rows selected.

可以把EMPS列强制转换为一个表，它会自然地为我们完成联结，这里不需要联结条件。实际上，由于我们的EMP类型根本没有DEPTNO列，所以无法明确地在哪个列上完成联结。这些杂事都由Oracle为我们做。

那么，怎么更新数据呢？假设我们想给部门10发$100的奖金。可以如下编写代码：

ops$tkyte@ORA10G> update 2    table( select emps 3    from dept_and_emp 4    where deptno = 10 5    ) 6    set comm = 100 7 / 3 rows updated.

前面说过“每一行都有一个虚拟的表”，这句话在这里就得到了应验。在前面所示的SELECT谓词中，每行有一个表可能还不太明显：特别是前面没有联结之类的工作，所有看上去有点“神奇”。不过，UPDATE语句能很清楚地显示出每行有一个表。我们选择一个具体的表来更新（UPDATE）——这个表没有名字，只是用一个查询来标识。如果使用的这个查询不是刚好选择（SELECT）一个表，我们就会得到以下错误：

ops$tkyte@ORA10G> update 2    table( select emps 3                   from dept_and_emp 4                   where deptno = 1 5    ) 6 set comm = 100 7 / update * ERROR at line 1: ORA-22908: reference to NULL table value   ops$tkyte@ORA10G> update 2    table( select emps 3           from dept_and_emp 4           where deptno > 1 5    ) 6    set comm = 100 7 / table( select emps * ERROR at line 2: ORA-01427: single-row subquery returns more than one row

如果返回至少一行（一个嵌套表实例也没有），更新就会失败。正常情况下，更新0行是可以的，但在这里不行，它会返回一个错误，就好像我们的更新中漏写了表名一样。如果返回了多行（不止一个嵌套表实例），更新也会失败。正常情况下，更新多行是完全可以的。但是这里显示出，Oracle认为DEPT_AND_EMP表中的每一行指向另一个表，而不是像关系模型中那样指定另外一个行集。

这就是嵌套表和父/子关系表之间的语义差别。在嵌套表模型中，每个父行有一个表。而关系模型中，每个父行有一个行集。这种差别使用嵌套表有时使用起来有些麻烦。考虑我们使用的这个模型，它从单个部门的角度提供了一个很好的数据视图。如果我们想问“KIND为哪个部门工作？”“有多少在职的会计”等待，这个模型就很糟糕了。这些问题最好去问EMP关系表，而在嵌套表模型中，我们只能通过DEPT数据来访问EMP数据。总是必须联结，而无法单独地查询EMP数据。在这方面，Oracle没有提供公开支持的方法（也没有相关的文档说明），但是我们可以使用一个技巧来完成（关于这个技巧，稍后还会更多地介绍）。如果需要更新EMPS_NT中的每一行，我们必须完成4个更新：分别更新DEPT_AND_EMP中的各行，从而更新与之关联的虚拟表。

更新部门10的员工数据时，还有考虑一个问题，我们是在语义上更新DEPT_AND_EMP表中的EMPS列。要知道，尽管在物理上涉及两个表，但是语义上只有一个表。即使我们没有更新部门表中的数据，包含所修改嵌套表的行也会被锁定，不允许其他会话更新。传统的父/子表关系中则不是这样。

正是由于这些原因，我不主张把嵌套表用作一种持久存储机制。作为子表，如果不用单独查询，这种情况实在是少见。在前面的例子中，EMP表应该是一个强实体。它是独立的，所以需要单独查询。我发现一般都是这种情况，所以打算通过关系表上的视图来使用嵌套表。

既然我们了解了如何更新一个嵌套表实例，插入和删除就相当容易了。下面向嵌套表实例部门10增加一行，再从部门20删除一行：

ops$tkyte@ORA10G> insert into table 2    ( select emps from dept_and_emp where deptno = 10 ) 3    values 4    ( 1234, 'NewEmp', 'CLERK', 7782, sysdate, 1200, null ); 1 row created.   ops$tkyte@ORA10G> delete from table 2    ( select emps from dept_and_emp where deptno = 20 ) 3    where ename = 'SCOTT'; 1 row deleted.   ops$tkyte@ORA10G> select d.dname, e.empno, ename 2    from dept_and_emp d, table(d.emps) e 3    where d.deptno in ( 10, 20 );   DNAME               EMPNO       ENAME --------------             ----------       ---------- ACCOUNTING        7782       CLARK ACCOUNTING        7839       KING ACCOUNTING        7934       MILLER RESEARCH             7369       SMITH RESEARCH             7566       JONES RESEARCH             7876       ADAMS RESEARCH             7902       FORD ACCOUNTING        1234       NewEmp 8 rows selected.

这就是查询和修改嵌套表的基本语法。你往往会发现，必须像我们刚才那样取消这些表的嵌套（特别是在查询中），才能使用这些嵌套表。一旦从概念上了解了“每行一个虚拟表”的概念，使用嵌套表就会容易得多。

前面我说过，“总是必须联结；而无法单独地查询EMP数据”，但是然后我又告诫说“如果你确实需要，（利用一个技巧）这也是能办到的”。这种方法没有得到公开支持，也没有相关的文档说明，所以只能把它作为最后一个杀手锏。如果你确实需要大批量地更新嵌套表（记住，要利用联结通过DEPT表来做到），此时这种方法才能最好地发挥作用。Oracle中有一个无文档说明的提示（只是简单地提了一下，而没有充分地说明）：NESTED_TABLE_GET_REFS，许多工具都使用了这个提示，如EXP和IMP就利用了这个提示来处理嵌套表。利用这种方法还可以查看嵌套表物理结构的更多信息。使用这个提示，可以完成查询来得到一些“神奇”的结果。EXP（一个数据卸载工具）从嵌套表中抽取数据时就使用了以下查询：

ops$tkyte@ORA10G> SELECT /*+NESTED_TABLE_GET_REFS*/ 2 NESTED_TABLE_ID,SYS_NC_ROWINFO$ 3 FROM "OPS$TKYTE"."EMPS_NT" 4 /   NESTED_TABLE_ID                                           SYS_NC_ROWINFO$(EMPNO, EN --------------------------------                                      ------------------------- F60DEEE0FF7D7BC1E030007F01001321 EMP_TYPE(7782, 'CLARK', '                                                                                   MANAGER', 7839, '09-JUN-8                                                                                   1', 2450, 100) F60DEEE0FF7D7BC1E030007F01001321 EMP_TYPE(7839, 'KING', 'P                                                                                   RESIDENT', NULL, '17-NOV-                                                                                   81', 5000, 100) ...

但是，如果你描述这个嵌套表，会有点奇怪：

ops$tkyte@ORA10G> desc emps_nt Name                                  Null?                     Type -----------------------------      --------                    -------------------- EMPNO                                                              NUMBER(4) ENAME                                                              VARCHAR2(10) JOB                                                                     VARCHAR2(9) MGR                                                                   NUMBER(4) HIREDATE                                                        DATE SAL                                                                     NUMBER(7,2) COMM                                                                NUMBER(7,2)

前面查询的两列居然没有出现。它们是嵌套表隐藏实现的一部分。NESTED_TABLE_ID实际上是父表DEPT_AND_EMP的一个外键。DEPT_AND_EMP中确实有一个隐藏列，用于联结至EMPS_NT。SYS_NC_ROWINFO$“列”是一个神奇的列；它更应该算是一个函数而不是一个列。这里的嵌套表实际上是一个对象表（由一个对象类型组成），而SYS_NC_ROWINFO$正是Oracle将行引用为对象所采用的内部方法，而并非引用行的各个标量列。在底层，Oracle会用系统生成的主键和外键实现一个父/子表。如果更进一步挖掘，可以查询“真正”的数据字典来查看DEPT_AND_EMP表中的所有列：

sys@ORA10G> select name 2    from sys.col$ 3    where obj# = ( select object_id 4           from dba_objects 5           where object_name = 'DEPT_AND_EMP' 6                   and owner = 'OPS$TKYTE' ) 7 /   NAME ------------------------------ DEPTNO DNAME EMPS LOC SYS_NC0000400005$

从嵌套表中选出这一列，我们会看到下面的结果：

ops$tkyte@ORA10G> select SYS_NC0000400005$ from dept_and_emp;   SYS_NC0000400005$ -------------------------------- F60DEEE0FF887BC1E030007F01001321 F60DEEE0FF897BC1E030007F01001321 F60DEEE0FF8A7BC1E030007F01001321 F60DEEE0FF8B7BC1E030007F01001321

这个列名看上去很古怪（SYS_NC0000400005$），这是放在DEPT_AND_EMP表中的系统生成的键。如果更深层次地挖掘，会发现Oracle已经在这个列上放上了惟一一个索引。不过，遗憾的是，它没有对EMP_NT中的NESTED_TABLE_ID加索引。这个列确实需要加索引，因为我们总是要从DEPT_AND_EMP联结到EMPS_NT。如果像刚才那样使用默认值，必须记住关于嵌套表的一个要点：一定要对嵌套表中的NESTED_TABLE_ID加索引!

不过现在我有些离题了，我要说的是如何把嵌套表当成一个真正的表来进行处理。NESTED_TABLE_GET_REFS提示就为我们做了这个工作。可以使用如下的提示：

ops$tkyte@ORA10G> select /*+ nested_table_get_refs */ empno, ename 2 from emps_nt where ename like '%A%';   EMPNO  ENAME ----------    ---------- 7782        CLARK 7876        ADAMS 7499        ALLEN 7521        WARD 7654        MARTIN 7698        BLAKE 7900        JAMES 7 rows selected.   ops$tkyte@ORA10G> update /*+ nested_table_get_refs */ emps_nt 2 set ename = initcap(ename); 14 rows updated.   ops$tkyte@ORA10G> select /*+ nested_table_get_refs */ empno, ename 2 from emps_nt where ename like '%a%';   EMPNO  ENAME ----------    ---------- 7782        Clark 7876        Adams 7521        Ward 7654        Martin 7698        Blake 7900        James 6 rows selected.

再次重申，这个特性没有相关的文档说明，没有得到公开支持。它是一个保障EXP和IMP工作的特定功能，而且只有在EXP和IMP环境中才能保证这种方法一定可行。你自己使用时会有风险，而且千万不要用在生成代码中。实际上，如果你发现确实需要使用这个特性，那么从定义来讲，这说明根本就不应该使用嵌套表！这个结构对你完全不适合。可以用这种方法对数据完成一次性修正，或者你对嵌套表很好奇，可以通过这个技巧来看看嵌套表里有什么。要报告嵌套表中的数据，公开支持的方法是消除嵌套，如下：

ops$tkyte@ORA10G> select d.deptno, d.dname, emp.* 2 from dept_and_emp D, table(d.emps) emp 3 /

查询和生产代码中应该使用这种方法。

10.8.2             嵌套表存储

我们已经了解了嵌套表结构的一些存储问题。在这一节中，我们将输入地分析Oracle默认创建的结构，并说明我们对这种结构能有怎样的控制。还是用前面的CREATE语句：

ops$tkyte@ORA10G> create table dept_and_emp 2    (deptno number(2) primary key, 3    dname varchar2(14), 4    loc varchar2(13), 5    emps emp_tab_type 6    ) 7    nested table emps store as emps_nt; Table created.   ops$tkyte@ORA10G> alter table emps_nt add constraint emps_empno_unique 2 unique(empno) 3 / Table altered.

我们知道Oracle实际上会创建一个图10-11所示的结构。

图10-11   嵌套表的物理实现

这个代码创建了两个实际的表。这里确实会创建我们请求创建的表，但是它有额外的一个隐藏列（默认情况下，对于表中的每一个嵌套表列，都会有一个额外的隐藏列）。它还在这个隐藏列上创建了一个惟一约束。Oracle为我们创建了嵌套表EMPS_NT。这个表有两个隐藏列，其中的SYS_NC_ROWINFO$列并不真正算是一个列，而应该是一个虚拟列，它会把所有标量元素返回为一个对象。另一个隐藏列是名为NESTED_TABLE_ID的外键，通过这个外键可以联结回父表。注意这个列上没有索引。最后，Oracle在DEPT_AND_EMP表的DEPTNO列上增加了一个索引，以保证主键。所以，我们本来只请求创建一个表，得到的却远不止一个表。如果查看这个表，与创建父/子关系时所看到的情况非常相似，但是你要使用DEPTNO上的现有主键作为EMPS_NT的外键，而不是生成一个代理键RAW(16)。

如果查看嵌套表示例的DBMS_METADATA.GET_DDL转储信息，可以看到以下内容：

ops$tkyte@ORA10G> begin 2           dbms_metadata.set_transform_param 3           ( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false ); 4    end; 5    / PL/SQL procedure successfully completed.   ops$tkyte@ORA10G> select dbms_metadata.get_ddl( 'TABLE', 'DEPT_AND_EMP' ) from dual;   DBMS_METADATA.GET_DDL('TABLE','DEPT_AND_EMP') ------------------------------------------------------------------------------- CREATE TABLE "OPS$TKYTE"."DEPT_AND_EMP" (        "DEPTNO" NUMBER(2,0),          "DNAME" VARCHAR2(14),          "LOC" VARCHAR2(13),          "EMPS" "OPS$TKYTE"."EMP_TAB_TYPE" ,          PRIMARY KEY ("DEPTNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE "USERS" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" NESTED TABLE "EMPS" STORE AS "EMPS_NT" (PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "USERS" ) RETURN AS VALUE

到此为止，只有一个新内容，即RETURN AS VALUE。这个选项用于描述如何向客户应用返回嵌套表。默认情况下，Oracle会按值把嵌套表返回给客户：具体数据会随各行传输。这个选项也可以设置为RETURN AS LOCATOR，这说明客户会得到指向数据的一个指针，而不是数据本身。当且仅当客户对这个指针解除引用（dereference）时，才会把数据传输给客户。因此，如果你相信客户通常不会查看对应各个父行的嵌套表（不会查看这些嵌套表中的行），就可以返回一个locator而不是值，这样可以节省网络往返通信开销。例如，如果你的客户应用要显示部门列表，当用户双击一个部门时，客户应用会显示出员工信息，你就可以考虑使用locator。这是因为通常都不会查看详细信息，查看详细信息的情况是例外，而不是一般情况。

那么，我们还能对嵌套表做些什么呢？首先，NESTED_TABLE_ID列必须建立索引。因为我们总是从父表联结到子表来访问嵌套表，我们确实需要这个索引。可以使用CREATE INDEX对该列建立索引，但是最好的解决方案是使用一个IOT来存储嵌套表。嵌套表也是一个适用IOT的绝好例子。它会按NESTED_TABLE_ID将子行物理地共同存储在一块（所以用最少的物理I/O就能完成表的获取），这样就不必在RAW(16)列上建立冗余的索引。再前进一步，由于NESTED_TABLE_ID就是IOT主键的第一列，还应该加入索引键压缩来避免冗余的NESTED_TABLE_ID（否则会重复存储）。另外，我们还可以在CREATE TABLE命令中加入EMPNO列的UNIQUE和NOT NULL约束。因此，对于前面的CREATE TABLE，可以稍作修改，如下所示：

ops$tkyte@ORA10G> CREATE TABLE "OPS$TKYTE"."DEPT_AND_EMP" 2    ("DEPTNO" NUMBER(2, 0), 3    "DNAME" VARCHAR2(14), 4    "LOC" VARCHAR2(13), 5    "EMPS" "EMP_TAB_TYPE") 6    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING 7    STORAGE(INITIAL 131072 NEXT 131072 8           MINEXTENTS 1 MAXEXTENTS 4096 9           PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 10         BUFFER_POOL DEFAULT) 11 TABLESPACE "USERS" 12 NESTED TABLE "EMPS" 13         STORE AS "EMPS_NT" 14         ( (empno NOT NULL, unique (empno), primary key(nested_table_id,empno)) 15                 organization index compress 1 ) 16         RETURN AS VALUE 17 / Table created.

现在可以得到以下的一组对象。这里没有一个传统表EMP_NT，而是一个IOT EMPS_NT，由图10-12中表上的索引结构指示。

图10-12 嵌套表实现为IOT

如果EMPS_NT是一个使用压缩的IOT，它比原来默认嵌套表占用的存储空间更少，而且其中有我们非常需要的索引。

10.8.3             嵌套表小结

我自己并不把嵌套表用作一种持久存储机制，原因如下：

q         这样会增加不必要的RAW(16)列存储开销。父表和子表都有这个额外的列。父表对于其中的各个嵌套表列都有一个额外的16字节RAW字段。由于父表通常已经有一个主键（在我们这个例子中就是DEPTNO），所以完全可以在子表中使用这个键，而不必使用一个系统生成的键。

q         这会在父表上增加另一个惟一约束（相应地带领不必要的开销），而父表中通常已经有一个惟一约束。

q         如果不使用NESTED_TABLE_GET_REFS（这个方法未得到公开支持），嵌套表本身使用起来并不2.如果是查询，可以通过消除嵌套来访问嵌套表，但是如果是大批量更新，则无法简单地消除嵌套。在实际中，表往往都会“自己”查询自己，我还没有见过这方面的例外。

不过作为一个编程构造，我确实大量使用了嵌套表，并把嵌套表用于视图中。我认为这才是嵌套表应有的位置。作为一个存储机制，我更倾向于我自己创建父/子表。创建了父/子表之后，实际上，我们可以再创建一个视图，使之看上去就好像我们有一个真正的嵌套表一样。也就是说，这样做可以得到嵌套表构造的所有好处，而不会引入嵌套表的开销。

如果你确实把嵌套表用作一个存储机制，一定要保证将嵌套表建立为一个IOT，以避免NESTED_TABLE_ID上索引的开销以及嵌套表本身的开销。请参考前面有关IOT的一节，了解基于溢出段和其他选项来建立IOT时有哪些建议。如果你没有使用IOT，则要确保在嵌套表的NESTED_TABLE_ID列上创建一个索引，来避免为查找子行而执行全表扫描。

10.9   临时表

临时表（Temporary table）用于保存事务或会话期间的中间结果集。临时表中保存的数据只对当前会话可见，所有会话都看不到其他会话的数据；即使当前会话已经提交（COMMIT）了数据，别的会话也看不到它的数据。对于临时表，不存在多用户并发问题，因为一个会话不会因为使用一个临时表而阻塞另一个会话。即使我们“锁住”了临时表，也不会妨碍其他会话使用它们自己的临时表。我们在第9章了解到，临时表比常规表生成的redo少得多。不过，由于临时表必须为其中包含的数据生成undo信息，所以也会生成一定的redo。UPDATE和DELETE会生成最多的undo；INSERT和SELECT生成的undo最少。

临时表会从当前登录用户的临时表空间分配存储空间，或者如果从一个定义者权限（definer right）过程访问临时表，就会使用该过程所有者的临时表空间。全局临时表实际上是表本身的一个模板。创建临时表的动作不涉及存储空间分配；不会为此分配初始（INITIAL）区段，这与常规表有所不同。对于临时表，运行时当一个会话第一次在临时表中放入数据时，才会为该会话创建一个临时段。由于每个会话会得到其自己的临时段（而不是一个现有段的一个区段），每个用户可能在不同的表空间为其临时表分配空间。USER1的临时表空间可能设置为TEMP1，因此他的临时表会从这个表空间分配。USER2可能把TEMP2作为其临时表空间，他的临时表就会从那里分配。

Oracle 的临时表与其他关系数据库中的临时表类似，这样区别只是：Oracle的临时表是“静态”定义的。每个数据库只创建一次临时表，而不是为数据库中的每个存储过程都创建一次。在Oracle中，临时表一定存在，它们作为对象放在数据字典中，但是在会话向临时表中放入数据之前，临时表看上去总是空。由于临时表是静态定义的，所以你能创建引用临时表的视图，还可以创建存储过程使用静态SQL来引用临时表，等等。临时表可以是基于会话的（临时表中的数据可以跨提交存在，即提交之前仍然存在，但是断开连接后再连接后再连接时数据就没有了），也可以是基于事务的（提交之后数据就消失）。下面这个例子显示了这两种不同的临时表。我使用SCOTT.EMP表作为一个模板：

ops$tkyte@ORA10G> create global temporary table temp_table_session 2    on commit preserve rows 3    as 4    select * from scott.emp where 1=0 5    / Table created.

ON COMMIT PRESERVE ROWS 子句使得这是一个基于会话的临时表。在我的会话断开连接之前，或者我通过一个DELETE或TRUNCATE物理地删除行之前，这些行会一直存在于这个临时表中。只有我的会话能看到这些行；即使我已经提交（COMMIT），其他会话也无法看到“我的”行。

ops$tkyte@ORA10G> create global temporary table temp_table_transaction 2    on commit delete rows 3    as 4    select * from scott.emp where 1=0 5    / Table created.

ON COMMIT DELETE ROWS 子句使得这是一个基于事务的临时表。我的会话提交时，临时表中的行就不见了。只需把分配给这个表的临时区段交回，这些行就会消失，在临时表的自动清除过程中不存在开销。

下面来看这两种类型之间的区别：

ops$tkyte@ORA10G> insert into temp_table_session select * from scott.emp; 14 rows created.   ops$tkyte@ORA10G> insert into temp_table_transaction select * from scott.emp; 14 rows created.

我们在各个TEMP表中方便放了14行，以下显示出我们可以“看到”这些行：

ops$tkyte@ORA10G> select session_cnt, transaction_cnt 2    from ( select count(*) session_cnt from temp_table_session ), 3           ( select count(*) transaction_cnt from temp_table_transaction );        SESSION_CNT    TRANSACTION_CNT     ----------------------    -----------------------------                              14                                      14 ops$tkyte@ORA10G> commit;

由于我们已经提交，所以仍可以看到基于会话的临时表行，但是看不到基于事务的临时表行：

ops$tkyte@ORA10G> select session_cnt, transaction_cnt 2    from ( select count(*) session_cnt from temp_table_session ), 3           ( select count(*) transaction_cnt from temp_table_transaction );        SESSION_CNT    TRANSACTION_CNT                    -----------                       ---------------                              14                                         0   ops$tkyte@ORA10G> ops$tkyte@ORA10G> disconnect Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 With the Partitioning, OLAP and Data Mining options ops$tkyte@ORA10G> connect / Connected.

由于此时已经开始了一个新的会话，所以两个表中的行都看不到了：

ops$tkyte@ORA10G> select session_cnt, transaction_cnt 2    from ( select count(*) session_cnt from temp_table_session ), 3           ( select count(*) transaction_cnt from temp_table_transaction );        SESSION_CNT    TRANSACTION_CNT                    -----------                       ---------------                                0                                         0

如果你曾在SQL Server和/或Sybase中用过临时表，现在所要考虑的主要问题是：不应该执行SELECT X, Y, Z INTO #TEMP FROM SOME_TABLE来动态创建和填充一个临时表，而应该：

q         将所有全局临时表只创建一次，作为应用安装的一部分，就像是创建永久表一样。

q         在你的过程中，只需执行INSERT INTO TEMP(X, Y, Z) SELECT X, Y, Z FROM SOME_TABLE。

归根结底，这里的目标是：不要在运行时在你的存储过程中创建表。这不是Oracle中使用临时表的正确做法。DDL是一种代价昂贵的操作：你要全力避免在运行时执行这种操作。一个应用的临时表应该在应用安装期间创建，绝对不要在运行时创建。

临时表可以有永久表的许多属性。它们可以有触发器、检查约束、索引等。但永久表的某些特性在临时表中并不支持，这包括：

q         不能有引用完整性约束。临时表不能作为外键的目标，也不能在临时表中定义外键。

q         不能有NESTED TABLE类型的列。在Oracle 9i及以前版本中，VARRAY类型的列也不允许；不过Oracle 10g中去掉了这个限制。

q         不能是IOT。

q         不能在任何类型的聚簇中。

q         不能分区。

q         不能通过ANALYZE表命令生成统计信息。

在所有数据库中，临时表的缺点之一是优化器不能正常地得到临时表的真实统计。使用基于代价的优化器（cost-based optimizer，CBO）时，有效的统计对于优化器的成败至关重要。如果没有统计信息，优化器就只能对数据的分布、数据量以及索引的选择性作出猜测。如果这些猜测是错的，为查询生成的查询计划（大量使用临时表）可能就不是最优的。在许多情况下，正确的解决方案是根本不使用临时表，而是使用一个INLINE VIEW（要看INLINE VIEW的例子，可以查看前面运行的SELECT，它就有两个内联视图）。采用这种方式，Oracle可以访问一个表的所有相关统计信息，而且得出一个最优计划。

我经常发现，人们之所以使用临时表，是因为他们在其他数据库中了解到一个查询中联结太多的表是一件“不好的事情”。但在Oracle开发中，必须把这个知识忘掉。不要想着你比优化器要聪明，把本来一个查询分解成3个或4个查询，将其子结果存储在临时表中，然后再合并这些临时表；正确的做法是应该编写一个查询，直接回答最初的问题。在一个查询中引用多个表是可以的；Oracle中在这个方面不需要临时表的帮助。

不过在其他情况下，可以在进程中使用临时表，这是一种正确的做法。例如，我曾经编写过一个PALM同步应用程序，将Palm Pilot上的日期簿与Oracle中存储的日历信息同步。Palm会为我提供自最后一次热同步以来修改的所有记录的列表，我必须取得这些记录，把它们与数据库中的当前数据相比较，更新数据库记录，然后生成一个修改列表，应用到Palm。这是一个展示临时表用处的绝好例子。我使用一个临时表在数据库中存储Palm上所做的修改。然后运行一个存储过程，它将Palm生成的修改与当前的永久表（非常大）相比较，发现需要对Oracle数据做哪些修改，然后找出Oracle数据库中的哪些修改需要再应用到Palm上。我必须对这个数据做两趟处理。首先，要发现仅在Palm上修改的记录，并在Oracle中做相应的修改。接下来，要发现自最后一次同步和修改以来Palm和数据库中都经过修改的所有记录。如何发现仅在数据库中经过修改的所有记录。并将其修改放在临时表中。最后，Palm同步应用程序从临时表拉出这些修改，把它们应用于Palm设备本身，断开连接时，临时表会消失。

不过，我遇到的问题是，由于会分析永久表，所以使用了CBO。但是临时表上没有统计信息（尽管可以分析临时表，但不会收集统计信息），因此CBO会对它做出很多“猜测”。作为一名开发人员，我知道可能的平均行数、数据的分布、查询选择的列等。我需要一种方法来告诉优化器这些更准确的猜测。可以有3中种方法向优化器提供关于全局临时表的统计信息。一种方法是通过动态采样（只是Oracle9i Release 2及以上版本中新增的特性），另一种方法是使用DBMS_STATS包，它有两种做法。下面首先来看动态采样。

动态采样（dynamic sampling）是优化器的一种功能，硬解析一个查询时，会扫描数据库中的段（采样），收集有用的统计信息，来完成这个特定查询的优化。这与硬解析期间完成一个“缩型收集统计”命令很类似。Oracle 10g中大量使用了动态采样，因为默认设置已经从level 1提升到level 2，采用level 2，优化器在结算查询计划之前，会对优化器处理的查询中引用的所有未分析的对象完成动态采样。9i Release2中则设置为level 1，所以动态采样的使用少得多。在Oracle9i Release 2中可以使用一个ALTER SESSION|SYSTEM命令，从而能有Oracle 10g默认行为，或者可以使用动态采样提示，如下：

ops$tkyte@ORA9IR2> create global temporary table gtt 2    as 3    select * from scott.emp where 1=0; Table created.   ops$tkyte@ORA9IR2> insert into gtt select * from scott.emp; 14 rows created.   ops$tkyte@ORA9IR2> set autotrace traceonly explain ops$tkyte@ORA9IR2> select /*+ first_rows */ * from gtt;   Execution Plan ---------------------------------------------------------- 0                SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=8168 Bytes... 1       0                TABLE ACCESS (FULL) OF 'GTT' (Cost=17 Card=8168 Bytes=710616)   ops$tkyte@ORA9IR2> select /*+ first_rows dynamic_sampling(gtt 2) */ * from gtt;   Execution Plan ---------------------------------------------------------- 0                SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=17 Card=14 Bytes=1218) 1       0                TABLE ACCESS (FULL) OF 'GTT' (Cost=17 Card=14 Bytes=1218)   ops$tkyte@ORA9IR2> set autotrace off

在此，我们在这个查询中把表GTT的动态采样设置为level 2.在此之前，优化器猜测会从表GTT返回8,168行。通过使用动态采样，估计的基数会与实际更为接近（这会得到总体上更好的查询计划）。使用level 2设置，优化器会很快地扫描表，对表的真实大小得出更实际的估计。在Oracle 10g中，这应该不成问题，因为默认就会发生动态采样：

ops$tkyte@ORA10G> create global temporary table gtt 2 as 3 select * from scott.emp where 1=0; Table created.   ops$tkyte@ORA10G> insert into gtt select * from scott.emp; 14 rows created.   ops$tkyte@ORA10G> set autotrace traceonly explain ops$tkyte@ORA10G> select * from gtt;   Execution Plan ---------------------------------------------------------- 0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=14 Bytes=1218) 1 0           TABLE ACCESS (FULL) OF 'GTT' (TABLE (TEMP)) (Cost=2 Card=14 Bytes=1218) ops$tkyte@ORA10G> set autotrace off

在此不必要求，就能得到正确的基数。不过，动态采样不是免费的，由于必须在查询解析时完成，所以存在相当大的代价。如果能提前收集适当的代表性统计信息，就可以避免在硬解析时执行动态采样。为此可以使用DBMS_STATS。

使用DBMS_STATS收集代表性统计信息有3种方法。第一种方法是利用GATHER_SCHEMA_STATS或GATHER_DATABASE_STATS调用来使用DBMS_STATS。这些过程允许你传入一个参数GATHER_TEMP，这是一个布尔值，默认为FALSE。设置为TRUE时，所有ON COMMIT PRESERVE ROWS全局临时表都会收集和存储统计信息（这个技术在ON COMMIT DELETE ROWS表上不可行）。考虑以下情况（注意这在一个空模式中完成：除了你创建的对象之外没有其他对象）：

ops$tkyte@ORA10G> create table emp as select * from scott.emp; Table created.   ops$tkyte@ORA10G> create global temporary table gtt1 ( x number ) 2 on commit preserve rows; Table created.   ops$tkyte@ORA10G> create global temporary table gtt2 ( x number ) 2 on commit delete rows; Table created.   ops$tkyte@ORA10G> insert into gtt1 select user_id from all_users; 38 rows created.   ops$tkyte@ORA10G> insert into gtt2 select user_id from all_users; 38 rows created.   ops$tkyte@ORA10G> exec dbms_stats.gather_schema_stats( user ); PL/SQL procedure successfully completed.   ops$tkyte@ORA10G> select table_name, last_analyzed, num_rows from user_tables; TABLE_NAME                  LAST_ANAL    NUM_ROWS ------------------------------     ---------                          ---------- EMP                                    01-MAY-05                          14 GTT1 GTT2

可以看到，在这种情况下，只会分析EMP表：两个全局临时表将被忽略。可以如下调用GATHER_SCHEMA_STATS（带GATHER_TEMP => TRUE）来改变这种行为：

ops$tkyte@ORA10G> insert into gtt2 select user_id from all_users; 38 rows created.   ops$tkyte@ORA10G> exec dbms_stats.gather_schema_stats( user, gather_temp=>TRUE ); PL/SQL procedure successfully completed.   ops$tkyte@ORA10G> select table_name, last_analyzed, num_rows from user_tables; TABLE_NAME                  LAST_ANAL    NUM_ROWS ------------------------------     ---------                          ---------- EMP                                    01-MAY-05                          14 GTT1                                   01-MAY-05                          38 GTT2                                   01-MAY-05                            0

注意，ON COMMIT PRESERVE ROWS表会有正确的统计，但是ON COMMIT DELETE ROWS表没有。DBMS_STATS将提交，而这会擦除ON COMMIT DELETE ROWS表中的所有信息。不过，要注意，现在GTT2确实有统计信息了，这本身并不好，因为统计信息太离谱了！运行时表居然只有0行，这实在是让人怀疑。所以，如果使用这种方法，要注意两点：

q         要保证在收集统计信息的会话中用代表性数据填充全局临时表。如果做不到，在DBMS_STATS看来它们就是空的。

q         如果有ON COMMIT DELETE ROWS全局临时表，就不应该使用这种方法，因为这样会收集到不正确的值。

对于ON COMMIT PRESERVE ROWS全局临时表，还可以采用第二种技术：直接在表上使用GATHER_TABLE_STATS。你要像我们刚才那样填充全局临时表，然后在这个全局临时表上执行GATHER_TABLE_STATS。注意还是像前面一样，对于ON COMMIT DELETE ROWS全局临时表，这种技术还是不能用，同样是因为存在前面所述的问题。

使用DBMS_STATS的最后一种技术是通过一个手动过程用临时表的代表性统计信息填充数据字典。例如，如果平均来讲临时表中的行数是500，而且行的平均大小是100字节，块数为7，则只需如下使用DBMS_STATS：

ops$tkyte@ORA10G> create global temporary table t ( x int, y varchar2(100) ); Table created.   ops$tkyte@ORA10G> begin 2           dbms_stats.set_table_stats( ownname => USER, 3           tabname => 'T', 4           numrows => 500, 5           numblks => 7, 6           avgrlen => 100 ); 7 end; 8 / PL/SQL procedure successfully completed.   ops$tkyte@ORA10G> select table_name, num_rows, blocks, avg_row_len 2 from user_tables 3 where table_name = 'T'; TABLE_NAME                  NUM_ROWS       BLOCKS    AVG_ROW_LEN ------------------------------               ----------          ----------                    ----------- T                                                           500                     7                            100

现在，优化器不会使用它自己的最优猜测，而会使用我们给出的最优猜测。

临时表小结

如果应用中需要临时存储一个行集由其他表处理（可能对应一个会话，也可能对应一个事务），临时表就很有用。不要把临时表作为一个分解大查询的方法，即拿到一个大查询，把它“分解”为几个较小的结果集，然后再把这些结果集合并在一起（这看来是其他数据库中最常见的临时表用法）。实际上，你会发现，在几乎所有的情况下。Oracle中如果将一个查询分解为较小的临时表查询，与原来的一个查询相比，只会执行得更慢。我就经常看到人们这样做，如果有可能把对临时表的一系列INSERT重写为一个大查询（SELECT），所得到的单个查询会比原来的多步过程快得多。

临时表会生成少量的redo，但是确实还是会生成redo，而且没有办法避免。这些redo是为回滚数据生成的，而且在最典型的情况下，可以忽略不计。如果只是对临时表执行INSERT和SELETE，生成的redo量几乎注意不到。只有对临时表执行大量DELETE和UPDATE时，才会看到生成大量的redo。

如果精心设计，可以在临时表上生成CBO使用的统计信息；不过，可以使用DBMS_STATS包对临时表上的统计给出更好的猜测，或者由优化器使用动态采样在硬解析时动态收集。

10.10      对象表

我们已经看到了一个不完整的对象表（带嵌套表）例子。对象表（object table）是基于一个TYPE创建的表，而不是作为一个列集合。正常情况下，CREATE TABLE可能如下所示：

create table t ( x int, y date, z varchar2(25) );

对象表创建语句则如下所示：

create table t of Some_Type;

T 的属性（列）由SOME_TYPE的定义得出。下面来简单地看一个例子，这里用到了几种类型，然后查看所得到的数据结构：

ops$tkyte@ORA10G> create or replace type address_type 2 as object 3           ( city varchar2(30), 4           street varchar2(30), 5           state varchar2(2), 6           zip number 7 ) 8 / Type created.   ops$tkyte@ORA10G> create or replace type person_type 2 as object 3           ( name varchar2(30), 4           dob date, 5           home_address address_type, 6           work_address address_type 7 ) 8 / Type created.   ops$tkyte@ORA10G> create table people of person_type 2 / Table created.   ops$tkyte@ORA10G> desc people Name                                              Null?     Type ----------------------------------------  --------    ---------------------------- NAME                                                             VARCHAR2(30) DOB                                                                DATE HOME_ADDRESS                                       ADDRESS_TYPE WORK_ADDRESS                                      ADDRESS_TYPE

就这么多。我们创建了一些类型定义，接下来可以创建这种类型的表。这个表有4列，表示所创建的PERSON_TYPE的4个属性。现在我们可以在这个对象表上执行DML来创建和查询数据了：

ops$tkyte@ORA10G> insert into people values ( 'Tom', '15-mar-1965', 2           address_type( 'Reston', '123 Main Street', 'Va', '45678' ), 3           address_type( 'Redwood', '1 Oracle Way', 'Ca', '23456' ) ); 1 row created.   ops$tkyte@ORA10G> select * from people; NAME                         DOB         HOME_ADDRESS(CITY, S WORK_ADDRESS(CITY, S -----      ---------           --------------------         -------------------- Tom     15-MAR-65 ADDRESS_TYPE('Reston ADDRESS_TYPE('Redwoo                                       ', '123 Main Street' d', '1 Oracle Way',                                       , 'Va', 45678) 'Ca', 23456)   ops$tkyte@ORA10G> select name, p.home_address.city from people p; NAME     HOME_ADDRESS.CITY -----          ------------------------------ Tom         Reston

从现在开始，可以看到处理对象类型必需的一些对象语法了。例如，在INSERT语句中，必须把HOME_ADDRESS和WORK_ADDRESS用一个CAST包装起来。我们将标量值强制转换为一个ADDRESS_TYPE。对此也可以用另一种说法来解释，我们使用ADDRESS_TYPE对象的默认构造函数为这一行创建了一个ADDRESS_TYPE实例。

现在，从外部来看这个表，表中只有4个列。由于我们已经了解到嵌套表内部有神奇的隐藏列，所以可以猜测到，对象表可能也不会这么简单，也许还会做其他的事情。Oracle把所有对象关系数据都存储在普通的关系表中，最终还是存储在行和列中。如果挖掘“真正”的数据字典，可以看到这个表实际上是什么样子：

ops$tkyte@ORA10G> select name, segcollength 2 from sys.col$ 3 where obj# = ( select object_id 4                  from user_objects 5                  where object_name = 'PEOPLE' ) 6 / NAME                                        SEGCOLLENGTH -------------------------                                   ------------ SYS_NC_OID$                                                    16 SYS_NC_ROWINFO$                                          1 NAME                                                                    30 DOB                                                                          7 HOME_ADDRESS                                                1 SYS_NC00006$                                                  30 SYS_NC00007$                                                  30 SYS_NC00008$                                                    2 SYS_NC00009$                                                  22 WORK_ADDRESS                                                1 SYS_NC00011$                                                  30 SYS_NC00012$                                                  30 SYS_NC00013$                                                    2 SYS_NC00014$                                                  22 14 rows selected.

看上去这与DESCRIBE告诉我们的结果完全不同。显然，这个表中有14列，而不是4列。在这个例子中，这些列分别是：

q         SYS_NC_OID$：这是表中系统生成的对象ID。这是一个惟一的RAW(16)列。这个列上有一个惟一约束，而且在这个列上还创建了一个相应的惟一索引。

q         SYS_NC_ROWINFO$：这是嵌套表中已经研究过的那个“神奇”函数。如果从表中选择这个列，它会把整行作为一列返回：

ops$tkyte@ORA10G> select sys_nc_rowinfo$ from people;   SYS_NC_ROWINFO$(NAME, DOB, HOME_ADDRESS(CITY,STREET,STATE,ZIP), WORK_ADDRESS ---------------------------------------------------------------------------- PERSON_TYPE('Tom', '15-MAR-65', ADDRESS_TYPE('Reston', '123 Main Street', 'Va', 45678), ADDRESS_TYPE('Redwood', '1 Oracle Way', 'Ca', 23456))

q         NAME.DOB：这些是对象表的标量属性。与我们预期的一样，它们存储为常规的列。

q         HOME_ADDRESS，WORK_ADDRESS：这些也是“神奇的”函数。它们把所表示的列集返回为一个对象。这些不占用实际空间，只是为实际指示NULL或NOT NULL。

q         SYS_NCnnnnn$：这些是嵌入的对象类型的标量实现。由于PERSON_TYPE中嵌入了ADDRESS_TYPE，Oracle需要留出空间将它们存储在适当类型的列中。系统生成的名字是必要的，因为列名必须惟一，我们很可能多次使用同一个对象类型（像这里一样），如果不采用系统生成的名字，最后就完全有可能重复地使用相同的名字，如有两个ZIP列。

所以，就像嵌套表一样，这里完成了很多工作。首先增加了一个16字节的伪主键，而且为我们创建了一个索引。关于如何为对象指定对象标识符的值，默认行为是可以修改的，稍后将介绍。首先来看生成这个表的完整SQL。同样，这是使用EXP/IMP生成的，因为我想轻松地看到依赖对象，包括重建这个对象所需的全部SQL。这是如下得到的：

[tkyte@localhost tkyte]$ exp userid=/ tables=people rows=n Export: Release 10.1.0.3.0 - Production on Sun May 1 14:04:16 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved.   Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set Note: table data (rows) will not be exported   About to export specified tables via Conventional Path ... . . exporting table PEOPLE Export terminated successfully without warnings.   [tkyte@localhost tkyte]$ imp userid=/ indexfile=people.sql full=y Import: Release 10.1.0.3.0 - Production on Sun May 1 14:04:33 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved.   Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options   Export file created by EXPORT:V10.01.00 via conventional path import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set Import terminated successfully without warnings.

分析people.sql文件，可以看到以下结果：

CREATE TABLE "OPS$TKYTE"."PEOPLE" OF "PERSON_TYPE" OID 'F610318AC3D8981FE030007F01001464' OIDINDEX (PCTFREE 10 INITRANS 2 MAXTRANS 255        STORAGE(INITIAL 131072 NEXT 131072        MINEXTENTS 1 MAXEXTENTS 4096        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1        BUFFER_POOL DEFAULT) TABLESPACE "USERS") PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 131072 NEXT 131072        MINEXTENTS 1 MAXEXTENTS 4096        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1        BUFFER_POOL DEFAULT) TABLESPACE "USERS" NOCOMPRESS /   ALTER TABLE "OPS$TKYTE"."PEOPLE" MODIFY ("SYS_NC_OID$" DEFAULT SYS_OP_GUID()) /

由此我们可以更深入地了解到这里到底发生了什么，现在明显地看到了OIDINDEX子句，而且看到了对SYS_NC_OID$列的一个引用。这是这个表的隐藏主键。函数SYS_OP_GUID与SYS_GUID相同，他们都返回一个全局惟一的标识符，这是一个16字节的RAW字段。

OID’<big hex number>’ 语法在Oracle文档中没有相关说明。它的作用是在EXP和后续的IMP期间，确保底层类型PERSON_TYPE确实是相同的类型。这样当我们完成以下步骤时就能避免可能出现的错误：

（1）         创建PEOPLE表。

（2）         导出这个表。

（3）         删除这个表和底层PERSON_TYPE。

（4）         用不同的属性创建一个新的PERSON_TYPE。

（5）         导入原来的PEOPLE数据。

显然，原来导出的表无法导入到新结构中，因为结构不符。以上检查就能避免这种情况的出现。

如果你还记得，我曾经提到过：为对象实例分配对象标识符的行为是可以修改的。可以不让系统为我们生成一个伪主键，而是使用对象的自然键。最初这看上去有些弄巧成拙——SYS_NC_OID$仍然出现在SYS.COL$的表定义中，相对于系统生成的列来说，似乎这会占用更多的存储空间。不过，这里神奇再现。如果对象表所基于的是一个主键而不是系统生成的键，这个对象表的SYS_NC_OID$列就是虚拟列，并不占用磁盘上的任何实际存储空间。

下面的例子显示了数据字典中发生了什么，并展示出SYS_NC_OID$没有占用物理存储空间。先来分析系统生成的OID表：

ops$tkyte@ORA10G> create table people of person_type 2 / Table created.   ops$tkyte@ORA10G> select name, type#, segcollength 2 from sys.col$ 3 where obj# = ( select object_id 4           from user_objects 5           where object_name = 'PEOPLE' ) 6                  and name like 'SYS/_NC/_%' escape '/' 7 / NAME                                TYPE#      SEGCOLLENGTH -------------------------          ----------                       ------------ SYS_NC_OID$                        23                                  16 SYS_NC_ROWINFO$          121                                     1   ops$tkyte@ORA10G> insert into people(name) 2 select rownum from all_objects; 48217 rows created.   ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'PEOPLE' ); PL/SQL procedure successfully completed.   ops$tkyte@ORA10G> select table_name, avg_row_len from user_object_tables; TABLE_NAME               AVG_ROW_LEN ------------------------------                  ----------- PEOPLE                                                    23

在此我们看到，行平均长度为23字节：16字节用于SYS_NC_OID$，7字节用于NAME。还是做同样的事情，不过这一次使用NAME列上的一个主键作为对象标识符：

ops$tkyte@ORA10G> CREATE TABLE "PEOPLE" 2 OF "PERSON_TYPE" 3           ( constraint people_pk primary key(name) ) 4           object identifier is PRIMARY KEY 5 / Table created.   ops$tkyte@ORA10G> select name, type#, segcollength 2 from sys.col$ 3 where obj# = ( select object_id 4                  from user_objects 5                  where object_name = 'PEOPLE' ) 6                          and name like 'SYS/_NC/_%' escape '/' 7 / NAME                            TYPE#      SEGCOLLENGTH ------------------------------ ----------                     ------------ SYS_NC_OID$                    23                                  81 SYS_NC_ROWINFO$      121                                     1

根据结果来看，现在SYS_NC_OID$列不是只有16字节，而变成一个81字节的大列！实际上，这里没有存储任何数据，它是空的。系统会根据对象表、其底层类型和行本身中的值生成一个惟一ID。如下所示：

ops$tkyte@ORA10G> insert into people (name) 2 values ( 'Hello World!' ); 1 row created.   ops$tkyte@ORA10G> select sys_nc_oid$ from people p; SYS_NC_OID$ ------------------------------------------------------------------------------- F610733A48F865F9E030007F0100149A00000017260100010001002900000000000C07001E01000 02A 00078401FE000000140C48656C6C6F20576F726C642100000000000000000000000000000000 0000   ops$tkyte@ORA10G> select utl_raw.cast_to_raw( 'Hello World!' ) data 2 from dual; DATA ------------------------------------------------------------------------------- 48656C 6C 6F 20576F 726C 6421   ops$tkyte@ORA10G> select utl_raw.cast_to_varchar2(sys_nc_oid$) data 2 from people; DATA ------------------------------------------------------------------------------- <garbage bits and bytes..>Hello World!

如果选择SYS_NC_OID$列，查看所插入串的HEX转储信息，可以看到行数据本身已经嵌入到对象ID中。将对象ID转换为一个VARCHAR2，可以更清楚地确认这一点。这是不是表示我们的数据要存储两次，而且存在大量开销？不，并非如此，这正是神奇之处，只是在获取时才有这样的SYS_NC_OID$列。Oracle从表中选择SYS_NC_OID$时会合成数据。

下面来表明我的观点。对象关系组件（嵌套表和对象表）就是我所说的“语法迷药”（syntactic sugar）。嵌套表和对象表总会转换为原来的关系行和列。我个人不把它们用作物理存储机制。”神奇之处“太多了，而且它们的副作用并不是很清楚。你会得到隐藏列、额外的索引、奇怪的伪列等。这并不是说对象关系组件毫无用处，恰恰相反，我就经常在PL/SQL中使用这些组件。我会利用对象视图来得到对象关系组件的功能。这么一来，不仅可以得到嵌套表结构的优点（同样能表示主表/明细表关系，但通过网络返回（传输）的数据较少；概念上也更容易于使用，等等），而且不存在任何物理存储问题。这是因为我可以使用对象视图，从关系数据合成对象。这就解决了对象表/嵌套表的大多数问题，因为物理存储由我来指定，联结条件也由我建立，而且这些表可以很自然地用作关系表（这一点是许多第三方工具和应用所要求的）。如果有人需要关系数据的对象视图，这是可以做到的；倘若有人需要关系视图，也同样可以达到目的。由于对象表实际上就是伪装的关系表，所以Oracle在底层为我们做的事情我们自己也可以做，而且还能更高效地完成工作，因为我们不必像对象表那样采用一般性的方式。例如，使用前面定义的类型，可以很容易地使用以下命令创建对象视图：

ops$tkyte@ORA10G> create table people_tab 2 ( name varchar2(30) primary key, 3 dob date, 4 home_city varchar2(30), 5 home_street varchar2(30), 6 home_state varchar2(2), 7 home_zip number, 8 work_city varchar2(30), 9 work_street varchar2(30), 10 work_state varchar2(2), 11 work_zip number 12 ) 13 / Table created.   ops$tkyte@ORA10G> create view people of person_type 2 with object identifier (name) 3 as 4           select name, dob, 5                  address_type(home_city,home_street,home_state,home_zip) home_adress, 6                  address_type(work_city,work_street,work_state,work_zip) work_adress 7           from people_tab 8 / View created.   ops$tkyte@ORA10G> insert into people values ( 'Tom', '15-mar-1965', 2           address_type( 'Reston', '123 Main Street', 'Va', '45678' ), 3           address_type( 'Redwood', '1 Oracle Way', 'Ca', '23456' ) ); 1 row created.

无论如何，效果完全相同，我很清楚存储什么、如何存储，以及在哪里存储。对于更复杂的对象，可能必须在对象视图上编写INSTEAD OF触发器，以允许通过视图修改数据。

对象表小结

Oracle 中的对象表用于实现对象关系模型。一个对象表一般会创建多个物理的数据库对象，并向模式增加一些额外的列完成管理。对象表存在一些“神奇“的方面。利用对象视图，你可以利用”对象“的语法和语义，与此同时，还能对数据的物理存储有完全的控制，并允许对底层数据进行关系型访问。采用这种方式，就能同时得到关系世界和对象关系世界中最棒的特点。

10.11      小结

读完这一章后，希望你已经得出这样一个结论：并非所有的表都创建得完全一样。Oracle提供了多种表类型可供使用。在这一章中，我们介绍了一般情况下表的一些突出的方面，并分析了Oracle为我们提供的各种表类型。

首先，我们介绍了与表相关的一些术语以及存储参数。这里讨论了freelist在多用户环境中的作用，如果多个人同时频繁地插入/更新一个表，freelist将产生很大影响；另外说明了如果使用ASSM表空间，就不必再考虑freelist。我们研究了PCTFREE和PCTUSED的含义，并为如何正确地设置这些信息提供了一些指导原则。

然后开始介绍各种类型的表，先从最常见的堆表开始。堆组织表是目前大多数Oracle应用中最常用的表，这也是默认的表类型。接下来介绍索引组织表（IOT），利用IOT，可以把表数据存储在索引结构中而不是堆表中。我们了解了这些表适用于哪些情况（如查询表和反向表），在这些情况下，堆表就不合适了，它将只是数据的一个冗余副本。在本章的后面，我们还指出了结合使用IOT与其他表类型确实很有用，特别是嵌套表类型。

我们还介绍了聚簇对象，Oracle中有3种聚簇：索引聚簇、散列聚簇和有序散列聚簇。聚簇有两方面的目标：

q         使我们能够把多个表的数据共同存储在同一个（多个）数据库块上。

q         是我们能够强制把类似的数据根据某个聚簇键物理地存储在“一起“。例如，采用这种方式，部门10的所有数据（来自多个表）可以存储在一起。

基于这些特性，我们可以非常快地访问相关的数据，而且只有最少的物理I/O。我们研究了索引聚簇和散列聚簇之间的主要区别，并讨论了各种聚簇分别在何种情况下适用（和不适用）。

接下来转向嵌套表。我们介绍了嵌套表的语法、语义和用法，了解到嵌套表实际上就是系统生成和维护的父/子表对，并且知道了Oracle在物理上是如何做到这一点的。我们分析了可以使用不同的表类型来实现嵌套表，默认会使用基于堆的表。我们发现，如果不使用IOT而使用堆表来实现嵌套表，一般这都是说不过去的。

接下来我们介绍了临时表的有关细节，包括如何创建、从哪里得到存储空间，还说明了临时表在运行时不会引入与并发性相关的任何问题。我们分析了会话级和事务级临时表之间的区别，讨论了Oracle数据库中使用临时表的适当方法。

在这一章的最后，我们讨论了对象表的内部工作原则。我们发现，与嵌套表一样，Oracle的对象表在底层也有很多“动作“。在此指出：利用关系表上的对象视图可以得到对象表的功能，与此同时还能轻松地访问底层关系数据。