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

第11章                      索引

索引是应用设计和开发的一个重要方面。如果有太多的索引，DML的性能就会受到影响。如果索引太少，又会影响查询（包括插入、更新和删除）的性能。要找到一个合适的平衡点，这对于应用的性能至关重要。

我常常发现，人们在应用开发中总是事后才想起索引。我坚持认为这是一种错误的做法。如果你知道数据将如何使用，从一开始就应该能提出应用中要使用怎样的索引，即具有一组代表性的索引。不过，一般的做法却往往是随应用“放任自流“，过后才发现哪里需要索引，这种情况实在太多了。这说明，你没有花时间来了解数据将如何使用以及最终要处理多少行。经过一段时间后，随着数据量的增长，你会不停地向系统增加索引（也就是说，你所执行的是一种反应式调优）。你就有一些冗余而且从不使用的索引，这不仅会浪费空间，还会浪费计算资源。磨刀不误砍柴工，如果刚开始的时候花几个小时好好地考虑何时为数据加索引，以及如何加索引，这肯定能在以后的”调优“中节省更多的时间（注意，我所说的是”肯定能“节省更多时间，而不只是”可能“节省更多时间）。

这一章的主旨是对Oracle中可用的索引提供一个概述，讨论什么时候以及在哪里可以使用索引。这一章的风格和格式与本书其他章有所不同。索引是一个很宽泛的主题，光是介绍索引就可以单独写一本书，其部分原因是：索引是开发人员和DBA角色之间的一个桥梁。一方面，开发人员必须了解索引，清楚如何在应用中使用索引，而且知道何时使用索引（以及何时不使用索引）等。另一方面，DBA则要考虑索引的增长、索引中存储空间的使用以及其他物理特性。我们将主要从应用角度来考虑，也就是从索引的实际使用来介绍索引。这一章前半部分提供了一些基本知识。这一章的后半部分回答了关于索引的一些最常问到的问题。

这一章中的各个例子分别需要不同的Oracle版本中的特性。如果每个例子需要Oracle企业版或个人版的某些特性（而标准版中不支持），我会明确地指出来。

11.1   Oracle索引概述

Oracle提供了多种不同类型的索引以供使用。简单地说，Oracle中包括如下索引：

B*树索引：这些是我所说的“传统“索引。到目前为止，这是Oracle和大多数其他数据库中最常用的索引。B*树的构造类似于二叉树，能根据键提供一行或一个行集的快速访问，通常只需很少的读操作就能找到正确的行。不过，需要注意重要的一点，”B*树“中的”B“不代表二叉（binary），而代表平衡（balanced）。B*树索引并不是一颗二叉树，这一点在介绍如何在磁盘上物理地存储B*树时就会了解到。B*树索引有以下子类型：

索引组织表（index organized table）：索引组织表以B*树结构存储。堆表的数据行是以一种无组织的方式存储的（只要有可用的空间，就可以放数据），而IOT与之不同，IOT中的数据要按主键的顺序存储和排序。对应用来说，IOT表现得与“常规“表并无二致；需要使用SQL来正确地访问IOT。IOT对信息获取、空间系统和OLAP应用最为有用。IOT在上一章已经详细地讨论过。

B*树聚簇索引（B*tree cluster index）这些是传统B*树索引的一个变体（只是稍有变化）。B*树聚簇索引用于对聚簇键建立索引（见第11.章中“索引聚簇表“一节），所以这一章不再讨论。在传统B*树中，键都指向一行；而B*树聚簇不同，一个聚簇键会指向一个块，其中包含与这个聚簇键相关的多行。

降序索引（descending index）：降序索引允许数据在索引结构中按“从大到小“的顺序（降序）排序，而不是按”从小到大“的顺序（升序）排序。我们会解释为什么降序索引很重要，并说明降序索引如何工作。

反向键索引（reverse key index）：这也是B*树索引，只不过键中的字节会“反转“。利用反向键索引，如果索引中填充的是递增的值，索引条目在索引中可以得到更均匀的分布。例如，如果使用一个序列来生成主键，这个序列将生成诸如987500、987501、987502等值。这些值是顺序的，所以倘若使用一个传统的B*树索引，这些值就可能放在同一个右侧块上，这就加剧了对这一块的竞争。利用反向键，Oracle则会逻辑地对205789、105789、005789等建立索引。Oracle将数据放在索引中之前，将先把所存储数据的字节反转，这样原来可能在索引中相邻放置的值在字节反转之后就会相距很远。通过反转字节，对索引的插入就会分布到多个块上。

