Oracle中的增强子查询优化

1、Oracle中的增强子查询优化这篇文章是我偶然在asktom上看到的，当时有人问子查询合并（subquery coalescing），TOM给出了这篇文章的链接：我花了大约一周时间翻译，有很多术语找不到相关翻译就只好自己杜撰一个，根据自己的理解对一些难懂的地方做了注释。如果你发现了错误请不吝指正。文中的例子非常值得一读，目前我没有时间去一一验证哪些已由CBO实现，如果你都看懂了对提高自己的SQL技巧是非常有帮助的。插图只上传了前四个，后面的都是一些关于性能的图表，自己看原文即可。Oracle中的增强型子查询优化作者：Srikanth Bellamkonda ()   

2、60;  Angela Amor ()      Rafi Ahmed ()       Mohamed Zait ()      Andrew Witkowski ()      Chun-Chieh Lin ()            Oracle USA       500 Oracle Parkway

3、       Redwood Shores, CA, USA （版权归原作者所有，未经许可不得转载）摘要本文描述了ORACLE关系数据库系统中的增强型子查询优化。它讨论了几种技术子查询合并，利用窗口函数（译者注：即分析函数）的子查询消除，对分组查询的视图消除(view elimination)。这些技术辨认出查询结构中的冗余成分，并把它们去除，将查询转换为可能更加优化的形式。本文也讨论了新型的并行执行技术，该技术应用广泛，并可用来改善这些经过变换的查询的可扩展性。它还描述了一种反连接（antijoin）的变种，用来优化在有空值的列上带全称量词（译者注：在

4、SQL中指ALL这类量词）的查询。它随后演示了这些优化的结果，表明在执行速度上有着显著的改善。1. 介绍当今的关系数据库系统处理各种复杂的SQL查询, 包括带有聚合函数，UNION/UNION ALL, DISTINCT, 分组(GROUP BY)视图等等的嵌套子查询。这类查询在决策支持系统(DSS)和在线分析处理系统(OLAP)中越来越重要。查询变换是通常推荐的用于优化此类子查询的技术。子查询是SQL的一种强大的组件，大大扩展了它的声明性和表达能力。SQL标准允许子查询被使用在 SELECT, FROM, WHERE 和 HAVING 子句中。决策支持系统的基准测试 TPC-H 文献14 和

5、 TPC-DS 文献15大量使用了子查询。TPC-H 基准测试的22个查询中，差不多有一半用了子查询。大部分是相关子查询，很多都含有聚合函数。所以，高效地执行复杂子查询对数据库至关重要。1.1 ORACLE中的查询变换Oracle执行很多种的查询变换子查询展开(译者注：Subquery Unnesting, 指的是把ANY和EXISTS这类子查询变成连接)，分组和DISTINCT视图的合并(译者注：group-by and distinct view merging, 指的是在带有GROUP BY/DISTICNT的视图/内联视图中，先和外层的表进行连接，过滤掉一些数据然后再做聚合操作)，相同

6、子表达式的消除(译者注：common  sub-expression elimination, 指的是同样的一个表达式出现多次，只需计算一次并多次引用计算结果)，连接谓词下推(译者注：join predicate pushdown，指的是把外层的连接条件推入里层从而达到预先过滤的目的)，连接因式分解(译者注：join factorization, 指的是把UNION的两个子查询中的公共部分提取出来放到UNION之后做，类似提取公因式)，集合操作INTERSECT和MINUS到连接/反连接的转换，OR谓词的扩展(译者注：OR expansion, 指的是把OR或者IN变成一系

7、列UNION ALL)，星型转换(译者注：star transformation, 用于数据仓库的事实表和维表连接，转换为事实表的位图索引的一系列BITAND运算)，分组和DISTINCT的置换（译者注：group-by and distinct  placement，指的是在有GROUP BY或DISTINCT, 同时有WHERE连接条件，CBO先做分组聚合减少行数再做连接操作, 和先前的group-by and distinct view merging恰好相反）。Oracle中的查询变换可能是试探式的或基于成本的。在基于成本的变换中，逻辑变换和物理优化结合起来生成了优

