带你读懂Oracle执行计划

举报
GaussDB100_DBA 发表于 2019/07/29 09:52:13 2019/07/29
【摘要】 1 序执行计划是Oracle优化器在执行SQL时选择的执行方案。对于优化器来说,同一个SQL可能有很多种执行计划,执行效率也可能相差很多。虽然优化器已经越来越智能,可以通过收集统计信息选择出最优的执行计划,看懂执行计划和调整执行计划仍然是数据库调优的必备技能。本文讲解了常见的执行计划,重点介绍多表连接的执行计划。虽然SQL语句中存在三个甚至更多的表连接,但是实际上优化器每次只能做两...

1     

执行计划是Oracle优化器在执行SQL时选择的执行方案。对于优化器来说,同一个SQL可能有很多种执行计划,执行效率也可能相差很多。虽然优化器已经越来越智能,可以通过收集统计信息选择出最优的执行计划,看懂执行计划和调整执行计划仍然是数据库调优的必备技能。

本文讲解了常见的执行计划,重点介绍多表连接的执行计划。虽然SQL语句中存在三个甚至更多的表连接,但是实际上优化器每次只能做两个表的连接,然后使用连接结果再和第三个表连接,所以只要掌握了两个表的连接自然也就掌握了多表的连接。

表的连接方式有很多种,最常用的是嵌套循环和哈希连接,需要重点掌握。如果你还没有掌握单表的执行计划,请先阅读轻松掌握Oracle索引,它是阅读本文的基础。看完本文,相信你可以看懂常见的执行计划,知道什么样的执行计划好,以及如何使用提示固定简单的执行计划。

2      基础知识

    在讲解执行计划前,首先介绍一些基础知识:优化器和统计信息。对于优化器,重点掌握CBO优化器,因为RBO已经逐渐被淘汰。对于统计信息,需要掌握常用的收集和查看方法。

2.1      优化器

1)优化器的作用

    SQL语句是非过程性的语言,它只说明需要取哪些数据,但是没有说明如何去取这些数据。对于一个SQL语句,可以使用的取数据方式有很多种,所以需要优化器来选择使用哪种方式。每一种取数据的方式就是一种执行计划。

 

2)优化器的种类有哪些

    优化器有RBOCBO两种,RBO是基于规则的优化器,CBO是基于成本的优化器,RBO是早期的优化器,从10g开始,oracle不再为RBO提供服务了,默认的优化器也是CBO了。

 

3RBO有哪些特点

    RBO是基于规则的优化器,它在解析SQL时只关注语句本身,不关注表中的数量,只要表结构没有变化,同一个SQL语句的执行计划是不变的。RBO不会计算成本(cost),所以在执行计划中,cost列为空。RBO的规则是固定的,例如,如果查询列上有索引,RBO会优先选择索引扫描。RBO在选择表的连接顺序时优先按照from后面从右到左的顺序。注意:只是优先,并不是一定,因为还有其他规则会影响RBO的选择。

 

4CBO有哪些特点

    RBO有明显的不足,因为它不关注表中的数据,仅仅根据规则选择的执行计划很可能不是最优的。CBO是针对RBO的不足产生的新一代的优化器,它在分析SQL的过程中会考虑各个表的数据量以及数据分布,因此理论上可以获得最优的执行计划。CBO优化器产生的执行计划的cost不为空,可以根据该列判断当前语句使用的是哪种优化器。CBO会计算每一种执行计划的cost,然后选择cost最小的执行计划。

 

59i下使用什么优化器

    9i数据库的参数optimizer_mode默认设置为CHOOSE,这种配置的含义是默认使用RBO,但是一些特殊情况使用CBO。这些情况包括:使用了提示(rule提示除外,该提示是选择使用RBO的含义);该SQL的任一个表被分析过,即有统计信息;使用了一些RBO不支持的特性,例如分区表。

    判断一个SQL使用哪种优化器,通过执行计划的cost列是否为空也很容易判断,注意在9i使用PL/SQL查看执行计划时要把Optimizer goal设置为Choose,如下图。

