SQL执行计划 - 查询转换hint的介绍和使用技巧

冷菠

冷菠，资深DBA，著有《Oracle高性能自动化运维》，有近10年的数据库运维、团队管理以及培训经验。擅长数据库备份恢复、数据库性能诊断优化以及数据库自动化运维等。目前致力于大数据、智能一体化、开源云计算等领域的佳实践探索。

index提示

在SQL优化中，除了可以通过修改参数的方式干预优化器工作外，还可以使用提示的方式进行干预，而且这种方式更加精准、不影响其他SQL，故使用场景更加广泛。本文主要介绍跟索引相关的hint。

index提示控制优化器使用索引扫描，主要包含以下2种使用方式：

/*+ index(table_name) */ 提示；

/*+ index(table_name index_name) */ 提示

我们通过以下步骤对这2种index提示使用进行介绍：

/*+ index(table_name) */ 提示：

查询条件中谓词列包含2个以上的索引，优化器扫表该列相关的所有的索引，最终选择Cost最小Index进行查询。

创建基础数据和索引，如下：

查看执行计划，如图6-1所示：

图6-1 默认index提示执行计划

可以看到，优化器默认选择索引idx_all进行查询。

/*+ index(table_name index_name) */ 提示：

优化器扫描index提示指定index进行查询，而不会选择最优化的索引，如图6-2所示：

图6-2 index提示指定索引执行计划

结合步骤1内容可以看到，谓词列上存在2个索引（IDX_ALL/IDX_SAL）。当不指定索引时候，系统选择成本最低的索引IDX_ALL（COST=1）；当指定使用索引IDX_SAL（COST=2）后，系统才会选择成本较高的索引IDX_SAL。

index_join提示

Index_join提示将查询数据索引进行Hash Join连接，从而避免了对表的直接访问。

可以通过以下步骤index_join提示的使用进行验证：

查询SQL执行计划，如图6-3所示：

图6-3 未使用index_join提示的执行计划

使用index_join 提示后的执行计划，如图6-4所示：

图6-4 使用index_join提示的执行计划

可以看到，使用index_join提示后，索引间进行Hash Join。 同时对比步骤1可以看到，由于使用Index Hash Join的成本（3）高于使用索引IDX_MGR的成本（2），因此Oracle在默认情况下，自动选择使用成本低的索引（IDX_MGR）。

index_ffs提示

Index_ffs（Index Fast Full Scan）提示强制CBO优化器使用索引快速全表扫描代替全表扫描进行查询。

Index_ffs 提示主要的使用场景如下：

组合索引中的查询结果为count(*)、avg()等数据集聚合（全表）及无Filter过滤的场景；

使用is not null强制查询转换，使用索引快速全表扫描代替全表扫描。

我们通过以下步骤来进行验证：

无Filter过滤场景：

查看未使用index_ffs提示的执行计划，如图6-5所示：

图6-5 未使用index_ffs提示的执行计划

使用index_ffs提示后的执行计划，如图6-6所示：

图6-6 使用index_ffs提示后后的执行计划

可以看到，当使用index_ffs提示后，使用索引快速全表扫描代替全表，性能得以提升（成本从3降低到2）。

使用is not null查询转换：

创建基础数据和索引：

查询转换前的执行计划，如图6-7所示：

图6-7 使用查询转换前的执行计划

可以看到，查询转换前使用全表扫描。

使用is not null进行查询转换，执行计划如图6-8所示：

图6-8 使用查询转化后的执行计划

可以看到，当使用is not null进行查询转换后，优化器使用索引快速全表扫描代替全表扫描。

index_ss提示

Index_ss（Index Skip Scan）提示使用索引跳跃扫描方式进行查询，Index_ss发生在组合索引中，它在逻辑上将组合索引分离为较小的子索引，当组合索引的某一个索引列没有出现在谓词过滤条件中，那么它将被跳过扫描，从而提高索引扫描的效率。

Index_ss提示适用场景：

组合索引index （c1，c2，c3...）；

谓词过滤条件中包含组合索的任意列或者任意列组合，也可以包含全部列。但是不建议谓词过滤条件中出现组合索引所有列，因为在这个情况下的Cost可能比其他扫描方式（例如Index Range Scan）成本高。

我们通过以下步骤来验证：

1、使用index_ss提示下的组合索引前导列：

当组合索引前导列出现在谓词过滤条件中时，使用index_ss提示的执行计划如图6-9所示：

图6-9 使用index_ss提示前导列的执行计划

可以看到，当使用index_ss提示的谓词列为组合索引前导列时，CBO使用索引跳跃扫描。

注意：where谓词过滤条件后跟任意列指的是组合索引中的随机列。

2、使用index_ss提示下的组合索引随机列：

当将组合索引随机列作为谓词过滤条件时，使用index_ss提示的执行计划如图6-10所示：

图6-10 使用index_ss提示随机列的执行计划

可以看到，当使用index_ss提示的谓词列为组合索引随机列时，CBO使用索引跳跃扫描。

3、使用index_ss提示下的组合索引所有列：

当将组合索引所有列作为谓词过滤时，使用index_ss提示的执行计划如图6-11所示：

图6-11 使用index_ss提示所有列的执行计划

可以看到，当使用index_ss提示的谓词列为组合索引所有列时，CBO使用索引跳跃扫描。

4、不使用index_ss提示的组合索引所有列：

当将组合索引所有列作为谓词过滤时，而不使用index_ss提示时，执行计划如图6-12所示：

图6-1 使用index_ss提示所有列的执行计划

对比步骤3可以发现，将组合索引所有列作为谓词过滤条件时使用index_ss提示的成本“4”高于不使用index_ss提示的成本“1”。

index_combine提示

Index_combine提示为表指定位图访问路径，主要分为BITMAP AND与BITMAP OR访问模式。

可以通过以下步骤进行验证：

1、BITMAP AND访问模式：

查询使用and作为谓词连接，而未使用index_combine提示时的执行计划，如图6-13所示：

图6-13 未使用index_combine提示的执行计划（and）

查询使用and作为谓词连接，同时使用index_combine提示时的执行计划，如图6-14所示

图6-14 使用index_join提示的执行计划（and）

可以看到，查询使用and作为谓词连接时，使用index_combine提示可以将提示中的指定的谓词列访问路径进行BITMAP AND操作。

2、BITMAP OR访问模式：

查询使用or作为谓词连接，而未使用index_combine提示时的执行计划，如图6-15所示：

图6-15 未使用index_combine提示的执行计划（or）

查询使用or作为谓词连接，同时使用index_combine提示时的执行计划，如图6-16所示：

图6-16 使用index_combine提示的执行计划（or）

可以看到，查询使用or作为谓词连接时，不管是否使用index_combine提示，执行计划中的谓词过滤列访问路径都进行BITMAP OR操作。