8、化的执行计划。Oracle 10g引进了一种用作基于成本的查询变换的通用框架文献8，和几种状态空间的搜索策略。在基于成本的查询变换过程中，一个查询被复制、变换，同时，现有的基于成本的物理优化器会计算出它的成本。这个过程被重复多次，每次运用一套不同的转换方案；最后，一种或多种转换被选中并应用于原来的查询，如果它的成本计算结果很理想的话。基于成本的变换框架提供了一种机制，能够试探一种或多种变换所生成的状态空间，从而使得Oracle能够以一种高效的方式选择理想的转换方案。基于成本的变换框架能够处理用户查询的多个查询块和多种转换方案之间的依赖关系造成的复杂性。因为有了这种基于成本的查询变换的通用框架，

9、其他富于创新的转换方案能够被添加到Oracle丰富的查询转换技术中。本文介绍了新的转换技术子查询合并，子查询消除，和过滤连接消除。1.2 子查询展开(Subquery Unnesting)子查询展开文献 1,2,8,9是一种数据库系统中广泛使用的转换技术。当一个相关子查询未被展开时，它被元组迭代语义多次求值。这类似于嵌套循环连接(nested-loop join), 因而有效的访问路径、连接方法和连接顺序都无法顾及。Oracle对几乎所有的子查询都进行展开。有两种范围广泛的展开方式一种生成衍生表(内联视图), 另一种把一个子查询合并到它的外层查询。在Oracle中，前者是以一种基于成本的方式应

10、用的，而后者则是以一种试探式的方法完成的。对于非标量子查询（译者注：scalar subqueries标量子查询指的是出现在SELECT部分的子查询）的展开往往变成半连接(semijoin)或反连接(antijoin)。Oracle能够使用索引查找，哈希，排序-合并来进行半连接或反连接。Oracle执行引擎对反连接或半连接结果中的左表元组进行缓存，所以当左表的连接列中有大量重复数据时，对子查询的多次求值能够避免。在缺乏相关索引的情况下，Oracle对存在量词或全称量词的不等比较子查询(例如: > ANY,  < ANY, 等等)进行展开，在不等谓词上做排序-合并

11、连接。如果子查询在全称量词比较（例如, <>ALL）中带有可空列，则无法展开为常规的反连接。Oracle使用了一种反连接的变种，称为“已知空值反连接”（null-aware antijoin），来展开此类子查询。1.3 窗口函数SQL 2003标准文献11 为SQL扩充了窗口函数（注：在Oracle文档中被称作析函数），不仅在表达上简洁优雅，而且可以使得查询优化更为有效，通过避免大量的自连接、查询块从而提高执行效率。窗口函数被广泛使用在许多分析类的应用系统中。Oracle从8i版本开始支持窗口函数。窗口函数的语法看起来像这样：Window_Function (arguments)

12、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包含了窗口函数的语法语义细节。

13、在一个查询块之中，窗口函数在 WHERE,GROUP-BY,和 HAVING 子句之后被求值。在计算一个窗口函数时，Oracle按分区键和排序键对数据进行排序，并且根据需要遍历数据。我们称之为窗口排序执行。显然，如果窗口函数没有分区键和排序键，则排序就没有必要。在这种情况下，Oracle为了计算窗口函数对数据进行缓存，这称作窗口缓存执行。如果Oracle的基于成本的优化器选择了一个计划，使得数据能够以分区键和排序键的顺序产生，那么排序就可以去掉。在这种情况下，窗口缓存执行被使用，Oracle仅仅是把数据缓存并多次访问来计算窗口函数。可是，对于像RANK, ROW_NUMBER, 累计窗口聚合函

