再上一篇:10.11小结
上一篇:11.1 Oracle索引概述
主页
下一篇:11.3位图索引
再下一篇:11.4基于函数的索引
文章列表

11.2 B*树索引

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

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@ORA10G> create table t
2 as
3 select * from all_objects; Table created.
ops$tkyte@ORA10G> create index t_idx on
2 t(owner,object_type,object_name); Index created.
ops$tkyte@ORA10G> analyze index t_idx validate structure; Index analyzed.
然后创建一个 INX_STATS 表,在这里存放 INDEX_STATS 信息,我们把表中的行标记为“未压缩”

(noncompressed):
ops$tkyte@ORA10G> 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@ORA10G> 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@ORA10G> 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..3

5 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..2

2 11..11.

CPU 时间(秒) 389 395 561

588

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

67/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..8

8 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@ORA10G> set autotrace traceonly explain
ops$tkyte@ORA10G> 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@ORA10G> 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@ORA10G> create index desc_t_idx on t(owner desc,object_type asc);
Index created.
ops$tkyte@ORA10G> exec dbms_stats.gather_index_stats( user, 'DESC_T_IDX' ); PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> 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是无可替代的。

11.2.4什么情况下应该使用 B*树索引?

我并不盲目地信息“经验“(所有规则都有例外),所以,对于什么时候该使用(和不该使用)B*树索 引,我没有什么经验可以告诉你。为了说明为什么在这方面不能提供经验,下面给出两种做法,这两种做 法同等有效:
仅当要通过索引访问表中很少的一部分行(只占一个很小的百分比)时,才使用B*树在列 上建立索引。
如果要处理表中的多行,而且可以使用索引而不用表,就可以使用一个B*树索引。 这两个规则看上去彼此存在冲突,不过在实际中,它们并不冲突,只是涵盖了两种完全不
同的情况。根据以上建议,有两种使用索引的方法:
索引用于访问表中的行:通过读索引来访问表中的行。此时你希望访问表中很少的一部分 行(只占一个很小的百分比)。
索引用于回答一个查询:索引包含了足够的信息来回答整个查询,我根本不用去访问表。 在这种情况下,索引则用作一个“较瘦“版本的表。
此 外,还有其他的一些做法,例如我们还可以使用一个索引来获取表中的所有行,包括索引本身中 没有的列。这看上去好像与前面的经验相左。交互式应用中可能就是 这种情况,你要得到并显示一些行, 然后再得到一些行,如此继续。为此,你可能希望优化查询以使最初的响应时间很短,而不是针对吞吐量 进行优化。

第一种情况(也就是说,为了访问表中很少的一部分行而使用索引)是指,如果你有一个表 T(还是 使用前面的表T),并有如下的一个查询计划:
ops$tkyte@ORA10G> set autotrace traceonly explain
ops$tkyte@ORA10G> select owner, status
2 from t
3 where owner = USER; Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1947 Bytes=25311)
11.0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=3 Card=1947 ....
2 1 INDEX (RANGE SCAN) OF 'DESC_T_IDX' (INDEX) (Cost=2 Card=8)
那你就应该只访问这个表的很少一部分行(只占一个很小的百分比)。这里要注意TABLE ACCESS BY INDEX ROWID后面的INDEX (RANGE SCAN)。这说明,Oracle会读索引,然后会对索引条目执行一个数据库 块读(逻辑或物理I/O)来得到行数据。如果你要通过索引访问T中的大量行(占很大的百分比),这就不 是最高效的方法了(稍后我们将定义多少才算是大百分比)。