image.png

611g下使用什么优化器

    11g默认使用CBO,除非使用了rule提示。如果SQL中含有分区表,即使使用rule提示仍然使用CBO,因为分区表等高级功能根本不支持RBO

 

2.2      统计信息

1)什么是统计信息

     CBO优化器需要根据表/索引中的数据来选择查询数据的方式,由于表中的数据量很大,不可能每次执行查询时实时的统计表中的数据量以及数据分布,所以需要定期分析数据,把表和索引的数据分布情况保存到数据字典里,以便优化器使用,这就是统计信息。

 

2)数据字典中已经保存了表占用的空间大小,为什么还需要统计信息

    数据字典(user_segments)中虽然保存了表/索引占用的空间大小,但是这些信息对于优化器来说是不够的,例如,一个表可能占用了很多的空间,但是可能该表中一条数据都没有。

 

3)如何查看统计信息

统计信息保存在数据字典中,主要包括表,表分区,索引,索引分区,列,列的直方图。我们主要关注表和索引上的统计信息,重点关注数据量和数据块数。


对象

视图

关键列

表上的统计信息

user_tables

NUM_ROWS:表中的数据量

BLOCKS:表占用的数据块,由于oracle的最小IO单位是块,所以这个值和NUM_ROWS一样重要

LAST_ANALYZED:最近生成统计信息的时间,如果为空,说明没有统计信息

表分区上的统计信息

user_tab_partitions

user_tab_subpartitions

同上

索引的统计信息

user_indexes

NUM_ROWS:索引中的数据量

LEAF_BLOCKS:索引叶子节点块数

DISTINCT_KEYS:该列有多少个不同的取值

LAST_ANALYZED:最近生成统计信息的时间,如果为空,说明没有统计信息

索引分区的统计信息

user_ind_partitions

user_ind_subpartitions

同上

列的统计信息

user_tab_columns

NUM_DISTICT:该列有多少个不同的取值

LOW_VALUE:最小值

HIGH_VALUE:最大值

LAST_ANALYZED:最近生成统计信息的时间,如果为空,说明没有统计信息

列的直方图

user_histograms



 

4)如果没有统计信息或统计信息不准确,优化器如何处理

    如果SQL语句涉及的表/索引等没有统计信息,CBO优化器在首次解析该语句时会做动态采样,即到该表/索引中抽取一定比例的数据块来分析表的数据分布情况。当然,对于一个大表来说,这个比例不会很高,否则查询就太慢了,因此动态采样的结果不一定准确,可能选择不好的执行计划,同时使用动态采样会导致首次执行一个SQL时比较慢。如果统计信息不准确,那么就可能误导优化器,导致选择了差的执行计划。

 

5)如何生成统计信息

Oracle提供了dbms_stats包,可以很方便的收集统计信息,常用的函数如下表。


方法名

作用

dbms_stats.gather_schema_stats

收集某个用户所有对象的统计信息

dbms_stats.gather_table_stats

收集某个表的统计信息,包括表上的索引和列

dbms_stats.gather_index_stats

收集索引的统计信息

dbms_stats.set_table_stats

设定表的统计信息

dbms_stats.set_index_stats

设定索引的统计信息

dbms_stats.set_column_stats

设定列的统计信息

dbms_stats.create_stat_table

创建一个保存统计信息的表

dbms_stats.export_schema_stats

导出整个用户的统计信息到create_stat_table创建的表中

dbms_stats.export_table_stats

导出某个表的统计信息

dbms_stats.export_index_stats

导出整个索引的统计信息

dbms_stats.export_column_stats

导出整个列的统计信息

dbms_stats.import_schema_stats

从临时表导入整个用户的统计信息

dbms_stats.import_table_stats

从临时表导入某个表的统计信息

dbms_stats.import_index_stats

从临时表导入某个索引的统计信息