14、数(译者注：例如SUM,COUNT等)这些窗口函数来说，假如数据是按顺序产生的，那么连缓存也没有必要。只要保留某些上下文信息（窗口函数值和分区键值），这些函数就能够在处理输入数据的同时被计算。1.3.1 报表类窗口函数本文展示的子查询优化利用了窗口函数中被称为报表窗口函数的一类。这些窗口函数，根据它们的定义，对于每行返回相应分区(按照分区键的定义)中所有行的聚合值。如果一个窗口函数没有排序键和WINDOW子句，或者当每行的WINDOW包含了它所属分区的每一行，则可称为报表窗口函数。在本文中，我们有时候也把这些函数成为窗口汇总。报表窗口函数在比较型分析时很有用，你可以用它来比较一行数据的某个等级

15、的值和更高等级的值。例如，为了计算一只股票的每日交易量对总交易量的比率，每一行（在一天的等级）必须具有横跨所有天数的总计SUM。获得所有天数的总计SUM窗口函数和输出看起来像这样子：Q1 SELECT ticker, day, volume,    SUM(volume) OVER (PARTITION   BY   ticker)  AS "Reporting SUM" FROM   stocks; Table 1. Reporting Window SUM Example

16、 Ticker   Day           Volume      Reporting SUM -GOOG     02-Feb-09     5           18            GOOG 

17、    03-Feb-09     6           18            GOOG     04-Feb-09     7           18     

18、0;      YHOO     02-Feb-09     21          62            YHOO     03-Feb-09     19          62 

19、           YHOO     04-Feb-09     22          62            如果一个报表聚合函数没有任何分区键，那么它返回的值就是所有行的总计，因为仅有一个隐含的分区。我们把这类报表聚合函数称为总计函数。在某些情况下，我们的子查询变换会把总计函数引入到查询中。2.

20、子查询合并子查询合并指的是这样一种技术，即两个子查询在某些条件下能够合并成一个，从而把多次表扫描、多次连接匹配减少为单次表扫描和单次连接匹配。虽然子查询合并被定义为二元操作（译者注：指合并的子查询数目为两个），它可以被依次应用到任意数目的子查询。子查询合并是可行的，因为一个子查询的作用相当于在外层查询的表之上的一个过滤谓词。如果两个查询块产生了同样的结果集，则它们被认为是语义等价的。结构或语法相同的两个查询块也可以确定它们的等价性。如果一个查询块Y的结果是X的结果的子集（不一定是真子集），那么我们就认为查询块X包容查询块Y。X称为包容查询块，而Y则称为被包容查询块。换而言之，假如Y含有一些&q

21、uot;与"过滤谓词P，并且当P不被考虑时X和Y变得等价，则X和Y满足包容属性。（译者注：假设Y是在X的基础上多加一些WHERE过滤谓词，这组谓词P是用AND连到X上去的，那么X就包容Y）包容属性是一个重要属性，它使得我们能够把两个子查询的动作合并到一起。如果两个相连的子查询违背了包容属性，那么它们的过滤谓词就不能结合到一个子查询，因为这个子查询只会产生交集。（译者注：例如 EXISTS A AND EXISTS B, A和B可以是“分别存在”即可，如果改为 EXISTS A AND B则要求满足两个条件的同一行存在，和原来不等价）目前，当两个 EXISTS (或NOT EXISTS

22、) 子查询出现在一个逻辑相连或逻辑分离之中（译者注：指AND或者OR。为方便起见，下文都把conjunctive和disjunctive翻译为AND和OR），Oracle会执行不同的子查询合并。既然ANY和ALL子查询能被相应转换成EXISTS和NOT EXISTS子查询，我们这里不再不讨论ANY/ALL子查询的合并。两个子查询等价而且是同一种类型（即都是EXISTS或都是NOT EXISTS）的情况是微不足道的，因为子查询合并仅仅是把其中一个去除。如果两个等价子查询是不同类型，则合并过程会把两个都去除并代之以FALSE/TRUE谓词，取决于这两个子查询是AND或者OR的关系。2.1 同类型的