位图索引（bitmap index）：在一颗B*树中，通常索引条目和行之间存在一种一对一的关系：一个索引条目就指向一行。而对于位图索引，一个索引条目则使用一个位图同时指向多行。位图索引适用于高度重复而且通常只读的数据（高度重复是指相对于表中的总行数，数据只有很少的几个不同值）。考虑在一个有100万行的表中，每个列只有3个可取值：Y、N和NULL。举例来说，如果你需要频繁地统计多少行有值Y，这就很适合建立位图索引。不过并不是说如果这个表中某一列有11.000个不同的值就不能建立位图索引，这一列当然也可以建立位图索引。在一个OLTP数据库中，由于存在并发性相关的问题，所以不能考虑使用位图索引（后面我们就会讨论这一点）。注意，位图索引要求使用Oracle企业版或个人版。

位图联结索引（bitmap join index）：这为索引结构（而不是表）中的数据提供了一种逆规范化的方法。例如，请考虑简单的EMP和DEPT表。有人可能会问这样一个问题：“多少人在位于波士顿的部门工作？“EMP有一个指向DEPT的外键，要想统计LOC值为Boston的部门中的员工人数，通常必须完成表联结，将LOC列联结至EMP记录来回答这个问题。通过使用位图联结索引，则可以在EMP表上对LOC列建立索引。

基于函数的索引（function-based index）：这些就是B*树索引或位图索引，它将一个函数计算得到的结果存储在行的列中，而不是存储列数据本身。可以把基于函数的索引看作一个虚拟列（或派生列）上的索引，换句话说，这个列并不物理存储在表中。基于函数的索引可以用于加快形如SELECT * FROM T WHERE FUNCTION(DATABASE_COLUMN) = SAME_VALUE这样的查询，因为值FUNCTION(DATABASE_COLUMN)已经提前计算并存储在索引中。

应用域索引（application domain index）：应用域索引是你自己构建和存储的索引，可能存储在Oracle中，也可能在Oracle之外。你要告诉优化器索引的选择性如何，以及执行的开销有多大，优化器则会根据你提供的信息来决定是否使用你的索引。Oracle文本索引就是应用域索引的一个例子；你也可以使用构建Oracle文本索引所用的工具来建立自己的索引。需要指出，这里创建的“索引“不需要使用传统的索引结构。例如，Oracle文本索引就使用了一组表来实现其索引概念。

可以看到，可供选择的索引类型有很多。在下面几节中，我将提供有关的一些技术细节来说明某种索引如何工作，以及应该何时使用这些索引。需要重申一遍，在此不会涵盖某些与DBA相关的主题。例如，我们不打算讨论在线重建索引的原理；而会把重点放在与应用相关的实用细节上。

11.2   B*树索引

B*树索引就是我所说的“传统“索引，这是数据库中最常用的一类索引结构。其实现与二叉查找树很相似。其目标是尽可能减少Oracle查找数据所花费的时间。不严格地说，如果在一个数字列上有一个索引，那么从概念上讲这个结构可能如图11.-1所示。

注意      也许会有一些块级优化和数据压缩，这些可能会使实际的块结构与图11.-1所示并不同。

图11.-1    典型的B*树索引布局

这个树最底层的块称为叶子节点（leaf node）或叶子块（leaf block），其中分别包含各个索引键以及一个rowid（指向所索引的行）。叶子节点之上的内部块称为分支块（branch block）。这些节点用于在结构中实现导航。例如，如果想在索引中找到值42，要从树顶开始，找到左分支。我们要检查这个块，并发现需要找到范围在“42..50“的块。这个块将是叶子块，其中会指示包含数42的行。有意思的是，索引的叶子节点实际上构成了一个双向链表。一旦发现要从叶子节点中的哪里”开始“（也就是说，一旦发现第一个值），执行值的有序扫描（也称为索引区间扫描（index range scan））就会很容易。我们不用再在索引结构中导航；而只需根据需要通过叶子节点向前或向后扫描就可以了。所以要满足诸如以下的谓词条件将相当简单：

where x between 20 and 30

Oracle发现第一个最小键值大于或等于20的索引叶子块，然后水平地遍历叶子节点链表，直到最后命中一个大于30的值。

