B*树索引

最新推荐文章于 2024-05-14 17:59:58 发布

weixin_34342992

最新推荐文章于 2024-05-14 17:59:58 发布

阅读量214

点赞数

文章标签：数据库嵌入式数据结构与算法

原文链接：https://my.oschina.net/u/1862478/blog/1836205

版权

2019独角兽企业重金招聘Python工程师标准>>>

索引是应用设计和开发的一个重要方面。

如果有太多的索引，DML的性能就会受到影响。

如果索引太少，又会影响查询（包括插入、更新和删除）的性能。

Oracle提供了多种不同类型的索引以供使用：

B*树索引：B*树的构造类似于二叉树，能根据键提供一行或一个行集的快速访问，通常只需很少的读操作就能找到正确的行。”B*树“中的”B“不代表二叉（binary），而代表平衡（balanced）。B*树索引有以下子类型：

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

B*树聚簇索引（B*tree cluster index）这些是传统B*树索引的一个变体（只是稍有变化）。B*树聚簇索引用于对聚簇键建立索引。在传统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文本索引就使用了一组表来实现其索引概念。

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

B*树索引

这是数据库中最常用的一类索引结构。其实现与二叉查找树很相似。其目标是尽可能减少Oracle查找数据所花费的时间。

这个树最底层的块称为叶子节点（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行的表，其主键索引建立在一个数字列上：

scott@ORCL>select index_name, blevel, num_rows
  2  from user_indexes
  3  where table_name = 'BIG_TABLE';

INDEX_NAME                         BLEVEL   NUM_ROWS
------------------------------ ---------- ----------
BIG_TABLE_PK                            1     100000

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

scott@ORCL>select id from big_table where id = 42;

        ID
----------
        42


执行计划
----------------------------------------------------------
Plan hash value: 1688966252

--------------------------------------------------------------------------------
--
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time
 |
--------------------------------------------------------------------------------
--
|   0 | SELECT STATEMENT  |              |     1 |     5 |     1   (0)| 00:00:01
 |
|*  1 |  INDEX UNIQUE SCAN| BIG_TABLE_PK |     1 |     5 |     1   (0)| 00:00:01
 |
--------------------------------------------------------------------------------
--

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=42)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        521  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott@ORCL>select id from big_table where id = 12345;

        ID
----------
     12345


执行计划
----------------------------------------------------------
Plan hash value: 1688966252

--------------------------------------------------------------------------------
--
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time
 |
--------------------------------------------------------------------------------
--
|   0 | SELECT STATEMENT  |              |     1 |     5 |     1   (0)| 00:00:01
 |
|*  1 |  INDEX UNIQUE SCAN| BIG_TABLE_PK |     1 |     5 |     1   (0)| 00:00:01
 |
--------------------------------------------------------------------------------
--

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=12345)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          1  physical reads
          0  redo size
        523  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott@ORCL>select id from big_table where id = 1234567;

未选定行


执行计划
----------------------------------------------------------
Plan hash value: 1688966252

--------------------------------------------------------------------------------
--
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time
 |
--------------------------------------------------------------------------------
--
|   0 | SELECT STATEMENT  |              |     1 |     5 |     1   (0)| 00:00:01
 |
|*  1 |  INDEX UNIQUE SCAN| BIG_TABLE_PK |     1 |     5 |     1   (0)| 00:00:01
 |
--------------------------------------------------------------------------------
--

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=1234567)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          1  physical reads
          0  redo size
        331  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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

1 索引键压缩

对于B*树索引，从串联（多列）索引去除冗余。
压缩键索引（compressed key index）的基本概念是，每个键条目分解为两个部分：“前缀”和“后缀”。前缀建立在串联索引（concatenated index）的前几列上，这些列有许多重复的值。后缀则在索引键的后几列上，这是前缀所在索引条目中的惟一部分（即有区别的部分）。

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

sys@ORCL>create table t
  2  as
  3  select * from all_objects;

表已创建。

sys@ORCL>create index t_idx on
  2  t(owner,object_type,object_name);