23、子查询合并如果两个 AND EXISTS 子查询或者两个OR NOT EXISTS子查询满足包容属性，那么它们就能被合并为一个，合并结果是被包容的那个子查询留下，包容子查询去除。如果是OR EXISTS或者 AND NOT EXISTS的情况，则合并结果是包容子查询被留下，被包容子查询去除。不满足包容属性的子查询也可以被合并，如果它们在去除了一些AND过滤和相关谓词之后是等价的。例如，两个 OR EXISTS 子查询只是在AND过滤和相关谓词不同，其他部分等价，那么就可以被合并为一个EXISTS子查询，带有从两个查询里面取出的不同谓词的OR条件。（译者注：例子：EXISTS (SELECT .

24、 WHERE 条件A AND 条件B) OR EXISTS (SELECT . WHERE 条件A AND 条件C) 可合并为: EXISTS (SELECT . WHERE 条件A AND (条件B OR 条件C) ）两个AND NOT EXISTS子查询也能用相似的办法合并。请看查询Q2, 它有两个OR EXISTS子查询；子查询有相同的相关谓词，但是AND过滤谓词不同。Q2:SELECT   o_orderpriority,   COUNT(*)   FROM   orders WHERE 

25、60;o_orderdate   >=   '1993-07-01'   AND         (EXISTS   (SELECT   *                      FROM   lineitem      &

26、#160;              WHERE   l_orderkey   =   o_orderkey   AND                             l_returnflag   = 

27、;  "R')  OR          EXISTS   (SELECT   *                      FROM   lineitem                &#

28、160;    WHERE   l_orderkey   =   o_orderkey   AND                             l_receiptdate   > l_commitdate)        

29、  )GROUP   BY   o_orderpriority; 我们的子查询合并会把两个EXISTS子查询并成单个带OR过滤谓词的子查询，变成Q3:Q3 SELECT   o_orderpriority,   COUNT(*)   FROM   orders WHERE   o_orderdate   >=   '1993-07-01'   AND  

30、       EXISTS   (SELECT   *                      FROM   lineitem                     WHERE   l_

31、orderkey   =   o_orderkey   AND                             (l_returnflag   =   "R'   OR             

32、                 l_receiptdate   >   l_commitdate) GROUP   BY   o_orderpriority; 2.2 不同类型的子查询的合并合并两个满足包容属性的不同类型的AND子查询需要不同的技术。请看子查询Q4, 这是一个 TPC-H第21号查询的简化版：Q4 SELECT   s_name   

33、FROM   supplier,   lineitem   L1 WHERE   s_suppkey   =   l_suppkey   AND          EXISTS   (SELECT   *                   

34、;   FROM   lineitem   L2                     WHERE   l_orderkey = L1.l_orderkey                          

35、   AND   l_suppkey   <>   L1.l_suppkey)          AND   NOT   EXISTS                   (SELECT   *          &#

36、160;           FROM   lineitem   L3                     WHERE   l_orderkey   =   L1.l_orderkey AND           

37、                 l_suppkey   <>   L1.l_suppkey   AND                             l_receiptdate > l_commitdate); Q4中的

38、两个子查询仅仅是类型不同，同时NOT EXISTS子查询多了一个过滤谓词，l_receiptdate > l_commitdate。子查询合并形成了查询Q5, 它只有单个EXISTS子查询，因此去除了一个lineitem表的访问。Q5 SELECT   s_name   FROM   supplier,   lineitem   L1 WHERE   s_suppkey   =   l_suppkey   AN

39、D          EXISTS   (SELECT   1                      FROM   lineitem   L2                    &#

40、160;WHERE   l_orderkey   =                             L1.l_orderkey   AND                         &

41、#160;   l_suppkey <> L1.l_suppkey                    HAVING   SUM(CASE WHEN l_receiptdate > l_commitdate                          

42、           THEN 1                                   ELSE 0                    &#

43、160;              END)   =   0); （译者注：子查询里不带GROUP BY的HAVING很有些趣味）HAVING子句里的聚合函数返回了满足子查询谓词的行数。合并后的子查询引入了HAVING子句，它带有一个新的过滤谓词检查是否有满足子查询过滤谓词的数据，从而模拟了被去除的NOT EXISTS子查询的动作。对于每一组相关值（译者注：指来自外层的L1表的值），Q4中的子查询的状态可能为如下的三种之一：. 当EIXSTS子查询没有返回

44、数据（即结果为假），两个子查询的AND结果为假。在Q5中，HAVING子句被作用在一个空集合之上，合并后的EXISTS同样也返回假。. 当EIXSTS子查询返回一些数据（即结果为真）并且NOT EIXSTS子查询也返回一些数据（即结果为假），两个子查询的AND结果为假。在Q5中，HAVING子句被作用于一个非空集合，而这个集合含有l_receiptdate > l_commitdate的数据，因此它结果为假。. 当EIXSTS子查询返回一些数据而NOT EIXSTS子查询没有返回数据，因为它有额外的过滤谓词，那么两个子查询的AND结果为真。在Q5中，HAVING子句被作用于一个非空集合，

45、而这个集合不含有l_receiptdate > l_commitdate的数据，因此它结果为真，从而使得合并后的子查询结果为真。上面的讨论证明了Q4和Q5是等价的。假如NOT EXISTS子查询是包容查询，而AND EXISTS子查询被包容，合并过程将会去除两个子查询并以FALSE谓词取代之。当EXISTS子查询有返回数据(即结果为真)时NOT EXISTS子查询必定同样也返回数据(即结果为假)。当NOT EXISTS子查询不返回数据，EXISTS子查询同样也不返回。因此，两个子查询的AND结果恒为假。类似的论证也可适用于两个满足包容属性的EXISTS和NOT EXISTS子查询在OR连

46、接谓词中出现的合并情况。2.3 合并和其他变换在文献8，我们讨论了不同的变换之间是如何互相影响的，而我们的基于成本的变换框架又是如何处理这些可能的影响。子查询合并也不例外，因为合并后的子查询可能是另外的变换方法的作用对象。Q5中的子查询可能进行展开变成Q6，里面含有一个内联视图（衍生表）V。Q6 SELECT   s_name   FROM   supplier,   lineitem   L1,       (SELECT   LX.

47、rowid   xrowid          FROM   lineitem   L2,   lineitem   LX         WHERE   L1.l_suppkey <> LX.l_suppkey   AND             

48、;    L1.l_orderkey   =   LX.l_orderkey        GROUP   BY   LX.rowid        HAVING   SUM(CASE WHEN L2.l_receiptdate > L2.l_commitdate                

49、;          THEN   1                            ELSE   0                       EN

50、D)   =   0)   V WHERE  s_suppkey   =   L1.l_suppkey   AND         L1.rowid   =   V.xrowid; 在视图合并之后，Q6变成了查询Q7，表LX被标记为多余的，既然子合并之后的查询块中LX和L1被用唯一的ROWID列连接起来。因此，LX被去除，所有对它的引用变成对L1的引用。Q7

51、 SELECT   s_name   FROM   supplier,   lineitem   L1,   lineitem   L2 WHERE   s_suppkey   =   L1.l_suppkey   AND          L1.l_orderkey   =   

52、;L2.l_orderkey GROUP   BY   L1.rowid,   S.rowid,   S.s_name HAVING   SUM(CASE   WHEN   L2.l_receiptdate   > L2.l_commitdate                     THE

53、N   1                       ELSE   0                END)   =   0); 这里我们至少有了四种不同的查询供取舍。在大部分情况下，这四种中的哪一种是最佳选择并不清楚。在1.1节中讨论的Oracle的基于