B*树索引中不存在非惟一（nonunique）条目。在一个非惟一索引中，Oracle会把rowid作为一个额外的列（有一个长度字节）追加到键上，使得键惟一。例如，如果有一个CREATE INDEX I ON T(X,Y)索引，从概念上讲，它就是CREATE UNIQUE INDEX I ON T(X,Y,ROWID)。在一个惟一索引中，根据你定义的惟一性，Oracle不会再向索引键增加rowid。在非惟一索引中，你会发现，数据会首先按索引键值排序（依索引键的顺序）。然后按rowid升序排序。而在惟一索引中，数据只按索引键排序。

B*树的特点之一是：所有叶子块都应该在树的同一层上。这一层也称为索引的高度（height），这说明所有从索引的根块到叶子块的遍历都会访问同样数目的块。也就是说，对于形如”SELECT INDEXED_COL FROM T WHERE INDEXED_COL = :X“的索引，要到达叶子块来获取第一行，不论使用的:X值是什么，都会执行同样数目的I/O。换句话说，索引是高度平衡的（height balanced）。大多数B*树索引的高度都是2或者3，即使索引中有数百万行记录也是如此。这说明，一般来讲，在索引中找到一个键只需要执行2或3次I/O，这倒不坏。

­­­注意      Oracle在表示从索引根块到叶子块遍历所涉及的块数时用了两个含义稍有不同的术语。第一个是高度（HEIGHT），这是指从根块到叶子块遍历所需的块数。使用ANALYZE INDEX <name> VALIDATE STRUCTURE命令分析索引后，可以从INDEX_STATS视图找到这个高度（HEIGHT）值。另一个术语是BLEVEL，这是指分支层数，与HEIGHT相差1（BLEVEL不把叶子块层算在内）。收集统计信息后，可以在诸如USER_INDEXES之类的常规字典表中找到BLEVEL值。

例如，假设有一个11.,000,000行的表，其主键索引建立在一个数字列上：

big_table@ORA9IR2> select index_name, blevel, num_rows 2 from user_indexes 3 where table_name = 'BIG_TABLE'; INDEX_NAME                   BLEVEL       NUM_ROWS ------------------------------       ----------                  ---------- BIG_TABLE_PK                             2            10441513

BLEVEL为2，这说明HEIGHT为3，要找到叶子需要两个I/O（访问叶子本身还需要第三个I/O）。所以，要从这个索引中获取任何给定的键值，共需要3个I/O：

big_table@ORA9IR2> select id from big_table where id = 42; Execution Plan ---------------------------------------------------------- 0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=11.Bytes=6) 11.0            INDEX (UNIQUE SCAN) OF 'BIG_TABLE_PK' (UNIQUE) (Cost=2 Card=11.Bytes=6) Statistics ---------------------------------------------------------- ... 3 consistent gets ... 11.rows processed big_table@ORA9IR2> select id from big_table where id = 12345; Statistics ---------------------------------------------------------- ... 3 consistent gets ... 11.rows processed big_table@ORA9IR2> select id from big_table where id = 1234567; Statistics ---------------------------------------------------------- ... 3 consistent gets ... 11.rows processed

B*树是一个绝佳的通用索引机制，无论是大表还是小表都很适用，随着底层表大小的增长，获取数据的性能只会稍有恶化（或者根本不会恶化）。

11.2.1             索引键压缩

对于B*树索引，可以做的一件有意思的工作是“压缩”。这与压缩ZIP文件的方式不同，它是指从串联（多列）索引去除冗余。

在第11.章的“索引组织表”一节中我们曾经详细地讨论压缩键索引，这里再简要地做个说明。压缩键索引（compressed key index）的基本概念是，每个键条目分解为两个部分：“前缀”和“后缀”。前缀建立在串联索引（concatenated index）的前几列上，这些列有许多重复的值。后缀则在索引键的后几列上，这是前缀所在索引条目中的惟一部分（即有区别的部分）。

下面通过一个例子来说明，我们将创建一个表和一个串联索引，并使用ANALYZE INDEX测量无压缩时所用的空间。然后利用索引压缩创建这个索引，分别压缩不同数目的键条目，查看有什么差别。下面先来看这个表和索引：

ops$tkyte@ORA 10G > create table t 2 as 3 select * from all_objects; Table created.   ops$tkyte@ORA 10G > create index t_idx on 2 t(owner,object_type,object_name); Index created.   ops$tkyte@ORA 10G > analyze index t_idx validate structure; Index analyzed.

然后创建一个INX_STATS表，在这里存放INDEX_STATS信息，我们把表中的行标记为“未压缩”（noncompressed）：