dbms_stats.import_column_stats

从临时表导入某个列的统计信息


 

2.3      表的连接

    Join是一种试图将两个表结合在一起的谓词,一次只能连接2个表,表连接也可以被称为表关联。实际上应该使用”row source”来代替,因为使用row source更严谨一些,表连接时并不是每次都需要连接整个表。

row source()之间的连接顺序对于查询的效率有非常大的影响。通过首先存取特定的表,即将该表作为驱动表,这样可以先应用某些限制条件,从而得到一个较小的row source,使连接的效率较高,这也就是我们常说的要先执行限制条件的原因。一般是在将表读入内存时,应用where子句中对该表的限制条件。

  目前为止,无论连接操作符如何,典型的连接类型共有3种:

   嵌套循环(Nested Loops (NL) )

  哈希连接(Hash Join)

  排序合并连接(Sort Merge Join, SMJ)

    本文只介绍嵌套循环和哈希连接,排序合并连接在实际应用中很少使用。

3      嵌套循环

3.1      什么是嵌套循环

    嵌套循环是最常见的表的连接方式,在RBO下,优化器倾向使用嵌套循环(RBO不支持哈希连接),下面我们首先通过一个最简单的例子来理解嵌套循环,其中NESTED LOOPS就是嵌套循环的意思。

image.png

这个执行计划的含义是以T_USERSERVICEINFO为驱动表(外表),先扫描该表,对该的每一条数据全表扫描T_USERINFO(内表),最后返回匹配的数据。这个过程很像Java中的循环语句。

3.2      如何选择驱动表

3.2.1        以小表为驱动表

    驱动表在执行计划中显示为上面的表,如上图中的T_USERSERVICEINFO。两个表做嵌套循环,驱动表的选择很重要。我们可以简单计算下哪个表做驱动表好。

假设T_USERSERVICEINFO是个大表,有4000万数据,占有100万个块;而T_USERINFO是个小表,有4000条数据,占有100个块。

1)以大表为驱动表去要访问的数据块:对T_USERSERVICEINFO全表扫描需要访问100万个块,而T_USERSERVICEINFO中的每一条数据都需要全表扫描T_USERINFO一次,需要访问40亿个块(4000*100),总共访问(40亿+100万)个块。

2)以小表为驱动表去要访问的数据块:对T_USERINFO全表扫描需要访问100个块,而T_USERINFO中的每一条数据都需要全表扫描T_USERSERVICEINFO一次,需要访问40亿个块(4000*100万),总共访问(40亿+100)个块。

从上面的计算看,当两个表都是全表扫描时,选择小表做驱动表相对好些。不过,两者的查询代价都太大,都是不能接受的,因为内表的数据被访问了N遍(N为驱动表的数据量),这是严重的浪费。

3.2.2        内表的关联列上必须有索引

    如果内表T_USERSERVICEINFO的关联列PHONENUMBER有索引会不会大大提升查询效率呢?我们接下来看下面的例子。

image.png

在这个例子中,仍然使用小表(T_USERINFO)为小表,但是对内表的访问不再全表扫描了,而是使用内表的关联列的索引。

此时的执行计划显示为两层的嵌套循环(不同Oracle版本显示的执行计划可能不同)。它的执行步骤是:

1)全表扫描T_USERINFO

2)对第一步的每一条数据访问T_USERSERVICEINFO的索引IX_USERSVC_PHONE,使用第一步查询出来的PHONENUMBER,到T_USERSERVICEINFO表做索引范围扫描。

3)获取第二步从索引叶子中查询到目标数据的ROWID,分别到T_USERSERVICEINFO表中根据ROWID直接获取目标数据块信息。

4)返回目标数据的信息。

从上面的过程看,优化器把根据ROWID访问表这个过程也看作是嵌套循环了。了解了执行过程,下来再来计算下查询的代价。假设索引的高度是4,最终有4000条数据命中。

