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操作。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-09-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

浅谈exp/imp(下) (r5笔记第84天)

相关链接:浅谈exp/imp(上) (r5笔记第81天) 你可能 不了解的dump文件 在工作中,dump文件对于dba而言是再平常不过的文件了。不过因为dum...

3189
来自专栏企鹅号快讯

从商用到开源:15个维度,全面剖析DB2与MySQL数据库的差异

编辑手记 MySQL是目前最流行的开源数据库,由于其部署方便,运维简单,被广泛用于互联网的各个领域。随着整体IT架构的变更,传统的金融,电信业务,也逐渐走上从商...

2289
来自专栏杨建荣的学习笔记

MySQL 5.7 General Tablespace学习(r11笔记第34天)

MySQL里面的文件蛮有意思,之前大体有两个参数来做基本的控制。一个是innodb_data_file_path就是一个共享表空间,数据都往这一个文件里放,也就...

3629
来自专栏杨建荣的学习笔记

system表空间不足的问题分析(二) (r8笔记第5天)

今天收到一条不太起眼的报警邮件,大体内容是某个表空间的空间有些紧张了。大体内容如下: Tablesapce: CMBI_SNZG_DATA: 92.2% [W...

3297
来自专栏杨建荣的学习笔记

一条看似平常的报警邮件所做的分析(r8笔记第9天)

今天留意到一封报警邮件。内容如下: ZABBIX-监控系统: ------------------------------------ 报警内容: CPU u...

2504
来自专栏漫漫全栈路

Oracle数据库项目——MC服务器管理系统

数据库设计内容,分为:系统功能说明,数据库分析与设计,数据库实现。本数据库系统提供的功能用于项目MC-Servers-Web,详情内容参见MC-Service...

6464
来自专栏L宝宝聊IT

索引、视图、存储过程和触发器的应用

1518
来自专栏文渊之博

比较列存储索引与行索引

原因:     之前已经写过一篇关于列存储索引的简介https://cloud.tencent.com/developer/article/1032222,很粗...

2056
来自专栏杨建荣的学习笔记

一个SQL性能问题的优化探索(一)(r11笔记第33天)

今天同事问我一个问题,看起来比较常规,但是仔细分析了一圈,发现实在是有些晕,我隐隐感觉这是一个bug,但是有感觉问题还有很多需要确认和理解的细节。 同事...

3469
来自专栏匠心独运的博客

数据库技术之记一次排查DB死锁的分析与思考

虽然很多童鞋在学数据库课程时都了解数据库隔离级别、死锁和事务等概念,但在测试/线上环境遇到死锁却不一定能够及时分析并解决这类问题。本文主要以作者在测试环境中遇到...

1421

扫码关注云+社区