ops$tkyte@ORA 10G > create table idx_stats 2 as 3 select 'noncompressed' what, a.* 4 from index_stats a; Table created.

现在可以看到，OWNER部分重复了多次，这说明，这个索引中的一个索引块可能有数十个条目，如图11.-2所示。

图11.-2    有重复OWNER列的索引块

可以从中抽取出重复的OWNER列，这会得到如同11.-3所示的块。

图11.-3    抽取了OWNER列的索引块

在图11.-3中，所有者（owner）名在叶子块上只出现了一次，而不是在每个重复的条目上都出现一次。运行以上脚本，传入数字1作为参数来重新创建这个索引，在此索引使用了第一列的压缩：

drop index t_idx; create index t_idx on t(owner,object_type,object_name) compress &1; analyze index t_idx validate structure; insert into idx_stats select 'compress &1', a.* from index_stats a;

为了进行比较，我们不仅在压缩一列的基础上运行了这个脚本，还分别使用了两个和3个压缩列来运行这个脚本，查看会发生什么情况。最终，我们将查询IDX_STATS，应该能观察到以下信息：

ops$tkyte@ORA 10G > select what, height, lf_blks, br_blks, 2 btree_space, opt_cmpr_count, opt_cmpr_pctsave 3 from idx_stats 4 / WHAT               HEIGHT LF_BLKS   BR_BLKS BTREE_SPACE   OPT_CMPR_COUNT OPT_CMPR_PCTSAVE -------------          -------    --------          -------            -----------                --------------          ---------------- noncompressed     3        337                 3           2718736                                 2                          28 compress 1             3        300                 3           2421684                                 2                          11. compress 2             2        240                 1           1926108                                 2                             0 compress 3             3        375                 3           3021084                                 2                          35

可以看到，COMPRESS 1索引的大小大约是无压缩索引的89%（通过比较BTREE_SPACE得出）。叶子块数大幅度下降。更进一步，使用COMPRESS 2时，节省的幅度更为显著。所得到的索引大约是原索引（无压缩索引）的70%，而且由于数据量减少，这些数据能放在单个块上，相应地索引的高度就从3降为2.实际上，利用列OPT_CMPR_PCTSAVE的信息（这代表最优的节省压缩百分比（optimum compression percent saved）或期望从压缩得到的节省幅度）。我们可以猜测出COMPRESS 2索引的大小：

ops$tkyte@ORA 10G > select 2718736*(11.0.28) from dual;      2718736*(11.0.28)               ----------------              1957489.92

注意      对无压缩索引执行ANALYZE命令时，会填写OPT_CMPR_PCTSAVE/OPT_CMPR_COUNT列，并估计出：利用COMPRESS 2，可以节省28%的空间；而事实确实如此，我们果真节省了大约这么多的空间。

不过，再看看COMPRESS 3会怎么样。如果压缩3列，所得到的索引实际上会更大：是原来索引大小的110%。这是因为：每删除一个重复的前缀，能节省N个副本的空间，但是作为压缩机制的一部分，这会在叶子块上增加4字节的开销。把OBJECT_NAME列增加到压缩键后，则使得这个键是惟一的；在这种情况下，则说明没有重复的副本可以提取。因此，最后的结果就是：我们只是向每个索引键条目增加了4个字节，而不能提取出任何重复的数据。IDX_STATS中的OPT_CMPR_COUNT列真是精准无比，确实给出了可用的最佳压缩数，OPT_COMPR_PCTSAVE则指出了可以得到多大的节省幅度。

对现在来说，这种压缩不是免费的。现在压缩索引比原来更复杂了。Oracle会花更多的时间来处理这个索引结构中的数据，不光在修改期间维护索引更耗时，查询期间搜索索引也更花时间。利用压缩，块缓冲区缓存比以前能存放更多的索引条目，缓冲命中率可能会上升，物理I/O应该下降，但是要多占用一些CPU时间来处理索引，还会增加块竞争的可能性。在讨论散列聚簇时，我们曾经说过，对于散列聚簇，获取100万个随机的行可能占用更多的CPU时间，但是I/O数会减半；这里也是一样，我们必须清楚存在的这种折中。如果你现在已经在大量占用CPU时间，在增加压缩键索引只能适得其反，这会减慢处理的速度。另一方面，如果目前的I/O操作很多，使用压缩键索引就能加快处理速度。

11.2.2             反向键索引