54、成本的变换框架可以用于作出选择。2.4 查询执行的增强Q7中的HAVING谓词，把那些组中至少含有一条收据日期大于提交日期的数据都排除出去。这个谓词和其他类似谓词，比如MIN(l_receiptdate)>'18-Feb-2001', COUNT(*)<=10, SUM(amount_sold) < 2000 （当amount_sold已知为正数，例如在数据库中有一个可信赖的约束）, 等等, 假如不满足，则整个组立刻可以判断为无用（即不会出现子结果集中），并可被推入分组操作(GROUP-BY)来进行对那一组的聚合短路处理。这样提高了执行效率。例如，在Q7中，有

55、一个输入数据l_receiptdate > l_commitdate 导致那一组的SUM聚合出现了值1, 因此使得整个组的数据不可用。类似地，当谓词是SUM(amount_sold)<2000，而且数据库中有一个amount_sold为正数的可信赖约束条件，那么一当某个组的SUM超过了2000, 整个组立即不可用。GROUP-BY 会跳过那些不可用的组的聚合处理。（译者注：那些坚持把约束拿到数据库之外的应用程序去实现的人，请睁大你的双眼，这可能会导致你的查询失去了优化的机会！）并行分组执行也从那些用来降低数据传输量的谓词之中获益。Oracle利用并行分组下推技术(parallel

56、group-by pushdown, 缩写GPD), 分组求值被推入到产生输入的进程（生产者子进程，producer slaves），从而降低通讯成本，提高分组操作的可扩展性。生产者子进程把聚合后的数据分发给另一组进程（消费者子进程，consumer slaves），依据的是分组键的HASH值或值范围。消费者子进程于是结束分组处理并产生结果。查询Q7的带GDP的并行查询计划如图1所示。当生产者子进程P1.Pn在分组处理过程中根据HAVING谓词把那些组过滤掉，数据传输量就能够下降。类似地，那些能够立即使得组被选中的谓词也被推入到分组操作中。一旦一个组被发现为可选，那些不是结果集中所需要的聚合处