在第二种情况下(也就是说,可以用索引而不必用表),你可以通过索引处理表中 100%的行(或者实 际上可以是任何比例)。使用索引可以只是为了创建一个“较瘦“版本的表。以下查询演示了这个概念:
ops$tkyte@ORA10G> select count(*)
2 from t
3 where owner = user; Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11. Card=11.Bytes=6)
11.0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=11. Card=1947
在此,只使用了索引来回答查询,现在访问多少行都没关系,因为我们只会使用索引。从查询计划可
以看出,这里从未访问底层表;我们只是扫描了索引结构本身。 重要的是,要了解这两个概念之间的区别。如果必须完成 TABLE ACCESS BY INDEX ROWID,就必须确
保只访问表中很少的一部分块(只占很小的百分比),这通常对应为很少的一部分行,或者需要能够尽可能
快地获取前面的几行(最终用户正在不耐烦地等着这几行)。如果我们访问的行太多(所占百分比过大,不 在总行数的1%~20%之间),那么与全表扫描相比,通过B*树索引来访问这些数据通常要花更长的时间。
对 于第二种类型的查询,即答案完全可以在索引中找到,情况就完全不同了。我们会读一个索引块, 选出许多“行“来处理,然后再到另一个索引块,如此继续,在此 从不访问表。某些情况下,还可以在索 引上执行一个快速全面扫描,从而更快地回答这类查询。快速全面扫描是指,数据库不按特定的顺序读取 索引块,而只是开始 读取它们。这里不再是把索引只用作一个索引,此时索引更像是一个表。如果采用快 速全面扫描,将不再按索引条目的顺序来得到行。
一般来讲,B*树索引会放在频繁使用查询谓词的列上,而且我们希望从表中只返回少量的数据(只占 很小的百分比),或者最终用户请求立即得到反馈。在一个瘦(thin)表(也就是说,只有很少的几个列, 或者列很小)上,这个百分比可能相当小。使用这个索引的查询应该只获取表中2%~3%(或者更少)的行 。 在一个胖(fat)表中(也就是说,这个表有很多列,或者列很宽),百分比则可能会上升到表的20%~25%。 以上建议不一定直接适用于每一个人;这个比例并不直观,但很精确。索引按索引键的顺序存储。索引会 按键的有序顺序进行访问。索引指向的块则随机地存储在堆中。因此,我们通过索引访问表时,会执行大 量分散、随机的I/O。这里“分散“(scattered)是指,索引会告诉我们读取块1,然后是块11.000、块
205、块321、块1、块11.032、块1,等等,它不会要求我们按一种连续的方式读取块1、然后是块2,接 着是块3.我们将以一种非常随意的方式读取和重新读取块。这种块I/O可能非常慢。
表中有100,000行,其中的20%就是2,000行。如果行大小约为 80字节,在一个块大小为 8KB的数据库中 , 每个块上则有大约100行。这说明,这个表有大约11.000个块。了解了执行情况,计算起来就非常容易了 。 我们要通过索引读取20,000行;这说明,大约是20,000个TABLE ACCESS BY ROWID操作。为此要处理 20,000 个表块来执行这个查询。不过,整个表才有大约11.000个块!最后会把表中的每一个块读取好处理20次。 即使把行的大小提高一个数量级,达到每行 800字节,这样每块有 11.行,现在表中就有 11.,000 个块。 要通过索引访问20,000行,仍要求我们把每一个块平均读取2次。在这种情况下,全表扫描就比使用索引 高效得多,因为每个块只会命中一次。如果查询使用这个索引来访问数据,效率都不会高,除非对于800 字节的行,平均只访问表中不到 5%的数据(这样一来,就只会访问大约 5,000个块),如果是 80字节的行 , 则访问的数据应当只占更小的百分比(大约0.5%或更少)。

1. 物理组织

数据在磁盘上如何物理地组织,对上述计算会有显著影响,因为这会大大影响索引访问的开销有多昂 贵(或者有多廉价)。假设有一个表,其中的行主键由一个序列来填充。向这个表增加数据时,序列号相邻 的行一般存储位置也会彼此“相邻“。
注意 如果使用诸如ASSM或多个freelist/freelist组等特性,也会影响数据在磁盘上的组织。这些 特性力图将数据发布开,这样可能就观察不到按主键组织的这种自然聚簇。

表会很自然地按主键顺序聚簇(因为数据或多或少就是已这种属性增加的)。当然,它不一定严格按 照键聚簇(要想做到这一点,必须使用一个IOT),但是,一般来讲,主键值彼此接近的行的物理位置也会 “靠“在一起。如果发出以下查询:
select * from T where primary_key between :x and :y
你想要的行通常就位于同样的块上。在这种情况下,即使要访问大量的行(占很大的百分比),索引