B*树索引的另一个特点是能够将索引键“反转”。首先，你可以问问自己“为什么想这么做？” B*树索引是为特定的环境、特定的问题而设计的。实现B*树索引的目的是为了减少“右侧”索引中对索引叶子块的竞争，比如在一个Oracle RAC环境中，某些列用一个序列值或时间戳填充，这些列上建立的索引就属于“右侧”（right-hand-side）索引。

注意      我们在第2章讨论过RAC。

RAC是一种Oracle配置，其中多个实例可以装载和打开同一个数据库。如果两个实例需要同时修改同一个数据块，它们会通过一个硬件互连（interconnect）来回传递这个块来实现共享，互连是两个（或多个）机器之间的一条专用网络连接。如果某个利用一个序列填充，这个列上有一个主键索引（这是一种非常流行的实现），那么每个人插入新值时，都会视图修改目前索引结构右侧的左块（见图11.-1，其中显示出索引中较高的值都放在右侧，而较低的值放在左侧）。如果对用序列填充的列上的索引进行修改，就会聚集在很少的一组叶子块上。倘若将索引的键反转，对索引进行插入时，就能在索引中的所有叶子键上分布开（不过这往往会使索引不能得到充分地填充）。

注意      你可能还会注意到，反向键可以用作一种减少竞争的方法（即使只有一个Oracle实例）。不过重申一遍，如这一节所述，反向键主要用于缓解忙索引右侧的缓冲区忙等待。

在介绍如何度量反向键索引的影响之前，我们先来讨论物理上反向键索引会做什么。反向键索引只是将索引键中各个列的字节反转。如果考虑90101、90102和90103这样几个数，使用Oracle DUMP函数查看其内部表示，可以看到这几个数的表示如下：

ops$tkyte@ORA10GR1> select 90101, dump(90101,11.) from dual 2 union all 3 select 90102, dump(90102,11.) from dual 4 union all 5 select 90103, dump(90103,11.) from dual 6 /             90101     DUMP(90101,11.)         ----------     ---------------------           90101     Typ=2 Len=4: c3,a,2,2           90102     Typ=2 Len=4: c3,a,2,3           90103     Typ=2 Len=4: c3,a,2,4

每个数的长度都是4字节，它们只是最后一个字节有所不同。这些数最后可能在一个索引结构中向右依次放置。不过，如果反转这些数的字节，Oracle就会插入以下值：

