Oracle Hint(提示）之INDEX_COMBINE

INDEX_COMBINE提示的作用和使用方法

INDEX_COMBINE提示是指导优化器，通过联合访问一个表上的多个索引来实现数据的检索。而“联合访问”的实现，是通过在目标索引上施加对应的过滤条件，将过滤后的结果行的ROWID转换为位图，然后做按位AND（对应于相关条件之间是AND的关系），或者按位OR（对应于相关条件之间是OR的关系），最后，再将该按位AND或者OR的结果转换为ROWID，最后基于ROWID回表获取相应的行。

INDEX_COMBINE提示的使用语法如下图所示：

其中：

tablespec表示目标表的名称或别名（当有别名时，必须用别名）。但不要加入表的属主（SCHEMA）名，哪怕在SQL中，明确写了属主，提示中也不能写。indexspec表示要使用哪个索引，或者要在哪个列上使用索引。其语法图如下所示：

queryblock表示查询块。优化器在为一条SQL制定执行计划时，会将该SQL中涉及的子查询和视图，拆分为相应的查询块。分别为每一个查询块制定执计划。

其使用方法和注意事项，也与INDEX提示相同，为节省篇幅，这再不再赘述。

下面，我们通过实验来说明施加该提示时，优化器是如何使用索引的。

测试验证

首先，我们创建一个测试表，并在表的多个列上，分别创建索引：

如上两图所示，我们创建了一个名为testtab的表，并在其上的object_id列，object_name列和owner列上分别创建了单列索引。对目标表TESTTAB收集统计信息，并设置SQLPLUS下的环境参数，以便显示的内容方便查看。如下图所示：

我们从表中随便找一行，做为后续实验的检索记录目标：

发出以下查询：

select * from testtab where object_id=20 and object_name=‘ICOL$’;

并查看此时的执行计划，如下图所示：

如上图所示，我们可以看到，默认情况下，Oracle优化器只会选择表上的一个索引来使用。在本例中，优化器选择了Object_id列上的索引。

下面，我们通过index_combine提示，让优化器选择object_id列和object_name列上各自的索引，联合在一起使用：

如上图所示，我们可以看到index_combine提示中，指定了对应列上各自的索引。从执行计划中，我们可以看到ID5和ID7这两步，分别访问了object_id列和object_name列上的索引。对应的过滤条件，就是object_id=20和object_name=‘ICOL$’。从各自索引中检索到满足各自条件的结果后，基于ROWID（注：索引中会保存对应记录的ROWID信息）再转换为位图（对应于执行计划中的ID4和ID6两步）；接着，对这两个位图做按位AND（对应于执行计划的ID3），其结果仍是个位图，再将该位图结果转换回ROWID（对应于执行计划的ID2）,最后，基于ROWID回表，获取到相关的记录的完整信息。假如发出的查询的两个过滤条件之间，不是AND，而OR，则其执行计划中ID3的操作，将从“BITMAP AND”，变成“BITMAP OR”。如下图所示：

但是，如果提示中指定的索引不正确，即过滤条件并不能使用该索引时，则该提示会被忽略，如下图所示：

如上图所示，在该示例中，我们在提示中指定的索引是在OWNER列上的索引，但WHERE子句中，并没有OWNER列上的过滤条件，这时，只有OBJECT_ID列的索引可以被使用，且只有一个索引，并不能联合多个索引来访问。所以，提示被忽略了。

但是，如果此时，我们在WHERE子句中，增加一个OWNER列上的过滤条件，使其可以使用上OWNER列上的索引，满足有多个索引可以被访问的条件，那么，这个提示是可以生效的。如下图所示：

从以上的几个例子中，我们也可以发现，使用多个索引做联合访问时，其COST值，都高于只使用OBJECT_ID列上一个索引时的COST。这是因为使用Object_id列的索引，有很好的过滤性，比再额外访问另一个或多个索引的成本要低。

反之，如果使用的索引，在各自的条件上的过滤性都不佳，但组合后，过滤性较好（即满足条件的记录少）的话，其联合多个索引来访问的成本，就会是相对较低的了。我们通过一个实验来验证：

如上图所示，我们的过滤条件分别是ojbect_id>113000和owner=‘PUBLIC’,单独满足其中一个条件的记录，均较多，但同时满足这两个条件的，却是没有的。这时，我们可以看到，即便我们没有使用index_combine的提示，优化器也选择了这种联合访问多个索引的方法。

如果这时，我们通过index的提示（index提示的使用介绍，可点击该链接https://support.enmotech.com/article/publish/5724查看)，来分别指定使用其中之一个索引，看看对应的成本是多少。如下两图所示：

此外，INDEX_COMBINE提示是指导优化器，通过联合访问一个表上的多个索引来实现数据的检索。而“联合访问”的实现，是通过在目标索引上施加对应的过滤条件，将过滤后的结果行的ROWID转换为位图。如果访问的目标索引，自身就是位图索引，则转换为位图的处理步骤可以省略，其执行计划也会与访问普通的BTREE索引不同。如下例所示：

我们在OWNER列上创建一个位图索引（注：由于此前已经在OWNER列上创建过一个普通索引，所以，要删除原索引，否则，创建新索引时会报错，提示目标列上已经存在一个索引了），如下图所示：

然后，我们仍然发出之前执行的查询SQL：

此时，其执行计划如下图所示：

如上图中的红框所示，我们可以看到，其处理步骤的名称为“BITMAP INDEX SINGLE VALUE”，意为访问BITMAP索引上的单个值。也没有了“BITMAP CONVERSION FROM ROWID"的处理步骤。这说明，当使用的索引是位图索引时，其可以直接使用该位图索引上的位图信息，而无需转换。

知识总结

1、index_combine提示用于多个索引的联合访问，如果只有一个索引可用，或者提示指定的索引只有一个索引可用，则该提示会被忽略。
2、联合多个索引的访问，涉及将访问各自索引后获取到的ROWID转换为位图（如果目标索引是位图索引，则可以直接使用位图索引中的位图信息，而无需转换），最终，还要将汇总后的位图结果转换回ROWID的操作。所以，在索引列上存在有较好过滤性的条件时，其成本不一定会低于访问单个索引。
3、当WHERE子句相关过滤条件列上的单独过滤性均不佳，而组合后反而满足条件的记录较少时，这时，联合访问多个索引就会有比较好的效果了。

参考文档

《Oracle® Database SQL Language Reference》