内表有索引时访问的块数:对T_USERINFO全表扫描需要访问100个块,而T_USERINFO中的每一条数据都需要扫描索引IX_USERSVC_PHONE一次,需要访问4000*4=1.6万个块,使用ROWID到表需要访问4000个块,总共需要访问100+1.6+4000=20100个块,比前面的计算结果提升了上万倍的性能。

感兴趣的话你还可以计算下如果大表为驱动表,小表上有索引的情况,肯定比小表为驱动表差的多,因为仅对大表的全表扫描就需要访问100万个块了。

 

3.3      带有其他条件的嵌套循环

     实际应用中的嵌套循环往往比上面的例子要复杂的多,下面说明常见的几种场景。

3.3.1        驱动表上有索引

    b表上有查询条件phonenumber = '123',此时还是选a表为驱动表好吗?

image.png

   虽然b表比a表大,但是b表应用条件(phonenumber = '123')后只会查出1条数据,那么如果以b表为驱动表,需要访问a表的索引次数最多1次,而反过来需要4000次,所以此时以b表为驱动表好。

image.png


    嵌套循环要使用小表(实际是row source)为驱动表,并不能认为表中数据量大的就一定不能做驱动表,实际上只要需要关联的数据集小就应该做驱动表。嵌套循环关注的数据量是需要连接的row source

   

3.3.2        内表非关联列上的索引

如果内表在关联列上没有索引,但在其他列上有索引呢?如下例所示,b表可以使用createtime的索引,此时好像内表上使用索引了,但如果根据createtime查出的数据量很大性能仍然是很差的,因为对a表的每一条数据都要遍历根据createtime查出的数据。

重复就是浪费,一次能查出来的数据不要查多次。

image.png


3.3.3        rownum条件

    下面看一个带rownum条件的嵌套循环。在执行计划中,rownum条件对应COUNT STOPKEY部分。嵌套循环有一个很大的好处,每一步不需要执行完就可以执行下一步,所以只要查出前100条满足结果的数据那么查询就可以结束。如果目标数据不足100条时,实际上还是需要访问所有数据的。

image.png

关于提示

3.4.1        为什么要使用提示

提示是用来调整和固定执行计划的。如果统计信息正确,优化器是可以选出最优的执行计划的。但是在某些情况下我们也可能需要使用提示:

*  没有收集统计信息

*  临时表上不能收集统计信息

*  优化器选择的执行计划不是最优的

    提示不是万能的,对于复杂的SQL,很难用提示调整执行计划,所以尽量把SQL写简单些。

3.4.2        使用提示为什么要加全套提示,怎样才算全套

如果使用了提示则优化器会使用CBO,如果系统中缺少统计信息,那么此时执行计划不稳定,需要加全套提示来固定,提示主要包括三个方面:

*  指定表的连接顺序,使用leadingordered等,leadingordered不能同时使用,leading只能指定一个表为驱动表,而ordered可以指定多个表的连接顺序。

*  指定表的连接方式,使用use_nluse_hash等,注意它仅表示表连接方式而不表示连接顺序,所以参数中的顺序无关紧要。

*  指定表的访问路径,使用fullindex等。

 

3.4.3        常用提示说明

    下面是常用提示的简要说明,后面还有针对这些提示的实例应用。


类型

提示名

使用说明

连接顺序

leading

指定某个表为驱动表,例如leading(a)

ordered

多表连接时指定表的连接顺序,按照from后面表的顺序连接,没有参数

no_unnest

以主查询为驱动,一般使用嵌套循环。

unnest

以子查询为驱动,一般使用嵌套循环。

连接方式

use_nl

指定表的连接方式为嵌套循环,例如use_nl(a b),括号内顺序不重要,括号内支持多个表

nl_sj

指定表的连接方式为嵌套循环,用于带inexists的子查询,该提示写在子查询处,10G后不建议使用。

nl_aj

指定表的连接方式为嵌套循环,用于带not innot exists的子查询,该提示写在子查询处,10G后不建议使用。

use_hash