ops$tkyte@ORA10GR1> select 90101, dump(reverse(90101),11.) from dual 2 union all 3 select 90102, dump(reverse(90102),11.) from dual 4 union all 5 select 90103, dump(reverse(90103),11.) from dual 6 /             90101     DUMP(REVERSE(90101),1         ----------     ---------------------           90101     Typ=2 Len=4: 2,2,a,c3           90102     Typ=2 Len=4: 3,2,a,c3           90103     Typ=2 Len=4: 4,2,a,c3

注意      REVERSE函数没有相关的文档说明，因此，使用是当心。我不建议在“实际“代码中使用REVERSE，因为它没有相关的文档，这说明这个函数未得到公开支持。

这些数彼此之间最后会“相距很远“。这样访问同一个块（最右边的块）的RAC实例个数就能减少，相应地，在RAC实例之间传输的块数也会减少。反向键索引的缺点之一是：能用常规索引的地方不一定能用反向键索引。例如，在回答以下谓词时，X上的反向键索引就没有：

where x > 5

存储之前，数据不是按X在索引中排序，而是按REVERSE(X)排序，因此，对X>5的区间扫描不能使用这个索引。另一方面，有些区间扫描确实可以在反向键索引上完成。如果在(X,Y)上有一个串联索引，以下谓词就能够利用反向键索引，并对它执行“区间扫描“：

where x = 5

这是因为，首先将X的字节反转，然后再将Y的字节反转。Oracle并不是将（X||Y）的字节反转，而是会存储(REVERSE(X) || REVERSE(Y))。这说明， X = 5的所有值会存储在一起，所以Oracle可以对这个索引执行区间扫描来找到所有这些数据。

下面，假设在一个用序列填充的表上有一个代理主键（surrogate primary key），而且不需要在这个（主键）索引上使用区间扫描，也就是说，不需要做MAX(primary_key)、MIN(primary_key)、WHERE primary_key < 100等查询，在有大量插入操作的情况下，即使只有一个Oracle实例，也可以考虑使用反向键索引。我建立了两个不同的测试，一个是在纯PL/SQL环境中进行测试，另一个使用了Pro*C，我想通过这两个测试来展示反向键索引和传统索引对插入的不同影响，即如果一个表的主键上有一个反向键索引，与有一个传统索引的情况相比，完成插入时会有什么差别。在这两种情况下，所用的表都是用以下DDL创建的（这里使用了ASSM来避免表块的竞争，这样可以把索引块的竞争隔离开）：

create table t tablespace assm as select 0 id, a.* from all_objects a where 11.0;   alter table t add constraint t_pk primary key (id) using index (create index t_pk on t(id) &indexType tablespace assm);   create sequence s cache 1000;

在此如果把&indexType替换为关键字REVERSE，就会创建一个反向键索引，如果不加&indexType（即替换为“什么也没有“），则表示使用一个”常规“索引。要运行的PL/SQL代码如下，将分别由1、2、5、11.或11.个用户并发运行这个代码：

create or replace procedure do_sql as begin for x in ( select rownum r, all_objects.* from all_objects ) loop               insert into t                      ( id, OWNER, OBJECT_NAME, SUBOBJECT_NAME,                      OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,                      LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,                      GENERATED, SECONDARY )               values                      ( s.nextval, x.OWNER, x.OBJECT_NAME, x.SUBOBJECT_NAME,                      x.OBJECT_ID, x.DATA_OBJECT_ID, x.OBJECT_TYPE, x.CREATED,                      x.LAST_DDL_TIME, x.TIMESTAMP, x.STATUS, x.TEMPORARY,                      x.GENERATED, x.SECONDARY );               if ( mod(x.r,100) = 0 )               then                      commit;               end if; end loop; commit; end; /

我们已经在第9章讨论过PL/SQL提交时优化，所以现在我想运行使用另一种环境的测试，以免被这种提交时优化所误导。我使用了Pro*C来模拟一个数据仓库抽取、转换和加载（extract, transform, load, ETL）例程，它会在提交之间一次成批地处理100行（即每次提交前都处理100行）：

exec sql declare c cursor for select * from all_objects; exec sql open c; exec sql whenever notfound do break; for(;;) {               exec sql               fetch c into :owner:owner_i,               :object_name:object_name_i, :subobject_name:subobject_name_i,               :object_id:object_id_i, :data_object_id:data_object_id_i,               :object_type:object_type_i, :created:created_i,               :last_ddl_time:last_ddl_time_i, :timestamp:timestamp_i,               :status:status_i, :temporary:temporary_i,               :generated:generated_i, :secondary:secondary_i;                 exec sql               insert into t                      ( id, OWNER, OBJECT_NAME, SUBOBJECT_NAME,                      OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,                      LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,                      GENERATED, SECONDARY )               values                      ( s.nextval, :owner:owner_i, :object_name:object_name_i,                      :subobject_name:subobject_name_i, :object_id:object_id_i,                      :data_object_id:data_object_id_i, :object_type:object_type_i,                      :created:created_i, :last_ddl_time:last_ddl_time_i,                      :timestamp:timestamp_i, :status:status_i,                      :temporary:temporary_i, :generated:generated_i,                      :secondary:secondary_i );               if ( (++cnt%100) == 0 )               {                      exec sql commit;               } } exec sql whenever notfound continue; exec sql commit; exec sql close c;

Pro*C预编译时PREFETCH设置为100，使得这个C代码与上述PL/SQL代码（要求Oracle版本为Oracle 10g ）是相当的，它们有同样的表现。

注意      在Oracle 10g Release 1及以上版本中，PL/SQL中简单的FOR X IN(SELECT * FROM T)会悄悄地一次批量获取100行，而在Oracle9i及以前版本中，只会一次获取一行。因此，如果想在Oracle9i及以前版本中执行这个测试，就需要修改PL/SQL代码，利用BULK COLLECT语法成批地进行获取。

两种代码都会一次获取100行，然后将数据逐行插入到另一个表中。下表总结了各次运行之间的差别，先从单用户测试开始，如表11.-1所示。

表11.-1 利用PL/SQL和Pro*C对使用反向键索引进行性能测试：单用户

                                 反向                 无反向                 反向              无反向

                               PL/SQL            PL /SQL              Pro*C            Pro*C

事务/秒                               38.24                      43.45                      11..35                  11..08

CPU时间（秒）                    25                           22                           33                       31

缓冲区忙等待数/时间           0/0                          0/0                          0/0                      0/0

耗用时间（分钟）               0.42                        0.37                        0.92                    0.83

日志文件同步数/时间                                          6/0                      11.940/7               11.940/7

从第一个单用户测试可以看到，PL/SQL代码在执行这个操作时比Pro*C代码高效得多，随着用户负载的增加，我们还将继续看到这种趋势。Pro*C之所以不能像PL/SQL那样很好地扩展，部分原因在于Pro*C必须等待日志文件同步等待，而PL/SQL提供了一种优化可以避免这种日志文件同步等待。

从这个单用户测试来看，似乎方向键索引会占用更多的CPU时间。这是有道理的，因为数据库必须执行额外的工作来反转键中的字节。不过，随着用户数的增加，我们会看到这种情况不再成立。随着竞争的引入，方向键索引的开销会完全消失。实际上，甚至在执行两个用户的测试时，这种开销就几乎被索引右侧的竞争所抵消，如表11.-2所示。

表11.-2 利用PL/SQL和Pro*C对使用反向键索引进行性能测试：两个用户

                                 反向                 无反向                 反向              无反向

                               PL/SQL            PL /SQL              Pro*C            Pro*C

事务/秒                               46.59                      49.03                      20.07                  20.29

CPU时间（秒）                    77                           73                          104                     101

缓冲区忙等待数/时间       4,267/2                 133,644/2                 3,286/0              23,688/1

耗用时间（分钟）               0.68                        0.65                        11.58                    11.57

日志文件同步数/时间          11./0                        11./0                    3,273/29             2,132/29

从这个两用户的测试中可以看到，PL/SQL还是要优于Pro*C。另外在PL/SQL这一边，使用方向键索引已经开始显示出某种好处，而在Pro*C一边还没有明显的反映。这种趋势也会延续下去。方向键索引可以解决由于索引结构中对最右边的块的竞争而导致的缓冲区忙等待问题，不过，对于影响Pro*C程序的日志文件同步等待问题则无计可施。这正是我们同时执行一个PL/SQL测试和一个Pro*C测试的主要原因：我们就是想看看这两种环境之间有什么差别。由此产生了一个问题：在这种情况下，为什么方向键索引对PL/SQL明显有好处，而对Pro*C似乎没有作用？归根结底就是因为日志文件同步等待事件。PL/SQL能不断地插入，而很少在提交时等待日志文件同步等待事件，而Pro*C不同，它必须每100行等待一次。因此，在这种情况下，与Pro*C相比，PL/SQL更多地要受缓冲区忙等待的影响。在PL/SQL中如果能缓解缓冲区忙等待，它就能处理更多事务，所以方向键索引对PL/SQL很有好处。但是对于Pro*C，缓冲区忙等待并不是根本问题，这不是主要的性能瓶颈，所以消除缓冲区忙等待对于总体性能来说没有说明影响。

下面来看5个用户的测试，如表11.-3所示

表11.-3 利用PL/SQL和Pro*C对使用反向键索引进行性能测试：5个用户

                                 反向                 无反向                 反向              无反向

                               PL/SQL            PL /SQL              Pro*C            Pro*C

事务/秒                               43.84                      39.78                      11..22                  11..11.

CPU时间（秒）                   389                         395                         561                     588

缓冲区忙等待数/时间     11.,259/45             221,353/153              11.,118/9           157,967/56

耗用时间（分钟）               11.82                        2.00                        4.11.                    4.38

日志文件同步数/时间                                       691/11.                  6,655/73             5,391/82

这里的结果似曾相识。PL/SQL程序运行时几乎没有日志同步等待，所以会显著地受缓冲区忙等待的影响。倘若采用一个传统索引，如果5个用户都试图插入索引结构的右侧，PL/SQL受到缓冲区忙等待的影响最大，相应地，如果能减少这种缓冲区忙等待，所得到的好处也最明显。

下面来看11.个用户测试，如表11.-4所示，可以看到这种趋势还在延续。

表11.-4 利用PL/SQL和Pro*C对使用反向键索引进行性能测试：11.个用户

                                 反向                 无反向                 反向              无反向

                               PL/SQL            PL /SQL              Pro*C            Pro*C

事务/秒                               45.90                      35.38                      11..88                  11..05

CPU时间（秒）                   781                         789                        11.256                  11.384

缓冲区忙等待数/时间    26,846/279          456,231/11.382          25,871/134      364,556/11.702

耗用时间（分钟）               3.47                        4.50                        8.90                    9.92

日志文件同步数/时间                                      2,602/72               11.,032/196         11.,653/141

PL/SQL程序中完全没有日志文件同步等待，通过消除缓冲区忙等待事件，会大为受益。尽管Pro*C程序出现遭遇到更多的缓冲区忙等待竞争，但是由于它还在频繁地等待日志文件同步事件，所以方向键索引对它的好处不大。对于一个有常规索引的PL/SQL实现，要改善它的性能，一种方法是引入一个小等待。这会减少对索引右侧的竞争，并提高总体性能。由于篇幅有限，这里不再给出11.个和20个用户测试的情况，但是可以确保一点，这一节观察到的趋势还会延续。

在这个演示中，我们得出了两个结论。方向键索引有助于缓解缓冲区忙等待问题：但是取决于其他的一些因素，你的投资可能会得到不同的回报。查看11.用户测试的表11.-4时，可以看到，通过消除缓冲区忙等待（在这里，这是等待最多的等待事件），将对事务吞吐量稍有影响；同时也确实显示出：随着并发程度的提高，可扩展性会增加。而对PL/SQL做同样的工作时，对性能的影响则有很大不同：通过消除这个瓶颈，吞吐量会有大幅提升。

11.2.3             降序索引

降序索引（descending index）是Oracle8i开始引入的，用以扩展B*树索引的功能。它允许在索引中以降序（从大到小的顺序）存储一列，而不是升序（从小到大）存储。在之前的Oracle版本（即Oracle8i以前的版本）中，尽管语法上也支持DESC（降序）关键字，但是一般都会将其忽略，这个关键字对于索引中数据如何存储或使用没有任何影响。不过，在Oracle8i及以上版本中，DESC关键字确实会改变创建和使用索引的方式。

Oracle能往前读索引，这种能力已不算新，所以你可能会奇怪我们为什么会兴师动众地说这个特性很重要。例如，如果使用先前的表T，并如下查询这个表：

ops$tkyte@ORA 10G > set autotrace traceonly explain ops$tkyte@ORA 10G > select owner, object_type 2 from t 3 where owner between 'T' and 'Z' 4           and object_type is not null 5 order by owner DESC, object_type DESC; Execution Plan ---------------------------------------------------------- 0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=82 Card=11395 Bytes=170925)