索引已创建。

sys@ORCL>analyze index t_idx validate structure;

索引已分析

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

sys@ORCL>create table idx_stats
  2  as
  3  select 'noncompressed' what, a.*
  4  from index_stats a;

表已创建。

现在可以看到，OWNER部分重复了多次，这说明，这个索引中的一个索引块可能有数十个条目，

可以从中抽取出重复的OWNER列

所有者（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，应该能观察到以下信息：

sys@ORCL>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_CM	PR_PCTSAVE
-------------			------	-------		------- ----------- 	-------------- 	----------------
noncompressed     3				503          3     4048096			2								28
compress 1        3				446          3     3590312			2								19
compress 2        3				359          3     2894660			2								0
compress 3        3				562          4     4525880			2								35

可以看到，COMPRESS 1索引的大小大约是无压缩索引的89%（通过比较BTREE_SPACE得出）。叶子块数大幅度下降。更进一步，使用COMPRESS 2时，节省的幅度更为显著。所得到的索引大约是原索引（无压缩索引）的72%.实际上，利用列OPT_CMPR_PCTSAVE的信息（这代表最优的节省压缩百分比（optimum compression percent saved）或期望从压缩得到的节省幅度）。我们可以猜测出COMPRESS 2索引的大小：

sys@ORCL>select 4048096*(0.28) from dual;

4048096*(0.28)
--------------
    1133466.88

sys@ORCL>select 4048096-2894660 from dual;

4048096-2894660
---------------
        1153436

对无压缩索引执行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操作很多，使用压缩键索引就能加快处理速度。

2 反向键索引

B*树索引的另一个特点是能够将索引键“反转”。实现B*树索引的目的是为了减少“右侧”索引中对索引叶子块的竞争，比如在一个Oracle RAC环境中，某些列用一个序列值或时间戳填充，这些列上建立的索引就属于“右侧”（right-hand-side）索引。

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

反向键可以用作一种减少竞争的方法（即使只有一个Oracle实例）。反向键主要用于缓解忙索引右侧的缓冲区忙等待。

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

scott@ORCL>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: 195,10,2,2
     90102 Typ=2 Len=4: 195,10,2,3
     90103 Typ=2 Len=4: 195,10,2,4

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

scott@ORCL>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'),11.)
---------- ----------------------------
     90101 Typ=96 Len=5: 49,48,49,48,57
     90102 Typ=96 Len=5: 50,48,49,48,57
     90103 Typ=96 Len=5: 51,48,49,48,57

这些数彼此之间最后会“相距很远“。这样访问同一个块（最右边的块）的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来避免表块的竞争，这样可以把索引块的竞争隔离开）：

scott@ORCL>create table t tablespace assm
  2  as
  3  select 0 id, a.*
  4  from all_objects a
  5  where 1<0;

表已创建。

scott@ORCL>alter table t
  2  add constraint t_pk
  3  primary key (id)
  4  using index (create index t_pk on t(id) REVERSE tablespace assm);

表已更改。

scott@ORCL>create sequence s cache 1000;

序列已创建。

scott@ORCL>create or replace procedure do_sql
  2  as
  3  begin
  4     for x in ( select rownum r, all_objects.* from all_objects )
  5     loop
  6             insert into t
  7                     ( id, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  8                     OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
  9                     LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
 10                     GENERATED, SECONDARY )
 11             values
 12                     ( s.nextval, x.OWNER, x.OBJECT_NAME, x.SUBOBJECT_NAME,
 13                     x.OBJECT_ID, x.DATA_OBJECT_ID, x.OBJECT_TYPE, x.CREATED,

 14                     x.LAST_DDL_TIME, x.TIMESTAMP, x.STATUS, x.TEMPORARY,
 15                     x.GENERATED, x.SECONDARY );
 16             if ( mod(x.r,100) = 0 )
 17             then
 18                     commit;
 19             end if;
 20     end loop;
 21     commit;
 22  end;
 23  /

过程已创建。

使用了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;

3 降序索引

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

例如，如果使用先前的表T，并如下查询这个表：

