oracle子查询的优化总结

1.1 ORACLE中的查询变换
Oracle执行很多种的查询变换——子查询展开(译者注：Subquery Unnesting,指的是把ANY和EXISTS这类子查询变成连接)，分组和DISTINCT视图的合并(译者注：group-by and distinct view merging,指的是在带有GROUP BY/DISTICNT的视图/内联视图中，先和外层的表进行连接，过滤掉一些数据然后再做聚合操作)，相同子表达式的消除(译者注：common sub-expression elimination,指的是同样的一个表达式出现多次，只需计算一次并多次引用计算结果)，连接谓词下推(译者注：join predicate pushdown，指的是把外层的连接条件推入里层从而达到预先过滤的目的)，连接因式分解(译者注：join factorization,指的是把UNION的两个子查询中的公共部分提取出来放到UNION之后做，类似提取公因式)，集合操作INTERSECT和MINUS到连接/反连接的转换，OR谓词的扩展(译者注：OR expansion,指的是把OR或者IN变成一系列UNION ALL)，星型转换(译者注：star transformation,用于数据仓库的事实表和维表连接，转换为事实表的位图索引的一系列BITAND运算)，分组和DISTINCT的置换（译者注：group-by and distinct placement，指的是在有GROUP BY或DISTINCT,同时有WHERE连接条件，CBO先做分组聚合减少行数再做连接操作,和先前的group-by and distinct view merging恰好相反）。Oracle中的查询变换可能是试探式的或基于成本的。在基于成本的变换中，逻辑变换和物理优化结合起来生成了优化的执行计划。
2.子查询合并
子查询合并指的是这样一种技术，即两个子查询在某些条件下能够合并成一个，从而把多次表扫描、多次连接匹配减少为单次表扫描和单次连接匹配。虽然子查询合并被定义为二元操作（译者注：指合并的子查询数目为两个），它可以被依次应用到任意数目的子查询。子查询合并是可行的，因为一个子查询的作用相当于在外层查询的表之上的一个过滤谓词。
Oracle对几乎所有的子查询都进行展开。有两种范围广泛的展开方式——一种生成衍生表(内联视图),另一种把一个子查询合并到它的外层查询。在Oracle中，前者是以一种基于成本的方式应用的，而后者则是以一种试探式的方法完成的。
对于非标量子查询（译者注：scalar subqueries标量子查询指的是出现在SELECT部分的子查询）的展开往往变成半连接(semijoin)或反连接(antijoin)。Oracle能够使用索引查找，哈希，排序-合并来进行半连接或反连接。Oracle执行引擎对反连接或半连接结果中的左表元组进行缓存，所以当左表的连接列中有大量重复数据时，对子查询的多次求值能够避免。在缺乏相关索引的情况下，Oracle对存在量词或全称量词的不等比较子查询(例如: > ANY, < ANY,等等)进行展开，在不等谓词上做排序-合并连接。
1.3.1报表类窗口函数
本文展示的子查询优化利用了窗口函数中被称为报表窗口函数的一类。这些窗口函数，根据它们的定义，对于每行返回相应分区(按照分区键的定义)中所有行的聚合值。如果一个窗口函数没有排序键和WINDOW子句，或者当每行的WINDOW包含了它所属分区的每一行，则可称为报表窗口函数。在本文中，我们有时候也把这些函数成为窗口汇总。
Window_Function ([arguments]) OVER (
[ PARTITION BY pk1 [, pk2,...] ]
[ ORDER BY ok1 [, ok2, ...] [WINDOW clause] ] )
窗口函数在由分区键PARTITION BY pk1, pk2, ...定义的分区中求值，每个分区的数据以排序键ORDER BY ok1,ok2,....进行排序。WINDOW子句为每行数据定义窗口(起止点)。SQL聚合函数(SUM, MIN, COUNT等等),排名函数(RANK, ROW_NUMBER,等等)，或参照函数(LAG, LEAD, FIRST_VALUE,等等)可被用作窗口函数。ANSI SQL标准[文献10,11]包含了窗口函数的语法语义细节。
2.1同类型的子查询合并
如果两个AND EXISTS子查询或者两个OR NOT EXISTS子查询满足包容属性，那么它们就能被合并为一个，合并结果是被包容的那个子查询留下，包容子查询去除。如果是OR EXISTS或者AND NOT EXISTS的情况，则合并结果是包容子查询被留下，被包容子查询去除。
如果Oracle的基于成本的优化器选择了一个计划，使得数据能够以分区键和排序键的顺序产生，那么排序就可以去掉。在这种情况下，窗口缓存执行被使用，Oracle仅仅是把数据缓存并多次访问来计算窗口函数。可是，对于像RANK, ROW_NUMBER,累计窗口聚合函数(译者注：例如SUM,COUNT等)这些窗口函数来说，假如数据是按顺序产生的，那么连缓存也没有必要。只要保留某些上下文信息（窗口函数值和分区键值），这些函数就能够在处理输入数据的同时被计算。
Oracle 10g引进了一种用作基于成本的查询变换的通用框架[文献8]，和几种状态空间的搜索策略。在基于成本的查询变换过程中，一个查询被复制、变换，同时，现有的基于成本的物理优化器会计算出它的成本。这个过程被重复多次，每次运用一套不同的转换方案；最后，一种或多种转换被选中并应用于原来的查询，如果它的成本计算结果很理想的话。基于成本的变换框架提供了一种机制，能够试探一种或多种变换所生成的状态空间，从而使得Oracle能够以一种高效的方式选择理想的转换方案。基于成本的变换框架能够处理用户查询的多个查询块和多种转换方案之间的依赖关系造成的复杂性。
在一个查询块之中，窗口函数在WHERE,GROUP-BY,和HAVING子句之后被求值。在计算一个窗口函数时，Oracle按分区键和排序键对数据进行排序，并且根据需要遍历数据。我们称之为窗口排序执行。显然，如果窗口函数没有分区键和排序键，则排序就没有必要。在这种情况下，Oracle为了计算窗口函数对数据进行缓存，这称作窗口缓存执行。
目前，当两个EXISTS (或NOT EXISTS)子查询出现在一个逻辑相连或逻辑分离之中（译者注：指AND或者OR。为方便起见，下文都把conjunctive和disjunctive翻译为AND和OR），Oracle会执行不同的子查询合并。既然ANY和ALL子查询能被相应转换成EXISTS和NOT EXISTS子查询，我们这里不再不讨论ANY/ALL子查询的合并。两个子查询等价而且是同一种类型（即都是EXISTS或都是NOT EXISTS）的情况是微不足道的，因为子查询合并仅仅是把其中一个去除。如果两个等价子查询是不同类型，则合并过程会把两个都去除并代之以FALSE/TRUE谓词，取决于这两个子处理各种复杂的SQL查询,包括带有聚合函数，UNION/UNION ALL, DISTINCT,分组(GROUP BY)视图等等的嵌套子查询。这类查询在决策支持系统(DSS)和在线分析处理系统(OLAP)中越来越重要。查询变换是通常推荐的用于优化此类子查询的技术。
子查询是SQL的一种强大的组件，大大扩展了它的声明性和表达能力。SQL标准允许子查询被使用在SELECT, FROM, WHERE和HAVING子句中。决策支持系统的基准测试TPC-H [文献14]和TPC-DS [文献15]大量使用了子查询。TPC-H基准测试的22个查询中，差不多有一半用了子查询。大部分是相关子查询，很多都含有聚合函数。所以，高效地执行复杂子查询对数据库至关重要。
包容属性是一个重要属性，它使得我们能够把两个子查询的动作合并到一起。如果两个相连的子查询违背了包容属性，那么它们的过滤谓词就不能结合到一个子查询，因为这个子查询只会产生交集。
（译者注：例如EXISTS A AND EXISTS B, A和B可以是“分别存在”即可，如果改为EXISTS A AND B则要求满足两个条件的同一行存在，和原来不等价）
SUM(volume) OVER (PARTITION BY ticker) AS "Reporting SUM"
FROM stocks;
Table 1. Reporting Window SUM Example
Ticker Day Volume Reporting SUM
-------------------------------------------------
如果两个查询块产生了同样的结果集，则它们被认为是语义等价的。结构或语法相同的两个查询块也可以确定它们的等价性。
如果一个查询块Y的结果是X的结果的子集（不一定是真子集），那么我们就认为查询块X包容查询块Y。X称为包容查询块，而Y则称为被包容查询块。换而言之，假如Y含有一些"与"过滤谓词P，并且当P不被考虑时X和Y变得等价，则X和Y满足包容属性。（译者注：假设Y是在X的基础上多加一些WHERE过滤谓词，这组谓词P是用AND连到X上去的，那么X就包容Y）
本文描述了ORACLE关系数据库系统中的增强型子查询优化。它讨论了几种技术——子查询合并，利用窗口函数（译者注：即分析函数）的子查询消除，对分组查询的视图消除(view elimination)。这些技术辨认出查询结构中的冗余成分，并把它们去除，将查询转换为可能更加优化的形式。本文也讨论了新型的并行执行技术，该技术应用广泛，并可用来改善这些经过变换的查询的可扩展性。它还描述了一种反连接（antijoin）的变种，用来优化在有空值的列上带全称量词（译者注：在SQL中指ALL这类量词）的查询。它随后演示了这些优化的结果，表明在执行速度上有着显著的改善。
如果子查询在全称量词比较（例如, <>ALL）中带有可空列，则无法展开为常规的反连接。Oracle使用了一种反连接的变种，称为“已知空值反连接”（null-aware antijoin），来展开此类子查询。