Oracle会往前读索引。这个计划最后没有排序步骤：数据已经是有序的。不过，如果你有一组列，其中一些列按升序排序（ASC），另外一些列按降序排序（DESC），此时这种降序索引就能派上用场了，例如：

ops$tkyte@ORA 10G > select owner, object_type 2 from t 3 where owner between 'T' and 'Z' 4           and object_type is not null 5 order by owner DESC, object_type ASC; Execution Plan ---------------------------------------------------------- 0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=85 Card=11395 Bytes=170925) 11.0            SORT (ORDER BY) (Cost=85 Card=11395 Bytes=170925) 2 1                     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=82 Card=11395 ...

Oracle不能再使用（OWNER, OBJECT_TYPE, OBJECT_NAME）上的索引对数据排序。它可以往前读得到按OWNER DESC排序的数据，但是现在还需要“向后读“来得到按OBJET_TYPE顺序排序（ASC）数据。此时Oracle的实际做法是，它会把所有行收集起来，然后排序。但是如果使用DESC索引，则有：

ops$tkyte@ORA 10G > create index desc_t_idx on t(owner desc,object_type asc); Index created. ops$tkyte@ORA 10G > exec dbms_stats.gather_index_stats( user, 'DESC_T_IDX' ); PL/SQL procedure successfully completed.   ops$tkyte@ORA 10G > select owner, object_type 2 from t 3 where owner between 'T' and 'Z' 4           and object_type is not null 5 order by owner DESC, object_type ASC; Execution Plan ---------------------------------------------------------- 0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=11395 Bytes=170925) 11.0            INDEX (RANGE SCAN) OF 'DESC_T_IDX' (INDEX) (Cost=2 Card=11395 ...

现在，我们又可以读取有序的数据了，在这个计划的最后并没有额外的排序步骤。应当注意，除非init.ora中的compatible参数设置为 8.11.0 或更高，否则CREATE INDEX上的DESC选项会被悄悄地忽略，没有警告，也不会产生错误，因为这是先前版本的默认行为。

注意      查询中最好别少了ORDER BY。即使你的查询计划中包含一个索引，但这并不表示数据会以“某种顺序“返回。要想从数据库以某种有序的顺序获取数据，惟一的办法就是在查询中包括一个ORDER BY子句。ORDER BY是无可替代的。