scott@ORCL>set autotrace traceonly explain
scott@ORCL>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;

执行计划
----------------------------------------------------------
Plan hash value: 961378228

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    28 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |     1 |    28 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    28 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE" IS NOT NULL AND "OWNER">='T' AND
              "OWNER"<='Z')


scott@ORCL>create index desc_t_idx on t(owner desc,object_type asc);

索引已创建。

scott@ORCL>exec dbms_stats.gather_index_stats( user, 'DESC_T_IDX' );

PL/SQL 过程已成功完成。

scott@ORCL>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;

执行计划
----------------------------------------------------------
Plan hash value: 2494308350

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     1 |    28 |     0   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| DESC_T_IDX |     1 |    28 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access(SYS_OP_DESCEND("OWNER")>=HEXTORAW('A5FF')  AND
              SYS_OP_DESCEND("OWNER")<=HEXTORAW('ABFF') )
       filter("OBJECT_TYPE" IS NOT NULL AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))>='T' AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))<='Z')

4 什么情况下应该使用B*树索引？

仅当要通过索引访问表中很少的一部分行（只占一个很小的百分比）时，才使用B*树在列上建立索引。
如果要处理表中的多行，而且可以使用索引而不用表，就可以使用一个B*树索引。
根据以上建议，有两种使用索引的方法：
索引用于访问表中的行：通过读索引来访问表中的行。此时你希望访问表中很少的一部分行（只占一个很小的百分比）。
索引用于回答一个查询：索引包含了足够的信息来回答整个查询，我根本不用去访问表。在这种情况下，索引则用作一个“较瘦“版本的表。

第一种情况（也就是说，为了访问表中很少的一部分行而使用索引）是指，如果有一个表T（还是使用前面的表T），并有如下的一个查询计划：

scott@ORCL>set autotrace traceonly explain
scott@ORCL>select owner, status
  2  from t
  3  where owner = USER;

执行计划
----------------------------------------------------------
Plan hash value: 1049179052

--------------------------------------------------------------------------------
----------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
Time     |
--------------------------------------------------------------------------------
----------
|   0 | SELECT STATEMENT            |            |     1 |    22 |     0   (0)|
00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T          |     1 |    22 |     0   (0)|
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | DESC_T_IDX |     1 |       |     0   (0)|
00:00:01 |
--------------------------------------------------------------------------------
----------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(SYS_OP_DESCEND("OWNER")=SYS_OP_DESCEND(USER@!))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))=USER@!)

那你就应该只访问这个表的很少一部分行（只占一个很小的百分比）。这里要注意TABLE ACCESS BY INDEX ROWID后面的INDEX (RANGE SCAN)。这说明，Oracle会读索引，然后会对索引条目执行一个数据库块读（逻辑或物理I/O）来得到行数据。如果你要通过索引访问T中的大量行（占很大的百分比），这就不是最高效的方法了。

在第二种情况下（也就是说，可以用索引而不必用表），你可以通过索引处理表中100%的行（或者实际上可以是任何比例）。使用索引可以只是为了创建一个“较瘦“版本的表。以下查询演示了这个概念：

scott@ORCL>select count(*)
  2  from t
  3  where owner = user;

执行计划
----------------------------------------------------------
Plan hash value: 2221593640

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

| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |            |     1 |    17 |     0   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |            |     1 |    17 |            |          |

|*  2 |   INDEX RANGE SCAN| DESC_T_IDX |     1 |    17 |     0   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(SYS_OP_DESCEND("OWNER")=SYS_OP_DESCEND(USER@!))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))=USER@!)

在此，只使用了索引来回答查询，现在访问多少行都没关系，因为我们只会使用索引。从查询计划可以看出，这里从未访问底层表；我们只是扫描了索引结构本身。

如果必须完成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可能非常慢。

1. 物理组织

假设有一个表，其中的行主键由一个序列来填充。向这个表增加数据时，序列号相邻的行一般存储位置也会彼此“相邻“。

