Oracle中组合索引的使用详解

        在Oracle中可以创建组合索引，即同时包含两个或两个以上列的索引。在组合索引的使用方面，Oracle有以下特点：

        1.当使用基于规则的优化器（RBO）时，只有当组合索引的前导列出现在SQL语句的where子句中时，才会使用到该索引；

        2.在使用Oracle9i之前的基于成本的优化器（CBO）时， 只有当组合索引的前导列出现在SQL语句的where子句中时，才可能会使用到该索引，这取决于优化器计算的使用索引的成本和使用全表扫描的成本，Oracle会自动选择成本低的访问路径（请见下面的测试一和测试二）；

        3.从Oracle9i起，Oracle引入了一种新的索引扫描方式——索引跳跃扫描（index skip scan），这种扫描方式只有基于成本的优化器（CBO）才能使用。这样，当SQL语句的where子句中即使没有组合索引的前导列，并且索引跳跃扫描的成本低于其他扫描方式的成本时，Oracle就会使用该方式扫描组合索引（请见下面的测试三）；

        4.Oracle优化器有时会做出错误的选择，因为它再“聪明”，也不如我们SQL语句编写人员更清楚表中数据的分布，在这种情况下，通过使用提示（hint），我们可以帮助Oracle优化器作出更好的选择（请见下面的测试四）。

        关于以上情况，我们分别测试如下：

        我们创建测试表T，该表的数据来源于Oracle的数据字典表all_objects。

create table t as select * from all_objects;

        验证新建的表T成功

        表T的结构如下：

        表中的数据分布情况如下：

        在表T上创建如下索引并对其进行分析：

        现在让我们编写几条SQL语句来测试一下Oracle优化器对访问路径的选择： 

测试一：

        正如我们所期望的，由于使用了组合索引的前导列并且访问了表中的少量记录，Oracle明智地选择了索引扫描。那么，如果我们访问表中的大量数据时，Oracle会选择什么样的访问路径呢？请看下面的测试：

测试二：

        很明显，即使使用了组合索引的前导列，但是由于访问了表中的大量数据，Oracle选择了不使用索引而直接使用全表扫描，因为优化器认为全表扫描的成本更低，但事实是不是真的这样的？我们通过增加提示（hint）来强制它使用索引来看看：

        从以上结果可以看出，在访问大量数据的情况下，使用索引确实会导致更高的执行成本，这从statistics部分的逻辑读取数（consistent gets）就可以看出，使用索引导致的逻辑读取数（1790）比不使用索引导致的逻辑读（1163）的多。因此，Oracle明智地选择了全表扫描而不是索引扫描。

        下面，让我们来看看where子句中没有索引前导列的情况：

测试三：

        OK！由于只查询了1条数据，即使没有使用前导列，Oracle正确地选择了索引跳跃扫描。我们再来看看如果不使用索引跳跃扫描，该语句的成本：

        正如我们所料，不使用索引所导致的逻辑读（403）确实比使用索引的逻辑读多（26），达到10倍以上。

        继续我们的测试，现在我们来看看Oracle不选择使用索引的情况：

测试四：

        这次只选择了68条数据，跟表T中总的数据量29165条相比，显然只是很小的一部分，但是Oracle还是选择了全表扫描，有407个逻辑读。这种情况下，如果我们强制使用索引，情况会怎样呢？

        通过添加提示（hint），我们强制Oracle使用了索引扫描（index full scan），执行了207个逻辑读，比使用全表扫描的时候少了一些。

        由此可见，Oracle优化器有时会做出错误的选择，因为它再“聪明”，也不如我们SQL语句编写人员更清楚表中数据的分布，在这种情况下，通过使用提示（hint），我们可以帮助Oracle优化器作出更好的选择。

 

附：登陆PL/SQL DEVELOPER工具中的COMMAND窗口，执行set autotrace traceonly报：“cannot set autotrace traceonly”，后来通过输入cmd，用sqlplus登陆，结果是OK的。操作步骤如下：

 

资料来源：http://www.cnblogs.com/rootq/archive/2008/10/19/1314669.html