指定表的连接方式为哈希连接,例如use_hash(a b),括号内顺序不重要,括号内支持多个表

hash_sj

指定表的连接方式为哈希连接,用于带inexists的子查询,该提示写在子查询处,10G后不建议使用。

hash_aj

指定表的连接方式为哈希连接,用于带not innot exists的子查询,该提示写在子查询处,10G后不建议使用。

use_merge

指定表的连接方式为归并排序,例如use_merge(a b),括号内顺序不重要,括号内支持多个表

访问路径

full

指定对某个表全表扫描,例如full(a)

index

指定对某个表使用索引扫描,按照索引的排序方式,默认正序,例如index(a 索引名)

index_desc

指定对某个表使用索引扫描,按照索引的倒序方式,例如index_desc(a 索引名)

index_ffs

指定对某个表使用快速全索引扫描方式,例如index_ffs(a 索引名)

index_ss

指定对某个表使用索引跳跃扫描,例如index_ss(a 索引名)

其他

parallel

指定对某个表使用并行查询,例如设定并行度为4可以这样parallel(a 4)

append

insert数据会直接加到表的最后面,而不会在表的空闲块中插入数据。使用append会增加数据插入的速度。使用时写在insert后面,没有参数。

rule

指定使用RBO优化器,不能有分区表等高级特性,不能和其他提示一起使用。


 

3.5      带子查询的嵌套循环

3.5.1        以主查询为驱动表的Semi-join

如果主查询的表需要连接的数据量小,并且子查询表的关联列上有索引,那么应该以主查询的表为驱动表,如下图所示,使用inexists是等价的。

如果想对这种查询指定嵌套循环并且以a表为驱动表,那么可以用nl_sj提示,注意这个提示写在子查询里。此处的ssemi的缩写,表示半连接。此处也可以用no_unnest提示,因为10g后,nl_sj已不建议使用。

image.png

3.5.2        以子查询为驱动表的Semi-join

如果子查询的结果集比较小,那么就要子查询为驱动,此时可以用ordered提示。为了固定执行计划还可以加上use_nl等其他提示。

这种查询很常见,它应用在b表的结果集很小的情况。注意此时优化器会先对b表排序驱重,因为优化器认为重复数据没必要和主表做连接,相对而言,连接的代价比去重高。如下图所示,使用inexists是等价的。

image.png

3.5.3        嵌套循环的Anti-join

    Anti-join是指使用not in/not exists的子查询。这种查询只能以主查询为驱动表。一般尽量用not exists,而不用not in。因为只要有一条数据关联列的值为空,not in的结果就是空。当关联列非空时,二者是等价的,如下例所示。

image.png

3.6      多表的嵌套循环

    下面是一个典型的多表嵌套循环。ordered指定表的连接顺序a=>b=>cuse_nl指定三个表的连接方式是嵌套循环;indexfull指定了三个表的访问路径,此时a表只能是全表扫描,而不能使用索引了,想想问什么?

image.png

    多表连接时最重要的是连接的顺序,一般原则是优先连接中间结果少的表。例如,abc三个表的连接,最终结果是100条数据。如果ab连接结果是100万数据,再和c连接的结果是100条数据;而ac连接结果是200条数据,再和b连接的结果是100条数据。那么,显然ac先连接比ab先连接好。

3.7      带外连接的嵌套循环

    如果是外连接,那么只能以左表为驱动表,这一点在哈希连接时也是如此。如下图所示,即使指定b表为驱动表,优化器仍然选择a表为驱动表。注意此时的执行计划显示为NESTED LOOPS OUTER

image.png

3.8      小技巧

3.8.1        通过rule提示固定执行计划

对上面这个三表嵌套循环,如果使用rule提示是什么效果呢?如下图,rule默认的表的连接顺序是从右至左的:c=>b=>a。而且,RBO优化器默认会使用嵌套循环和索引扫描,如果当前的执行计划是合适的,那么可以使用rule提示固定,因为RBO的执行计划是不会变化的。