表会很自然地按主键顺序聚簇（因为数据或多或少就是已这种属性增加的）。当然，它不一定严格按照键聚簇（要想做到这一点，必须使用一个IOT），但是，一般来讲，主键值彼此接近的行的物理位置也会“靠“在一起。如果发出以下查询：

select * from T where primary_key between :x and :y

你想要的行通常就位于同样的块上。在这种情况下，即使要访问大量的行（占很大的百分比），索引区间扫描可能也很有用。原因在于：我们需要读取和重新读取的数据库块很可能会被缓存，因为数据共同放置在同一个位置（co-located）。另一方面，如果行并非共同存储在一个位置上，使用这个索引对性能来讲可能就是灾难性的。

只需一个小小的演示就能说明这一点。首先创建一个表，这个表主要按其主键排序：

system@ORCL>create table colocated ( x int, y varchar2(80) );

表已创建。

system@ORCL>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 过程已成功完成。

system@ORCL>alter table colocated
  2  add constraint colocated_pk
  3  primary key(x);

表已更改。

system@ORCL>begin
  2     dbms_stats.gather_table_stats( user, 'COLOCATED', cascade=>true );
  3  end;
  4  /

PL/SQL 过程已成功完成。

这个表正好满足前面的描述，即在块大小为8KB的一个数据库中，每块有大约100行。在这个表中，，X = 11.2,3的行极有可能在同一个块上。仍取这个表，但有意地使它“无组织“。在COLOCATED表中，我们创建一个Y列，它带有一个前导随机数，现在利用这一点使得数据”无组织“，即不再按主键排序：

system@ORCL>create table disorganized
  2  as
  3  select x,y
  4  from colocated
  5  order by y;

表已创建。

system@ORCL>alter table disorganized
  2  add constraint disorganized_pk
  3  primary key (x);

表已更改。

system@ORCL>begin
  2     dbms_stats.gather_table_stats( user, 'DISORGANIZED', cascade=>true );
  3  end;
  4  /

PL/SQL 过程已成功完成。

可以证明，这两个表是一样的——这是一个关系数据库，所以物理组织对于返回的答案没有影响（至少关于数据库理论的课程中就是这么教的）。实际上，尽管会返回相同的答案，但这两个表的性能特征却有着天壤之别。给定同样的问题，使用同样的查询计划，查看TKPROF（SQL跟踪）输出，可以看到以下报告：

                                                     CPU      Elapsd
  Physical Rds   Executions  Rds per Exec   %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
          1,063            3          354.3   78.3     0.28      0.66 2088343704
select * from colocated where x between 20000 and 40000

            942            1          942.0   69.4     0.31      1.27 3151995455
select /*+ index( disorganized disorganized_pk ) */* from disorg
anized where x between 20000 and 40000

...

                                                     CPU      Elapsd     Old
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
         21,374            1       21,374.0   65.1     0.31      1.27 3151995455
select /*+ index( disorganized disorganized_pk ) */* from disorg
anized where x between 20000 and 40000

          6,673            3        2,224.3   20.3     0.28      0.66 2088343704
select * from colocated where x between 20000 and 40000

在我的数据库中（块大小为8KB），这些表的总块数如下：

system@ORCL>select table_name, blocks
  2  from user_tables
  3  where table_name in ( 'COLOCATED', 'DISORGANIZED' );
TABLE_NAME                        BLOCKS
------------------------------ ----------
COLOCATED                            1191
DISORGANIZED                         1191

每个逻辑I/O都涉及缓冲区缓存的一个或多个锁存器。在一个多用户/CPU情况下，在我们自旋并等待锁存器时，与第一个查询相比，第二个查询所用的CPU时间无疑会高出几倍。

ARRAYSIZE对逻辑I/O的影响

ARRAYSIZE是客户请求下一行时Oracle向客户返回的行数。客户将缓存这些行，在向数据库请求下一个行集之前会先使用缓存的这些行，ARRAYSIZE对查询执行的逻辑I/O可能有非常重要的影响，这是因为，如果必须跨数据库调用反复地访问同一个块（也就是说，通过多个数据库调用反复访问同一个块，这里特别是指跨获取调用），Oracle就必须一而再、再而三地从缓冲区缓存获取这个块。