57、理就可被跳过（译者注：比如HAVING中用到了COUNT(*)>0谓词，而COUNT(*)没有出现在SELECT中，那么一旦满足了条件，剩下的计数就不必要继续了）。这类谓词的例子有MIN(l_receiptdate)<'18-Feb-2001',  COUNT(*)>10,  SUM(amount_sold)>2000, 当amount_sold已知为正数。图1 并行分组操作下推3. 分组视图消除在这一节，我们讨论一种成为过滤表消除的技术，这是基于过滤连接的思想。一个过滤连接或者是一个半连接(semijoin)或者

58、是一个等值内连接，连接发生在所涉及的两个表之一的一个唯一列。在这里我们把一个唯一列Y以下划线来表示，并用一个非标准的记号=来表示一个等值半连接。R是一个表，而T1和T2分别是来自同一个基表或衍生表T的两个实例。T1,T2要么是含有完全相同的过滤谓词，如果有的话，要么是T1上的过滤谓词比T2上的更为严格。在下列的情形中，T2和过滤连接可以被去除。R.X = T1._Y_ AND R.X = T2._Y_ R.X = T1._Y_R.X = T1.Y AND R.X = T2.Y R.X = T1.YR.X = T1.Y AND R.X = T2.Y R.X = T1.Y假设非过滤连接（如果有的话

59、）先发生。过滤连接最多会保持R中的所有结果行，既然一个过滤连接仅仅能够滤掉R中的数据，这与内连接不同，内连接既可滤掉数据也可生成重复数据。过滤连接使得T2表变成多余的，因此可以去除。虽然这种技术看上去和 AND EXISTS 子查询的合并非常相似，接下来我们要展示一下这种技术的不同应用。3.1 视图消除请看Q8, 它是一个TPC-H第18号查询的简化版本： Q8 SELECT o_orderkey,c_custkey,   SUM(l_quantity)   FROM orders,   lineitem   L1

60、,   customers WHERE o_orderkey   =   l_orderkey   AND        c_custkey   =   o_custkey   AND        o_orderkey   IN (SELECT   l_orderkey        

61、                  FROM   lineitem   L2                         GROUP   BY   l_orderkey       &#

62、160;                 HAVING   SUM(l_quantity)   >   30) GROUP   BY   o_orderkey,   o_totalprice; 查询Q8经过展开变成Q9。Q9中由展开变形产生的内联视图（衍生表）V2 不需要半连接，既然它是一个等值连接，并且V2上的连接列是唯一的，这是V2上对此列做分组操作的结果。