image.png

  虽然RBO默认的连接顺序是从右至左,但是这只是默认情况,如果有其他条件,RBO认为这个条件的优先级更高,那么RBO会改变连接的顺序。如下图,当b表上有查询条件并且可以走索引扫描,那么优化器会优先访问b表,并且以b表为驱动表和其他表做嵌套循环。

image.png


3.8.2        屏蔽索引

    一个表上可能有很多个索引,如果不想使用某个索引可以在列上追加一个表达式,如下图,字符类型可以拼接一个空字符串,数值类型可以加零。

image.png


3.9      嵌套循环的使用场景总结

嵌套循环是最重要的连接方式,尤其是在实时性比较高的系统中,因为这种系统的查询要求快速返回,不能访问很多的数据块,而这正是嵌套循环适用的场景。

嵌套循环的使用场景:

1)驱动表的结果集小(应用过自己的查询条件后),选择驱动表非常重要。

2)内表的连接列上有索引。

3)驱动表和内表连接匹配的数据量小,即扫描内表连接列的索引的次数少。

 

4      哈希连接

4.1      什么是hash连接

    嵌套循环只适用在连接数据量小的情况,如果有上百万数据需要连接,那么就可能需要对内表做上百万次的索引范围扫描,导致内表的很多数据块被重复扫描,而且,索引范围扫描如果需要扫描超过5%的数据很可能就比全表扫描的性能还差了。

实际应用中,这种大数据量的连接是普遍存在的,尤其在后台job和数据分析中,这时候就该考虑应用hash连接了。下面是一个hash连接的实例。下面通过这个例子来说明hash连接的过程。

image.png

    1)选择一个表在内存中使用连接列生成hash

     这个表就是选择的驱动表(上例中为a表,执行计划中显示在上面),由于需要在内存(PGA)中生成hash表,所以选择数据集较小的表为驱动表较好。如果内存中放不下这个hash表,那么就需要放到临时表空间中,如果临时表空间也满了,那么就会报错了。

这个步骤一般需要全表扫描驱动表,或者说需要连接的数据集。那么,不管另一个表有没有数据,即使使用了rownum的条件,这个全表扫描也是必须做的。

 

2)全表扫描探测表,查询hash

当驱动表生成了hash表后,遍历另一个表(也叫探测表),对探测表的每一条数据使用连接列的值到hash表中匹配,如果匹配上将对应数据保存到结果集中,直到查询结束。

如果查询条件有rownum<=100的条件,那么只要找到100条满足的数据就可以停止探测表的扫描了。

如果hash表没有全部放在内存中,那么探测表就需要遍历多次,分别对hash表的多个分区做连接。

 

3)输出hash结果

hash连接结束就得到了连接结果,跟索引扫描不同,hash连接一般是不需要再访问表的,所以hash连接对表的扫描一般是全表扫描或快速全索引扫描。

 

4.2      带子查询的hash连接

与嵌套循环类似,对in/exists的连接可以使用hash_sj提示,对于not in/not exists的连接可以使用hash_aj提示。

4.2.1        Semi-join

下面第一个例子中的HASH JOIN RIGHT SEMI是优化器做了优化,它不同于第二个中的HASH JOIN SEMI。因为这个语句正常应该以a表为驱动表做hash表,以b表为探测表。当优化器认为b表更小时也可以选择b为驱动表,此时的执行计划就是HASH JOIN RIGHT SEMI。此处使用inexists是等价的。

image.png

4.2.2        Anti-join

    对于hash_aj也类似,优化器也可以选择主查询或子查询为驱动表,区别是当选择子查询为驱动时,执行计划会多一个RIGHT    

image.png

跟嵌套循环类似,尽量用not exists,而不用not in,不过在9i上,如果关联列上有索引,not existshash_aj是不生效的,此时只能用not in

4.3      小技巧

4.3.1        使用快速全索引扫描