聚簇因子

USER_INDEXES视图中的CLUSTERING_FACTOR列

根据索引的值指示表中行的有序程度：
如果这个值与块数接近，则说明表相当有序，得到了很好的组织，在这种情况下，同一个叶子块中的索引条目可能指向同一个数据块上的行。
如果这个值与行数接近，表的次序可能就是非常随机的。在这种情况下，同一个叶子块上的索引条目不太可能指向同一个数据块上的行。

可以把聚簇因子（clustering factor）看作是通过索引读取整个表时对表执行的逻辑I/O次数。也就是说，CLUSTERING_FACTOR指示了表相对于索引本身的有序程度，查看这些索引时，会看到以下结果：

system@ORCL>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       1191              1190
DISORGANIZED_PK                    100000       1191             99915

所以数据库说：“如果通过索引COLOCATED_PK从头到尾地读取COLOCATED表中的每一行，就要执行1190次I/O。不过，如果我们对DISORGANIZED表做同样的事情，则会对这个表执行99,915次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)

    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
      0.11            1       0.11   24.1       0.39           1,399 2513878408


  Elapsed                Elap per            CPU                        Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)  Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
      0.39            1       0.39   17.8       0.11           1,399 2513878408


                                                     CPU      Elapsd     Old
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
          1,399            1        1,399.0   15.3     0.11      0.39 2513878408


                                                     CPU      Elapsd
  Physical Rds   Executions  Rds per Exec   %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
          1,399            1        1,399.0   52.5     0.11      0.39 2513878408


                                                CPU per    Elap per     Old
 Executions   Rows Processed   Rows per Exec    Exec (s)   Exec (s)  Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
           1               1              1.0       0.11        0.39  2513878408


                           % Total    Old
 Parse Calls   Executions   Parses Hash Value
------------ ------------ -------- ----------
           1            1     1.56 2513878408

select count(Y)
from
(select /*+ INDEX(DISORGANIZED DISORGANIZED_PK) */ * from disorganized)

    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
      0.31            1       0.31   52.6       1.29         100,175  204991286
   
      
  Elapsed                Elap per            CPU                        Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)  Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
      1.29            1       1.29   70.2       0.31           1,403  204991286
      
      
                                                     CPU      Elapsd     Old
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
        100,175            1      100,175.0   95.5     0.31      1.29  204991286
        
        
                                                     CPU      Elapsd
  Physical Rds   Executions  Rds per Exec   %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
          1,403            1        1,403.0   98.4     0.31      1.29  204991286
          

                                                CPU per    Elap per     Old
 Executions   Rows Processed   Rows per Exec    Exec (s)   Exec (s)  Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
           1               1              1.0       0.31        1.29   204991286
          
           
                           % Total    Old
 Parse Calls   Executions   Parses Hash Value
 ------------ ------------ -------- ----------
  1            1     1.25  204991286

system@ORCL>SET AUTOTRACE ON
system@ORCL>select count(Y) from
  2  (select /*+ INDEX(COLOCATED COLOCATED_PK) */ * from colocated)
  3  ;
  COUNT(Y)
----------
    100000


执行计划
----------------------------------------------------------
Plan hash value: 3483305348

------------------------------------------------------------------------------
---------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%C
PU)| Time     |
------------------------------------------------------------------------------
---------------
|   0 | SELECT STATEMENT             |              |     1 |    76 |  1402
(1)| 00:00:17 |
|   1 |  SORT AGGREGATE              |              |     1 |    76 |
   |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| COLOCATED    |   100K|  7421K|  1402
(1)| 00:00:17 |
|   3 |    INDEX FULL SCAN           | COLOCATED_PK |   100K|       |   211
(1)| 00:00:03 |
------------------------------------------------------------------------------
---------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1399  consistent gets
       1399  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

system@ORCL>select count(Y) from
  2  (select /*+ INDEX(DISORGANIZED DISORGANIZED_PK) */ * from disorganized) ;
  COUNT(Y)