63、Q9 SELECT   o_orderkey,c_custkey,   SUM(l_quantity)   FROM   orders,   lineitem   L1,   customers,         (SELECT   l_orderkey            FROM 

64、 lineitem   L2           GROUP   BY   l_orderkey          HAVING   SUM(l_quantity)   >   30)   V2 WHERE   o_orderkey   =   V2.l_orde

65、rkey   AND          o_orderkey   =   L1.l_orderkey   AND          c_custkey   =   o_custkey GROUP   BY   o_orderkey,   c_custkey; 利用分组和连接置换（即分组置换）文献5,

66、6,8，另一个包含表L1的视图V1能被产生出来，如Q10所示；SUM(l_quantity)被加到V2的SELECT列表中，Q9的语义不变。Q10 SELECT   o_orderkey, c_custkey, SUM(V1.qty)   FROM   orders,   customers,          (SELECT l_orderkey, SUM(l_quantity) qty        

67、    FROM   lineitem   L2            GROUP   BY   l_orderkey           HAVING   SUM(l_quantity)   >   30)   V2,      &

68、#160;   (SELECT   l_orderkey, SUM(l_quantity) qty             FROM   lineitem   L1           GROUP   BY   l_orderkey)   V1 WHERE   o_orderkey  &#

69、160;=   V1.l_orderkey   AND         o_orderkey   =   V2.l_orderkey   AND         c_custkey   =   o_custkey GROUP   BY   o_orderkey,   c_cust

70、key; 正如所示，V1和V2是同一视图的两个不同实例，只是V2的过滤谓词比V1更严格，因为V2多了个HAVING子句。不仅如此，V1和V2是在一个唯一列o_orderkey之上和ORDERS表进行等值连接的，因为它是视图中唯一的分组键；因此这两个连接是过滤连接。所以，V1可以去除，对V1的引用可以被对V2的引用取代。去除Q10的过滤视图后得到了Q11。Q11 SELECT   o_orderkey, c_custkey, SUM(V2.qty)   FROM   orders,   customers, 

71、0;        (SELECT   o_orderkey,   SUM(l_quantity)             FROM   lineitem           GROUP   BY   l_orderkey           HAV

72、ING   SUM(l_quantity)   >   30)   V2, WHERE   o_orderkey   =   V2.l_orderkey   AND         c_custkey   =   o_custkey GROUP   BY   o_orderkey, 

73、  c_custkey; 如果Q9中的视图V2被合并，另外一种利用过滤连接的论证方法也能得到去除外层查询的lineitem表的同样结果。4. 利用窗口函数的子查询消除这种技术利用窗口函数代替子查询文献11，因此减少了表访问、连接求值的次数并改善查询的性能。这里讨论的子查询消除技术有些在Oracle 9i引入，有些只是作为文献出版文献13。在它的简化版中，包含聚合子查询(subsumed aggregation subqueries)被利用窗口函数所消除。当一个外层查询块如果含有在子查询中出现的所有的表和谓词，它就称为包含一个子查询。外层查询块可能还有额外的表和谓词。显然，包含属性和第

74、二节谈到的包容属性是不同的。这种技术利用了无损连接属性和代数聚合（例如 SUM, MIN, MAX, COUNT, AVG等等）。Q12 展示了适用子查询消除的包含聚合子查询的一种形式。T1和T2可能是基表或衍生表或多表的连接结果。子查询中的聚合AGG参与了一种和外层查询的列T2.z的关系比较 (relop)，相关列则在T1.y列。Q12 SELECT   T1.x FROM   T1,   T2 WHERE   T1.y   =   T2.y   and