如果可能的话,优化器会使用快速全索引扫描替代全表扫描,因为一个索引占用的空间比整个表的空间要小的多,所以我们在执行计划中经常可以看到FAST INDEX FULL SCAN,如下例所示。

image.png

4.3.2        update技巧

如果需要使用b表的值更新a表对应的值,并且ab都是大表,应该怎么做呢?

由于ab都是大表,需要大量的数据连接,显然应该使用hash连接,但是由于是update语句,下面这样是用不了hash连接的,实际还是嵌套循环。


*  方法1:使用视图更新

这个方法要求比较苛刻,关联列在源表中必须是唯一键。

image.png


*  方法2PL/SQL程序块使用rowid更新,这个方法比较常用

 

begin

    for item in (select /*+leading(a) use_hash(a b) */a.rowid, b.operinitinfo

                   from t_userinfo a, t_userserviceinfo b

                  where a.userid = b.userservid ) loop

   

        update t_userinfo

           set username = item.operinitinfo

         where rowid = item.rowid;   

   

    end loop;

 

end;

/

4.3.3        使用并行查询

为了快速获得查询结果,对大表的查询可以设置并行查询。parallel这个提示在数据分析时经常需要使用。

image.png

4.4      hash连接使用场景总结

hash连接是非常常用的连接方式,它有以下特点:

1)适用于较大数据集的连接,并且连接条件必须是等值连接。

2)尽量选择小的数据集作为驱动表在内存中生成hash表。

3)设置一个较大的PGA可以加快hash连接的速度,避免使用临时表空间。

4)多表连接时同样要注意连接顺序,中间结果集尽量小,少做无用功。

    hash连接相对与嵌套循环,它实际上是通过空间来换取时间,因为它需要保存hash表,占用较大的内存,在临时表空间不足的情况下是可能报错的,而嵌套循环一般不会报错,但可能非常慢。

5      分区表

5.1      分区裁剪

分区表在实际项目中广泛应用,它在物理上实现了表数据的分离而逻辑上却仍然是一个表,这样既提高了性能也降低了应用开发的难度,因为分区特性对应用是透明的。

分区裁减是指当查询分区表时,如果查询条件含有分区列,那么优化器会根据分区列的查询条件判断哪些分区是不需要扫描的,这样就可以大大提高查询的性能。为了应用分区裁减,对分区表的查询一般要有分区列的条件。

 

5.2      分区表的执行计划

分区表的执行计划和普通表略有不同,下面介绍下常见的执行计划。

1)只有一层分区,扫描所有分区

由于没有分区列的条件,所以需要扫描所有分区:PARTITION RANGE ALL

image.png

2)只有一层分区,扫描部分分区

由于包含了分区列的条件,所以只需要扫描部分分区:PARTITION RANGE ITERATOR

image.png


3)二级分区

由于包含了一级分区列的条件,所以一级分区只需要扫描部分分区,但是没有二级分区的查询条件,所以对二级分区只能扫描所有分区:PARTITION HASH ALL

image.png

6      总结

执行计划对开发人员来说一直是很神秘的东西,看起来乱七八糟的,不知道是什么含意,为什么稍微变化了一点性能差别就那么大呢?多表连接的执行计划有很多种,有的看起来非常复杂,想读懂都很困难,所以建议一个SQL连接的表的数量不超过四个,要不然很容易出现性能问题。

本文总结了开发过程中常见的执行计划,重点讲解嵌套循环和哈希连接。看完文章后,希望你能回答出以下几个问题:

1)什么情况下应该用嵌套循环?

2)什么情况下应该用hash连接?

3)如何选择驱动表?

4)查询分区表为什么要带分区列的条件?

5)什么是Semi-joinAnti-join

实际应用中的执行计划可能是千变万化的,本文不可能都覆盖到,不过,只要掌握了原理,多思考,相信你就能举一反三的。由于时间仓促外加水平有限,文中疏漏在所难免,如果你有任何高见欢迎讨论和指正。

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。