区间扫描可能也很有用。原因在于:我们需要读取和重新读取的数据库块很可能会被缓存,因为数据共同 放置在同一个位置(co-located)。另一方面,如果行并非共同存储在一个位置上,使用这个索引对性能来 讲可能就是灾难性的。只需一个小小的演示就能说明这一点。首先创建一个表,这个表主要按其主键排序::
ops$tkyte@ORA10G> create table colocated ( x int, y varchar2(80) );
Table created.
ops$tkyte@ORA10G> begin
2 for i in 1 .. 100000
3 loop
4 insert into colocated(x,y)
5 values (i, rpad(dbms_random.random,75,'*') );
6 end loop;

7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> alter table colocated
2 add constraint colocated_pk
3 primary key(x); Table altered.
ops$tkyte@ORA10G> begin
2 dbms_stats.gather_table_stats( user, 'COLOCATED', cascade=>true );
3 end;
4 /
PL/SQL procedure successfully completed.
这个表正好满足前面的描述,即在块大小为 8KB的一个数据库中,每块有大约100行。在这个表中,,

X = 11.2,3的行极有可能在同一个块上。仍取这个表,但有意地使它“无组织“。在COLOCATED表中,我 们创建一个Y列,它带有一个前导随机数,现在利用这一点使得数据”无组织“,即不再按主键排序:
ops$tkyte@ORA10G> create table disorganized
2 as
3 select x,y
4 from colocated
5 order by y; Table created.
ops$tkyte@ORA10G> alter table disorganized

2 add constraint disorganized_pk
3 primary key (x); Table altered.
ops$tkyte@ORA10G> begin
2 dbms_stats.gather_table_stats( user, 'DISORGANIZED', cascade=>true );
3 end;
4 /
PL/SQL procedure successfully completed.
可以证明,这两个表是一样的——这是一个关系数据库,所以物理组织对于返回的答案没有影响(至

少关于数据库理论的课程中就是这么教的)。实际上,尽管会返回相同的答案,但这两个表的性能特征却有 着天壤之别。给定同样的问题,使用同样的查询计划,查看TKPROF(SQL跟踪)输出,可以看到以下报告:
select * from colocated where x between 20000 and 40000
call count cpu elapsed disk query current rows

-------

------

--------

----------

----------

----------

--

--------

----------

Parse 5 0.00 0.00 0 0
0 0
Execute 5 0.00 0.00 0 0
0 0
Fetch 6675 0.59 0.60 0 14495
0 100005

-------

------

--------

----------

----------

----------

--

--------

----------

total 6685 0.59 0.60 0 14495
0 100005