75、          T2.z   relop   (SELECT   AGG(T2.w)                            FROM   T2              

76、             WHERE   T2.y   =   T1.y); 假设T1和T2的连接是一个无损连接，即T2.y是一个指向主键T1.y的外键，那么引入一个把相关列作为分区键的窗口函数就可消除子查询。如Q13所示。Q13 SELECT   V.x   FROM   (SELECT   T1.x,   T2.z,    

77、                AGG   (T2.w) OVER   (PARTITION   BY   T2.y)                          AS   win_agg      &#

78、160;      FROM   T1,   T2            WHERE   T1.y   =   T2.y)   V WHERE   V.z   relop   win_agg; 为了利用窗口函数做子查询消除，T1和T2的连接并不要求是无损的。然而，无损连接可以导致另一种变形，允许优化

79、器考虑更多连接置换。上述Q12的变种形式，如非相关子查询，或者有额外表和谓词，或者没有聚合，或者当子查询和外层查询都有分组，都可以利用子查询消除技术进行变形。我们在随后的章节中会给出例子。4.1 相关包含子查询请看查询 Q14, 这是一个TPC-H第二号查询的简化版本。外层查询多了表PARTS和那个表上的谓词。子查询和PARTS表相关并且被外层查询包含。 Q14 SELECT   s_name,   n_name,   p_partkey   FROM   parts   P

80、,   supplier,   partsupp,          nation,   region WHERE   p_partkey   =   ps_partkey   AND          s_suppkey   =   ps_suppkey   AND  

81、;        s_nationkey   =   n_nationkey   AND          n_regionkey   =   r_regionkey   AND          p_size   =   36   AND 

82、0;        r_name   =   'ASIA'   AND          ps_supplycost IN                        (SELECT   MIN   (ps_su

83、pplycost)                           FROM   partsupp,   supplier,   nation, region                       

84、  WHERE   P.p_partkey   =   ps_partkey   AND                                  s_suppkey   =   ps_suppkey   AND 

85、0;                                s_nationkey   =   n_nationkey   AND                      

86、0;           n_regionkey   =   r_regionkey   AND                                  r_name   =   'ASIA&

87、#39;); 子查询消除技术把Q14变换为Q15: Q15 SELECT   s_name,   n_name,   p_partkey   FROM   (SELECT ps_supplycost,                  MIN   (ps_supplycost) OVER (PARTITION   BY 

88、60;ps_partkey)                        AS   min_ps,                  s_name,   n_name,   p_partkey        

89、     FROM parts,   supplier,   partsupp,                  nation,   region            WHERE   p_partkey   =   ps_partkey 

90、;  AND                    s_suppkey   =   ps_suppkey   AND                    s_nationkey   =   n_nationkey   AND &

91、#160;                  n_regionkey   =   r_regionkey   AND                    p_size   =   36   AND       

92、;             r_name   =   'ASIA')   V WHERE    V.ps_supplycost   =   V.min_ps; 由PARTSUPP和PARTS表的连接产生的重复行，如果有的话，在这里无关紧要，因为聚合函数是MIN。如果聚合函数不是MIN/MAX，或者对额外表的连接（在本例中是PARTS表）不是无损的，那么窗口函数的计算必须在视图中完成

93、，然后才和附加表连接。这正是TPC-H第17号查询的情形，子查询消除把它变换为Q16: Q16 SELECT   SUM(V.avg_extprice)/7   AS   avg_yearly   FROM   parts,          (SELECT   (CASE WHEN l_quantity < (1.2*AVG(l_quantity) OVER (PARTITION BY l_partkey)

94、                         THEN l_extprice                          ELSE   NULL             

95、        END)   avg_extprice,                     l_partkey             FROM   lineitem)   V WHERE   p_partkey  

96、0;=   V.l_partkey   AND          V.avg_extprice   IS   NOT   NULL   AND          P_brand   =   'Brand#23'   AND       

97、0;  p_container   =   'MED  BOX' 4.2 非相关包含子查询请看查询Q17, 这是TPC-H第15号查询的一个简化版。Q17有一个和外层查询不相关的聚合子查询，子查询和外查询同时引用一个相同的分组视图（衍生表）V.Q17 WITH  V  AS  (SELECT l_suppkey,                      SUM(l_extprice) revenue