oracle中sum和count可以嵌套吗_【分享吧】Oracle查询转换

前言

“查询转换”是Oracle解析SQL语句中重要的步骤。其原理是Oracle在解析时通过对原有SQL的等价改写，以达到较高执行效率的方式。

上图展示了SQL的执行过程，当客户提交的语句经过解析后，在提交给优化器之前会进行一个查询转换的步骤。在这个步骤中，Oracle会根据一些规则来决定对目标SQL进行查询转换。根据处理方式的不同，查询转换可以分为两类：

       1、基于规则的查询转化，即当满足规则定义的条件时，对语句进行相应的转化。

       2、基于成本的查询转化，即比较转换前后的SQL成本，当等价改写SQL的成本小于原始SQL成本时，进行SQL转化。

常见的查询转化有：子查询类、视图类、谓词类等。通过以下实例，对常用的查询转化进行说明。

01

子查询类

子查询是SQL中常用的写法，优化器对会对子查询提前进行评估，使得优化器可以更早地介入优化，已获得更优质的执行计划。

1、准备两张表，在子表上创建索引。

2、通过提示PUSH_SUBQ/NO_PUSH_SUBQ对优化的子查询转换进行控制，不使用查询转换，从执行计划可见，出现了FILTER关键字，这是指执行按照T_OBJECTS和T_USERS进行了一个索引的嵌套循环，效率较低。

3、不使用提示，通过执行计划可看出没有出现两表嵌套，而是提前处理了子查询，先生成了MAX CREATED，然后全表扫描T_OBJECTS进行过滤，显然这种方式效率更高。

02

视图类

在视图类的查询转换中最常用的就是视图合并，是指优化器将视图定义的语句进行拆解，不作为整体执行，而是将其定义的语句与外部查询合并起来，再由优化器选择执行计划。

1、创建一个带有过滤条件的视图，并对这个视图进行带过滤条件的查询。在执行计划中可以看出已经没有视图对象出现。视图内部的过滤条件OWNER=’SYS’和外部的过滤条件OBJECT_ID=10都被合并在一起并转换为基表T_OBJECTS的过滤条件。

2、同修改隐含参数，不允许进行简单视图的合并。从执行计划中可以看到出现“VIEW”字样，即没有进行视图合并。

03

谓词类

谓词是指SQL语句中WHERE部分对数据过滤条件。Oracle优化器会将SQL语句中谓词的整体考虑，进行谓词转化。例如：如果存在视图，可以将视图外部的过滤条件推入视图中，这样做可以尽早过滤数据，提高查询效率。

1、新建一个带有过滤条件的视图，然后进行查询。我们看到执行计划没有看到谓词推入，原因是这里采用了前面说的到视图合并。

2、使用no_merge提示后我们看到在ID=1的步骤里可以看到VIEW字样，即视图没有进行合并。再看一下ID=2的步骤，由Predicate Information可见，过滤条件是STATUS=‘VALID’ AND OBJECT_ID=20。可见这里的条件不仅包括视图定义中对表的过滤条件，还包括了从外部传入的过滤条件。即过滤谓词被推入了视图定义中。

04

消除类

消除类是指优化器在生成执行计划之前通过分析，省略SQL中的部分内容。当然消除前后的SQL语句一定是等价的。

1、排序消除，是指在优化器再生成执行计划前，将语句中没有必要的排序操作消除，避免在执行计划中出现排序操作或由排序导致的操作。

看下面查询语句中的排序不是必须的，从Statistics的Sorts部分可以看出都是0，可以看出优化器在生成执行计划时，对排序进行了消除。

2、去重消除，是指如果语句中对象存在主键或唯一约束，那么语句中的DISTINCT是可以消除的。

新建一张表，进行DISTINCT查询。通过下面的执行计划，可以看到默认走了全面扫描，然后用HASH进行了去重。

通过给T_USERS增加唯一索引后，再进行查询。通过执行计划可以看出是直接通过新增榆树UK_USERNAME的索引完成了扫描，不需要再去重了。

3、表消除，是指两表关联且存在主外键关系时，优化器可以消除不必要的表访问以提高效率。

首先创建T_TABLES、T_TABLESPACES两张表，并创建他们之间的外键关系，并对T_TABLES进行查询。虽然SQL中关联到了T_TABLESPACES，但其实是不需要的。所以通过执行计划可以看到优化器对T_TABLESPACE进行了消除。

除了以上介绍的查询转换，Oracle优化器还会对OR、LIKE、IN、BETWEEN、NOT、常量等进行查询转换，以生成更加合理的的执行计划，提高查询效率。希望通过对Oracle查询转换的介绍，能让大家对Oracle优化有更进一步的了解。