<oracle-10> 索引

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

10.1 oracle索引概述
Oracle提供了多种不同类型的索引供使用。简单的说，oracle中包括如下索引。
（1） B*树索引：这些就是传统索引。目前为止，这是oracle和大多数其他数据库中最常用的索引。B*树的构造类似二叉树，能根据键提供一行或一个行集的快速访问，通常只需要很少的读操作就能找到正确的行。不过需要注意”B*树”中的”B”不代表二叉（binary）,而代表平衡（balanced）。B*树索引并不是一颗二叉树。B*树索引有以下子类型：索引组织表、B*树聚簇索引、降序索引、反向键索引。
1. 索引组织表：以B*树结构存储。堆表的数据行是以一种无组织的方式存储，而IOT与之不同，IOT中的数据要按主键的顺序存储和排序。
2. B*树聚簇索引：这是传统B*树索引的一个变体。B*树聚簇索引用于对聚簇键建立索引。在传统B*树种，键都是指向一行，而B*树聚簇不同，一个聚簇键会指向一个块，其中包含与这个聚簇键相关的多行。
3. 降序索引：降序索引允许数据在索引结构中按“从大到小”的顺序排列。
4. 反向键索引：这也是B*树索引，只不过键中的字节会反转。利用反向键索引，如果索引中填充的是递增的值，索引条目在索引中可以得到更均匀的分布。

（2） 位图索引：在一颗B*树中，通常索引条目和行之间存在一对一的关系，一个索引条目就指向一行。而对于位图索引，一个索引条目则使用一个位图同时指向多行。位图索引适用于高度重复而且通常只读的数据（高度重复是指相对于表中的总行数，数据只有很少的几个不同值，比如枚举值）。注意，位图索引要求使用oracle企业版或个人版。
（3） 位图联接索引：这为索引结构中的数据提供一种逆规范化的方法。
（4） 基于函数的索引：这些就是B*树索引或位图索引，它将一个函数计算得到的结果存储在行的列中，而不是存储列数据本身。可以把基于函数的索引看做一个虚拟列上的索引。换句话说，这个列并不物理地存储在表中。基于函数的索引可以用于加快形如：select * from t where fuction(database_column) = some_value；这样的查询，因为值function(database_column)已经提前计算并存储在索引中。
（5） 应用域索引：是你自己构建和存储的索引，可能存储在oracle中，也可能在oracle外。你要告诉优化器索引的选择性如何，以及执行的开销有多大，优化器则会根据你提供的信息来决定是否使用你的索引。

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


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

Oracle发现第一个最小键值大于或等于20的索引叶子块，然后水平地遍历叶子节点链表，直到最后命中一个大于30的值。
B*树索引中不存在非唯一条目。在一个非唯一索引中，oracle会把rowid作为一个额外的列（有一个长度字节）追加到键上，使得键唯一。例如，如果有一个create index I on T(x,y)索引，从概念上讲，它就是create unique index I on T(x, y, rowid)。在一个唯一索引中，根据你定义的唯一性，oracle不会再向索引键增加rowid。在非唯一索引中，你会发现，数据首先按索引键值排序，然后按rowid升序排序。而在唯一索引中，数据只按索引键值排序。

B*树的特点之一是：所有叶子块都应该在树的同一层中。这一层也称为索引的高度(height)。这说明所有从索引的根块到叶子块的遍历都会访问同样数目的块。换句话说，索引是高度平衡的。大多数B*树索引的高度都是2或者3，即使索引有数百万行记录也是如此。这说明，一般来讲，在索引中找到一个键只需要执行2或3次IO.

10.2.1 索引键压缩
对于B*树索引，可以做的一件有意思的工作是压缩。这与压缩zip文件的方式不同，它是指从串联（多列）索引中去除冗余。压缩键索引的基本特征是，每个键条目分解为两个部分：前缀和后缀。前缀建立在串联索引的前几列上，这些列由许多重复的值。后缀则在索引键的后几列上，这时前缀所在索引条目中的唯一部分（即有区别的部分）。
压缩不是免费的，压缩索引比原来更复杂了。Oracle会花更多的时间来处理这个索引结构中的数据，不光在修改期间维护索引更耗时，查询期间搜索索引页更花时间。需要对增加的cpu时间和减少的IO时间做出权衡来决定是否压缩索引。

10.2.2 反向键索引
B*树索引的另一个特定是能够将索引键反转。B*树索引是为特定的环境，特定的问题设计的。实现B*树索引的目的是为了减少“右侧”索引中对索引叶子块的竞争，比如某些列时用给一个序列值或时间戳填充，这些列上建立的索引就属于“右侧”索引。

物理上反向键索引只是将索引键中各个列的字节反转。如果考虑90101,90102和90103这几个数，使用oracle dump函数查看其内部表示，可以看到每个数的长度都是4字节，他们只是最后一个字节有所不同。这些数最后可能在一个索引结构中向右依次放置。不过，如果反转这些数的字节，这些数彼此之间最后会相距很远。这样就能减少竞争，访问同一个块（最右边的块）的实例个数就能减少。反向键索引的缺点之一是：能用常规索引的地方不一定能用反向键索引。