----------
    100000


执行计划
----------------------------------------------------------
Plan hash value: 102678025

------------------------------------------------------------------------------
------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost
(%CPU)| Time     |
------------------------------------------------------------------------------
------------------
|   0 | SELECT STATEMENT             |                 |     1 |    76 |   100
K  (1)| 00:20:03 |
|   1 |  SORT AGGREGATE              |                 |     1 |    76 |
      |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DISORGANIZED    |   100K|  7421K|   100
K  (1)| 00:20:03 |
|   3 |    INDEX FULL SCAN           | DISORGANIZED_PK |   100K|       |   211
   (1)| 00:00:03 |
------------------------------------------------------------------------------
------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     100124  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

tkprof 报告如下：

SQL ID: db146dyxc9ctw
Plan Hash: 3483305348
select count(Y) 
from
 (select /*+ INDEX(COLOCATED COLOCATED_PK) */ * from colocated) 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       0.03          0       1399          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.03          0       1399          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 5  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1399 pr=0 pw=0 time=0 us)
 100000   TABLE ACCESS BY INDEX ROWID COLOCATED (cr=1399 pr=0 pw=0 time=40664 us cost=1402 size=7600000 card=100000)
 100000    INDEX FULL SCAN COLOCATED_PK (cr=209 pr=0 pw=0 time=13426 us cost=211 size=0 card=100000)(object id 81368)

********************************************************************************

select count(Y) from
(select /*+ INDEX(DISORGANIZED DISORGANIZED_PK) */ * from disorganized) 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.12       0.12          0     100124          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.12       0.12          0     100124          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=100124 pr=0 pw=0 time=0 us)
 100000   TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=100124 pr=0 pw=0 time=140919 us cost=100177 size=7600000 card=100000)
 100000    INDEX FULL SCAN DISORGANIZED_PK (cr=209 pr=0 pw=0 time=17519 us cost=211 size=0 card=100000)(object id 81370)

在这两种情况下，索引都需要执行209次逻辑I/O（Row Source Operation行中的cr-209）。如果
将一致读（consistent read）总次数减去209，只测量对表执行的I/O次数，就会发现所得到的数字与各个索引的聚簇因子相等。COLOCATED_PK是“有序表“的一个经典例子，DISORGANIZE_PK则是一个典型的”表次序相当随机“的例子。现在来看看这对优化器有什么影响。如果我们想获取25,000行，Oracle对两个索引都会选择全表扫描（通过索引获取25%的行不是最优计划，即使是对很有序的表也是如此）。不过，如果只选择表数据的11%，就会观察到以下结果：

select * 
from
 colocated where x between 20000 and 30000


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      668      0.00       0.02          0       1452          0       10001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      670      0.00       0.03          0       1452          0       10001

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5  

Rows     Row Source Operation
-------  ---------------------------------------------------
  10001  TABLE ACCESS BY INDEX ROWID COLOCATED (cr=1452 pr=0 pw=0 time=27435 us cost=142 size=810162 card=10002)
  10001   INDEX RANGE SCAN COLOCATED_PK (cr=689 pr=0 pw=0 time=13544 us cost=22 size=0 card=10002)(object id 81368)

********************************************************************************
select * 
from
 disorganized where x between 20000 and 30000


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      668      0.03       0.06          1       1855          0       10001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      670      0.03       0.06          1       1855          0       10001

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5  

Rows     Row Source Operation
-------  ---------------------------------------------------
  10001  TABLE ACCESS FULL DISORGANIZED (cr=1855 pr=1 pw=0 time=25769 us cost=326 size=810162 card=10002)

这里的表结构和索引与前面完全一样，但聚簇因子有所不同。在这种情况下，优化器为COLOCATED表选择了一个索引访问计划，而对DISORGANIZED表选择了一个全面扫描访问计划。要记住，11%并不是一个阀值，它只是一个小于25%的数，而且在这里会导致对COLOCATED表的一个索引区间扫描。

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

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

转载于:https://my.oschina.net/u/1862478/blog/1836205