Rows Row Source Operation
------- ---------------------------------------------------
20001 TABLE ACCESS BY INDEX ROWID COLOCATED (cr=2899 pr=0 pw=0 time=120134 us)
20001 INDEX RANGE SCAN COLOCATED_PK (cr=1374 pr=0 pw=0 time=40081 us)(object id...
********************************************************************************
select /*+ index( disorganized disorganized_pk ) */* from disorganized where x between 20000 and 40000
call count cpu elapsed disk query current rows

-------

------

--------

----------

----------

----------

--

--------

----------

Parse 5 0.00 0.00 0 0
0 0
Execute 5 0.00 0.00 0 0
0 0
Fetch 6675 0.85 0.87 0 106815 0
100005
------- ------ -------- ---------- ---------- ---------- --
-------- ----------
total 6685 0. 85 0.87 0 106815
0 100005
Rows Row Source Operation
------- ---------------------------------------------------
20001 TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=21363 pr=0 pw=0 time=220144 ...
20001 INDEX RANGE SCAN DISORGANIZED_PK (cr=1374 pr=0 pw=0 time=40311 us)(...
注意 我把每个查询运行了5次,从而得到每个查询的“平均“运行时间。
简直是难以置信。物理数据布局居然会带来这么大的差异!表11.-5对这些结果做了一个总结。
表11.-5 研究物理数据布局对索引访问开销的影响

表 CPU 时 间 逻辑 I/O

共同放置(Co-located) 0.59 秒

11.,495

无组织(Disorganized) 0.85 秒

106,815

共同放置表与无组织表的开销相对百分比 70%

11.%

在我的数据库中(块大小为 8KB),这些表的总块数如下:

ops$tkyte@ORA10G> select table_name, blocks
2 from user_tables
3 where table_name in ( 'COLOCATED', 'DISORGANIZED' );
TABLE_NAME BLOCKS
------------------------------ ---------- COLOCATED 1252
DISORGANIZED 1219
对无组织表的查询正如先前计算的一样:我们做了20,000次以上的逻辑I/(O
这里总共查询了100,000
个块,因为查询运行了5次,所以每次查询做了100,000/5 = 20,000次逻辑I/O)。每个块被处理了 20次 ! 另一方面,对于物理上共同放置的数据(COLOCATED),逻辑 I/O次 数则大幅下降。由此很好地说明了为什 么很难提供经验:在某种情况下,使用这个索引可能很不错,但在另外一种情况下它却不能很好地工作。 从生产系统转储数据 并加载到开发系统时也可以考虑这一点,因为这至少能从一定程度上回答如下问题: “为什么在这台机器上运行得完全不同?难道它们不一样吗?“不错,他们确实 不一样。

注意 第6章曾说过,逻辑I/O会增加,但这还只是冰山一角。每个逻辑I/O都涉及缓冲区缓存的一 个或多个锁存器。在一个多用户/CPU情况下,在我们自旋并等待锁存器时,与第一个查询相比, 第二个查询所用的CPU时间无疑会高出几倍。第二个示例查询不仅要完成更多的工作,而且无法 像第一个查询那样很好地扩展。
ARRAYSIZE对逻辑I/O的影响
有一个问题很有意思,这就是ARRAYSIZE对所执行逻辑I/O的影响。ARRAYSIZE是客户请求下一行时
Oracle向客户返回的行数。客户将缓存这些行,在向数据库请求下一个行集之前会先使用缓存的这些行,

ARRAYSIZE对查询执行的逻辑I/O可能有非常重要的影响,这是因为,如果必须跨数据库调用反复地访问 同一个块(也就是说,通过多个数据库调用反复访问同一个块,这里特别是指跨获取调用),Oracle就必 须一而再、再而三地从缓冲区缓存获取这个块。因此,如果一个调用从数据库请求100行,Oracle可能就 能够处理完一个数据库块,而无需再次获取这个块。如果你一次请求11.行,Oracle就必须反复地获得同 一个块来获取同样的行集。
在这一节前面的例子中,我们使用了SQL*Plus的默认批量获取大小(11.行,如果把获取的总行数除 以获取调用的个数,所得到的结果将非常接近11.)。我们在每次获取11.行和每次获取100行的情况下执 行前面的查询,从而做一个比较,会观察到对于COLOCATED表有以下结果:
select * from colocated a15 where x between 20000 and 40000
Rows Row Source Operation
------- ---------------------------------------------------
20001 TABLE ACCESS BY INDEX ROWID COLOCATED (cr=2899 pr=0 pw=0 time=120125...
20001 INDEX RANGE SCAN COLOCATED_PK (cr=1374 pr=0 pw=0 time=40072 us)(...
select * from colocated a100 where x between 20000 and 40000
Rows Row Source Operation
------- ---------------------------------------------------
20001 TABLE ACCESS BY INDEX ROWID COLOCATED (cr=684 pr=0 pw=0 ...)
20001 INDEX RANGE SCAN COLOCATED_PK (cr=245 pr=0 pw=0 ...
执行第一个查询时ARRAYSIZE为11.,Row Source Operation中(cr-nnnn)值显示出,对这个索引 执行了 11.374 个逻辑 I/O,并对表执行了 11.625 个逻辑 I/O(2,899-11.374;这些数在 Row Source Operation步骤中加在了一起,即2,899)。把 ARRAYSIZE从11.增加到100时,对索引的逻辑I/O数减至
245,这是因为,不必每11.行就从缓冲区缓存重新读取索引叶子块,而是每100行才读取一次。为了说明 这一点,假设每个叶子块上能存储200行。如果扫描索引时每次只能读取11.行,则必须把第一个叶子块 获取11.次才能得到其中的全部200行。另一方面,如果每次批量获取100行,只需要将这个叶子块从缓 冲区缓存中获取两次就能得到其中的所有行。
对于表块也存在这种情况。由于表按索引键同样的顺序排序,所以会更少地获取各个表块,原因是每 个获取调用能从表中得到更多的行。
这么说来,如果增加ARRAYSIZE对COLOCATED表很合适,那么这对于 DISORGANIZED表也同样会很好, 是这样吗?并非如此。DISORGANIZED表的相应结果如下所示:
select /*+ index( a15 disorganized_pk ) */ *

from disorganized a15 where x between 20000 and 40000
Rows Row Source Operation
------- ---------------------------------------------------
20001 TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=21357 pr=0 pw=0 ...
20001 INDEX RANGE SCAN DISORGANIZED_PK (cr=1374 pr=0 pw=0 ...
select /*+ index( a100 disorganized_pk ) */ *
from disorganized a100 where x between 20000 and 40000
Rows Row Source Operation
------- ---------------------------------------------------
20001 TABLE ACCESS BY INDEX ROWID OBJ#(75652) (cr=20228 pr=0 pw=0 ...
20001 INDEX RANGE SCAN OBJ#(75653) (cr=245 pr=0 pw=0 time=20281 us)(... 索引有关的结果并无不同,这是有道理的,因为不论表如何组织,索引中存储的数据都一样,执行一
次查询时,如果从11.增加到100,对索引执行的逻辑I/O总次数在调整ARRAYSIZE前后并没有太大差别: 分别是21,357和20,281。为什么呢?原因是对表执行的逻辑I/O次数根本没有变化,如果把每个查询执 行的逻辑I/O总次数减去对索引执行的逻辑I/O次数,就会发现这两个查询对表执行的逻辑I/O此时都是
11.,983。这是因为,每次我们希望从数据库得到N行时,其中的任何两行在同一个块上的机率非常小,所 以通过一个调用就从一个表块中得到多行是不可能的。
我见过的每一种与Oracle交互的专业编程语言都实现了这种批量获取(array fetching)的概念。 在 PL/SQL中,可以使用 BULK COLLECT,也可以依靠为隐式游标 for循环执行的隐式批量获取(一次 100 行)来实现。在 Java/JDBC中,连接(connect)或语句(statement)对象上有一个预获取(prefetch) 方法。Oracle调用接口(Oracle Call Interface,即OCI,这是一个C API)与Pro*C类似,允许在程序 中设置预获取大小。可以看到,这对查询执行的逻辑I/O次数会有重要而且显著的影响,值得注意。

在这个例子的最后,来看一下全面扫描DISORGANIZED表时会发生什么:
select * from disorganized where x between 20000 and 40000

call count cpu elapsed disk query current rows

-------

------

--------

----------

----------

----------

--

--------

----------

Parse 5 0.00 0.00 0 0
0 0
Execute 5 0.00 0.00 0 0
0 0
Fetch 6675 0.53 0.54 0 12565
0 100005

-------

------

--------

----------

----------

----------

--

--------

----------

total 6685 0.53 0. 54 0 12565 0 100005
Rows Row Source Operation
------- ---------------------------------------------------
20001 TABLE ACCESS FULL DISORGANIZED (cr=2513 pr=0 pw=0 time=60115 us)
由此显示出,在这个特殊的例子中,根据数据在磁盘上的物理存储的方式,非常合适采用全表扫描。
这就带来一个问题:“为什么优化器不先对这个查询执行全面扫描呢?“这么说吧,如果按照它自己本来的 设计,确实会先执行全面扫描,但是在对 DISORGANIZED的第一个示例查询中,我有意地为查询提供了提示 , 告诉优化器要构造一个使用这个索引的计划。在第二个例子中,则是让优化器自己来选择最佳的字体计划。

2. 聚簇因子

接下来,我们来看 Oracle 所用的一些信息。我们要特别查看 USER_INDEXES 视图中的 CLUSTERING_FACTOR列。Oracle reference手册指出了这个列有以下含义:
根据索引的值指示表中行的有序程度: 如果这个值与块数接近,则说明表相当有序,得到了很好的组织,在这种情况下,同一个
叶子块中的索引条目可能指向同一个数据块上的行。
如果这个值与行数接近,表的次序可能就是非常随机的。在这种情况下,同一个叶子块上 的索引条目不太可能指向同一个数据块上的行。

可以把聚簇因子(clustering factor)看作是通过索引读取整个表时对表执行的逻辑I/O次数。也 就是说,CLUSTERING_FACTOR指示了表相对于索引本身的有序程度,查看这些索引时,会看到以下结果:
ops$tkyte@ORA10G> select a.index_name,

2 b.num_rows,
3 b.blocks,
4 a.clustering_factor
5 from user_indexes a, user_tables b
6 where index_name in ('COLOCATED_PK', 'DISORGANIZED_PK' )
7 and a.table_name = b.table_name
8 /
INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
--------------- ---------- ----------
-----------------
COLOCATED_PK 100000 1252
1190
DISORGANIZED_PK 100000 1219
99932
注意 对于这一节的例子,我使用了一个ASSM管理的表空间,由此可以解释为什么COLOCATED表的聚
簇因子小于表中的块数。COLOCATED表中在HWM之下有一些未格式化的块,其中未包含数据,而 且ASSM本身也使用了一些块来管理空间,索引区间扫描中不会读取这些块。第11.章更详细地解 释了HWM和ASSM。
所以数据库说:“如果通过索引COLOCATED_PK从头到尾地读取COLOCATED表中的每一行,就要执行

11.190次I/O。不过,如果我们对DISORGANIZED表做同样的事情,则会对这个表执行 99,932次I/O。“之 所以存在这么大的区别,原因在于,当Oracle对索引结构执行区间扫描时,如果它发现索引中的下一行于 前一行在同一个数据库块上,就不会再执行另一个I/O从缓冲区缓存中获得表块。它已经有表块的一个句 柄,只需直接使用就可以了。不过,如果下一行不在同一个块上,就会释放当前的这个块,而执行另一个 I/O从缓冲区缓存获取要处理的下一个块。因此,在我们对索引执行区间扫描时,COLOCATED_PK索引会发 现下一行几乎总于前一行在同一个块上。DISORGANIZED_PK索引发现的情况则恰好相反。实际上,你会看 到这个测量相当准确。通过使用提示,让优化器使用索引全面扫描来读取整个表,再统计非NULL的Y值个 数,就能看到通过索引读取整个表需要执行多少次I/O:
select count(Y)
from
(select /*+ INDEX(COLOCATED COLOCATED_PK) */ * from colocated)

call count cpu elapsed disk query current row s
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 2 0.11. 0.11. 0 1399
0 1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.11. 0.11. 0 1399
0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1399 pr=0 pw=0 time=160325 us)
100000 TABLE ACCESS BY INDEX ROWID COLOCATED (cr=1399 pr=0 pw=0 time=500059 us)
100000 INDEX FULL SCAN COLOCATED_PK (cr=209 pr=0 pw=0 time=101057 us)(object ...
********************************************************************************
select count(Y)
from
(select /*+ INDEX(DISORGANIZED DISORGANIZED_PK) */ * from disorganized)
call count cpu elapsed disk query current row s
------- ------ -------- ---------- ---------- ---------- ----------
----------

Parse 1 0.00 0.00 0 0
0 0
Execute 1 0.00 0.00 0 0
0 0
Fetch 2 0.34 0.40 0 100141 0
1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.34 0.40 0 100141 0
1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=100141 pr=0 pw=0 time=401109 us)
100000 TABLE ACCESS BY INDEX ROWID OBJ#(66615) (cr=100141 pr=0 pw=0 time=800058...
100000 INDEX FULL SCAN OBJ#(66616) (cr=209 pr=0 pw=0 time=101129 us)(object...
在这两种情况下,索引都需要执行209次逻辑I/O(Row Source Operation行中的cr-209)。如果将

一致读(consistent read)总次数减去 209,只测量对表执行的I/O次数,就会发现所得到的数字与各个 索引的聚簇因子相等。COLOCATED_PK是“有序表“的一个经典例子,DISORGANIZE_PK则是一个典型的”表 次序相当随机“的例子。现在来看看这对优化器有什么影响。如果我们想获取25,000 行,Oracle对两个 索引都会选择全表扫描(通过索引获取25%的行不是最优计划,即使是对很有序的表也是如此)。不过,如 果只选择表数据的11.%,就会观察到以下结果:
ops$tkyte@ORA10G> select * from colocated where x between 20000 and 30000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=143 Card=10002 Bytes=800160)
11.0 TABLE ACCESS (BY INDEX ROWID) OF 'COLOCATED' (TABLE) (Cost=143 ...
2 1 INDEX (RANGE SCAN) OF 'COLOCATED_PK' (INDEX (UNIQUE)) (Cost=22
...
ops$tkyte@ORA10G> select * from disorganized where x between 20000 and 30000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=337 Card=10002 Bytes=800160)
11.0 TABLE ACCESS (FULL) OF 'DISORGANIZED' (TABLE) (Cost=337 Card=10002 ...

这里的表结构和索引与前面完全一样,但聚簇因子有所不同。在这种情况下,优化器为COLOCATED
表选择了一个索引访问计划,而对DISORGANIZED表选择了一个全面扫描访问计划。要记住,11.%并不是一 个阀值,它只是一个小于25%的数,而且在这里会导致对COLOCATED表的一个索引区间扫描。
以 上讨论的关键点是,索引并不一定总是合适的访问方法。优化器也许选择不使用索引,而且如前 面的例子所示,这种选择可能很正确。影响优化器是否使用索引的因 素有很多,包括物理数据布局。因此 , 你可能会矫枉过正,力图重建所有的表来使所有索引有一个好的聚簇因子,但是在大多数情况下这可能只 会浪费时间。只有当 你在对表中的大量数据(所占百分比很大)执行索引区间扫描时,这才会产生影响。 另外必须记住,对于一个表来说,一般只有一个索引能有合适的聚簇因子!表中 的行可能只以一种方式排 序。在前面所示的例子中,如果Y列上还有一个索引,这个索引在 COLOCATED表中可能就不能很好地聚簇, 而在DISORGANIZED表中则恰好相反。如果你认为数据物理聚簇很重要,可以考虑使用一个IOT、B*树聚簇 , 或者在连续地重建表时考虑散列聚簇。

11.2.5 B*树小结

B*树索引是到目前为止Oracle数据库中最常用的索引结构,对B*树索引的研究和了解也最为深入。 它们是绝好的通用索引机制。在访问时间方面提供了很大的可扩缩性,从一个11.000行的索引返回数据所 用的时间与一个100,000行的索引结构中返回数据的时间是一样的。
什么时候建立索引,在哪些列上建立索引,你的设计中必须注意这些问题。索引并不一定就意味着更 快的访问;实际上你会发现,在许多情况下,如果Oracle使 用索引,反而会使性能下降。这实际上两个 因素的一个函数,其中一个因素是通过索引需要访问表中多少数据(占多大的百分比),另一个因素是数据 如何布局。如 果能完全使用索引“回答问题“(而不用表),那么访问大量的行(占很大的百分比)就是有 意义的,因为这样可以避免读表所带来的额外的分散I/O。如果使用索引来访问表,可能就要确保只处理 整个表中的很少一部分(只占很小的百分比)。
应该在应用的设计期间考虑索引的设计和实现,而不要事后才想起来(我就经常见到这种情况)。如 果对如何访问数据做了精心的计划和考虑,大多数情况下就能清楚地知道需要什么索引。