10.2.3 降序索引
降序索引是oracle 8i引入的，用以扩展B*树索引的功能。它允许在索引中以降序（从大到小）存储一列，而不是升序存储。例如使用表T:
create table t as select * from all_objects;
create index t_idx on t(owner, object_type,object_name);

如果有查询：select owner, object_type from t where owner between ‘T’ and ‘Z’ and object_type is not null order by owner desc, object_type desc;
可以看到查询计划：


Oracle会往前读索引。这个计划最后没有排序步骤，数据已经是有序的。不过如果你有一组列，其中一些列按升序排序，另外一些列按降序排序，此时降序索引就能排上用场了，例如：
Select owner, object_type from t where owner between ‘T’ and ‘Z’ and object_type is not null order by owner desc, object_type asc;



Oracle不能使用（owner, object_type, object_name）上的索引对数据排序。它可以往前读得到按owner desc排序的数据，但是现在还需要“向后读”来得到按object_type升序排序的数据。此时oracle的实际做法是，它会把所有行收集起来，然后排序。但是如果使用desc索引，则有

create index desc_t_idx on (owner desc, object_type asc);

同样再是上面的查询则有查询计划：



在这个计划的最后并没有额外的排序步骤。

10.2.4 什么情况下应该使用B*树索引
不要盲目的相信“经验”，所有规则都有例外，所以对于什么时候该使用和不该使用B*树索引，并没有什么经验。为了说明为什么在这方面不能提供经验，下面给出两种做法，这两种做法同等有效。
（1） 仅当要通过索引访问表中很少一部分行（只占一个很小的百分比）时，才使用B*树在列上建立索引。
（2） 如果要处理表中的多行，而且可以使用索引而不用表，就可以使用一个B*树索引。

这两个规则看上去存在冲突，不过实际中，他们并不冲突，只是涵盖了两种完全不同的情况。根据以上建议，有两种使用索引的方法。
（1） 访问表中的行：通过读索引来访问表中的行。此时你希望访问表中很少的一部分行（只占一个很小的百分比，如果不在总行数的1%~20%之间，那么与全表扫描相比，通过B*树索引通常要花更长的时间）。
（2） 回答一个查询：索引包含了足够的信息来回答整个查询，我们根本不用去访问表。在这种情况下，索引则用作一个较瘦版本的表。

1. 物理组织
数据在磁盘上如何物理地组织，对访问表的IO块数的计算会有显著影响。

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

可以把聚簇因子看做是通过索引读取整个表时对表执行的逻辑IO次数。也就是说，clustering_factor指示了表相对于索引本身的有序程度。

10.2.5 B*树小结
B*树索引是到目前为止oracle数据库中最常用的索引结构，对B*树索引的研究和了解也最为深入。在访问时间方面提供了很大的可扩展性，从一个1000行的索引返回数据所用的时间与一个100 000行的索引结构中返回数据的时间是一样的。
什么时候建立索引，在哪些列上建立索引，你的设计中必须注意这些问题。索引并不一定就意味着更快的访问。实际上你会发现，在许多情况下，如果oracle使用索引，然而会使性能下降。这实际上只是两个因素的函数，其中一个因素是通过索引需要访问表中多少数据（占多大百分比），另一个因素是数据如何布局。


10.3 位图索引
位图索引（bitmap index）是从oracle 7.3开始引入的。目前oracle企业版和个人版都支持位图索引，但标准版不支持。位图索引是为数据仓库/即时查询环境设计的，在此所有查询要求的数据在系统实现时根本不知道。位图索引特别不适用于OLTP系统，如果系统中的数据会由多个并发会话频繁地更新，这种系统也不适用位图索引。
位图索引是这样一种结构，其中用一个索引键条目存储指向多行的指针；这与B*树结构不同，在B*树结构中，索引键和表中的行存在着对应关系。在位图索引中，可能只有很少的索引条目，每个索引条目指向多行。而在传统的B*树种，一个索引条目就指向一行。

10.3.1 什么情况下应该使用位图索引
位图索引对于相异基数低的数据最为合适（也就是说，与整个数据集的基数相比，这个数据只有很少几个不同的值）。对此作出量化是不太可能的，很难定义低相异基数到底是多大。
位图索引在读密集的环境中能很好地工作，但是对于写密集的环境则极不适用。原因在于，一个位图索引键指向多行，如果一个会话修改了有索引的数据，那么在大多数情况下，这个索引指向的所有行都会被锁定，oracle无法锁定一个位图索引中的单独一位。

10.4 基于函数的索引
基于函数的索引是oracle 8.1.5中增加的一种索引。现在已经是标准版的一个特性，但在oracle 9i release2之前的版本中，这还只是企业版的一个特性。
利用基于函数的索引，我们能够对“计算得出的列”建立索引，并在查询中使用这些索引。简而言之，利用这种能力，可以做很多事情，如执行不区分大小写的搜索或排序；根据复杂的公式进行搜索；还可以实现自己的函数和运算符，然后在此之上执行搜索从而高效扩展sql语言。
使用基于函数的索引可能有很多原因，其中主要的原因如下：
（1） 这种索引很容易实现，并能立即提交一个值。
（2） 可以加快现有应用的速度，而不用修改任何逻辑或查询。

10.4.1 重要的实现细节
（1）使用基于代价的优化器（CBO）。在基于函数的索引中，虚拟列（应用了函数的列）只对CBO可见，而基于规则的优化器（RBO）不能使用这些虚拟列。
（2）对于返回varchar2或raw类型的用户编写的函数，使用substr来约束其返回值。也可以把substr隐藏在一个视图中（这是推荐的做法）。

一旦满足前面的条件，基于函数的索引就很容易使用。只需使用create index命令来创建索引，优化器会在运行时发现并使用你的索引。

10.5 关于索引的常见问题
10.5.1 视图能使用索引吗
视图实际上就是一个存储查询（stored query）。Oracle会把查询中访问视图的有关文本代之以视图定义本身。视图只是为了方便最终用户或程序员，优化器还是会对基表使用查询。使用视图时，完全可以考虑为基表编写的查询中所能用的所有索引。对视图建立索引实际就是对基表建立索引。

10.5.2 null和索引能协作吗
B*树索引（除了聚簇B*树索引这个特例之外）不会存储完全为null的条目（如果是联合索引，则所有字段同时为null），而位图和聚簇索引则不同。这个副作用可能会带来一些混淆，但是如果你理解了不存储完全为null的键是什么含义，就能很好的利用这一点。
Oracle中，考虑唯一性时(null,null)与(null,null)并不相同，这是sql标准要求的。不过对于聚集来说，认为是相同的。两个(null,null)在比较时并不相同，但对group by来说却是一样的。所以应当考虑到，每个唯一约束应该至少有一个确实唯一的not null列。
比如：select * from T where x is null;
如果x上有普通索引，这个查询不会使用索引。只有当索引键中至少有一个列定义为not null时查询才会使用索引。

10.5.3 外键是否应该加索引。
前面说过，外键不加索引是导致死锁的最主要的原因。因为无论是更新父表主键或者删除一个父记录，都会在子表上加一个表锁。在以下情况下，外键未加索引也会很糟糕：
（1） 如果有一个on delete cascade, 而且没有对子表建索引。
（2） 从父表查询子表时。如emp是dept的子表。如果频繁的执行以下查询：
Select * from dept, emp where emp.deptno = dept.deptno and dept.dname = ：x;
你会发现如果没有索引会使查询减慢。

那么什么时候不需要对外键加索引呢？一般来说，如果同时满足以下条件：
（1） 未删除父表中的行
（2） 不论是有意还是无意，总之未更新父表的唯一/主键值。
（3） 不会从父表联接到子表，或者更一般地讲，外键列不支持子表的一个重要的访问路径，而且你的谓词中没有使用这些外键列从子表中选择数据（如dept到emp）。

10.5.4 为什么没有使用我的索引
对此有很多可能的原因，这里介绍一些最常见的原因。
1. 情况1
我们在使用一个B*树索引，而且谓词中没有使用索引的最前列。

2. 情况2
我们在使用一个select count(*) from T查询，而且在表T上有一个B*树索引。不过，优化器并不是统计索引条目，而是全表扫描。这种情况下，索引可能建立在一些允许为null值的列上。由于对于索引键完全为null的行不会建立相应的索引条目，所以索引中的行数可能并不是表中的行数。这里优化器的选择是对的。


3. 情况3
对于一个有索引的列，做以下查询：
select * from t where f(indexed_column) = value;
因为这个列上使用了函数，可以对函数建立索引就会走索引了。


4. 情况4
我们已经对一个字符串列建立了索引。这个列只包含数值数据。如果使用以下语法来查询：
select * from t where indexed_column = 5;
注意查询中的数字5是常熟5，而没有用’5’，此时就没有使用indexed_column上的索引。这是因为前面的查询等价于以下查询：
select * from t where to_number(indexed_column) = 5;
我们队这个列隐式的应用了一个函数，如情况3所述，这就会禁止使用这个索引。
如果可能的话，倘若谓词中有函数，尽量不要对数据库列应用这些函数，把函数转移到“值上去”，比如把select * from t where x + 2 > ：x转成 select * from t where x > ：x -2;
这样做不仅可以使用更多的索引，还能减少处理数据库所需的时间。

5. 情况5
使用了索引反而导致查询更慢，这种情况也很常见。Oracle（对CBO而言）只会在合理地时候才使用索引。如果运行一个查询，它只需要表中相对较少的数据，此时优化器很乐意使用索引。不过，如果估计通过索引获取的行数超过一个阀值（取决于不同的优化器设计，物理设计等，这个阀值有可能变化），就会开始一个全表扫描。

6. 情况6
有一段时间没有分析表了。这些表起先很小，但等到查看时，他们已经增长得非常大。现在索引就爱很有意义。如果此时分析这个表，就会使用索引。如果没有正确的统计信息，CBO将无法做出正确的决定。所以对于CBO，应该及时的分析表。