行列转换总结

这篇文章是我偶然在asktom上看到的，当时有人问子查询合并（subquery coalescing），TOM给出了这篇文章的链接：
http://www.vldb.org/pvldb/2/vldb09-423.pdf

我花了大约一周时间翻译，有很多术语找不到相关翻译就只好自己杜撰一个，根据自己的理解对一些难懂的地方做了注释。如果你发现了错误请不吝指正。
文中的例子非常值得一读，目前我没有时间去一一验证哪些已由CBO实现，如果你都看懂了对提高自己的SQL技巧是非常有帮助的。

插图只上传了前四个，后面的都是一些关于性能的图表，自己看原文即可。

Oracle中的增强型子查询优化

作者：Srikanth Bellamkonda ([e-mail])
      Angela Amor ([e-mail])
      Rafi Ahmed ([e-mail]) 
      Mohamed Zait ([e-mail])
      Andrew Witkowski ([e-mail])
      Chun-Chieh Lin ([e-mail])
      
      Oracle USA 
      500 Oracle Parkway 
      Redwood Shores, CA, USA 

（版权归原作者所有，未经许可不得转载）

摘要
本文描述了ORACLE关系数据库系统中的增强型子查询优化。它讨论了几种技术——子查询合并，利用窗口函数（译者注：即分析函数）的子查询消除，对分组查询的视图消除(view elimination)。这些技术辨认出查询结构中的冗余成分，并把它们去除，将查询转换为可能更加优化的形式。本文也讨论了新型的并行执行技术，该技术应用广泛，并可用来改善这些经过变换的查询的可扩展性。它还描述了一种反连接（antijoin）的变种，用来优化在有空值的列上带全称量词（译者注：在SQL中指ALL这类量词）的查询。它随后演示了这些优化的结果，表明在执行速度上有着显著的改善。


1. 介绍
当今的关系数据库系统处理各种复杂的SQL查询, 包括带有聚合函数，UNION/UNION ALL, DISTINCT, 分组(GROUP BY)视图等等的嵌套子查询。这类查询在决策支持系统(DSS)和在线分析处理系统(OLAP)中越来越重要。查询变换是通常推荐的用于优化此类子查询的技术。

子查询是SQL的一种强大的组件，大大扩展了它的声明性和表达能力。SQL标准允许子查询被使用在 SELECT, FROM, WHERE 和 HAVING 子句中。决策支持系统的基准测试 TPC-H [文献14] 和 TPC-DS [文献15]大量使用了子查询。TPC-H 基准测试的22个查询中，差不多有一半用了子查询。大部分是相关子查询，很多都含有聚合函数。所以，高效地执行复杂子查询对数据库至关重要。

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中的查询变换可能是试探式的或基于成本的。在基于成本的变换中，逻辑变换和物理优化结合起来生成了优化的执行计划。

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

因为有了这种基于成本的查询变换的通用框架，其他富于创新的转换方案能够被添加到Oracle丰富的查询转换技术中。本文介绍了新的转换技术——子查询合并，子查询消除，和过滤连接消除。

1.2 子查询展开(Subquery Unnesting)

子查询展开[文献 1,2,8,9]是一种数据库系统中广泛使用的转换技术。当一个相关子查询未被展开时，它被元组迭代语义多次求值。这类似于嵌套循环连接(nested-loop join), 因而有效的访问路径、连接方法和连接顺序都无法顾及。

Oracle对几乎所有的子查询都进行展开。有两种范围广泛的展开方式——一种生成衍生表(内联视图), 另一种把一个子查询合并到它的外层查询。在Oracle中，前者是以一种基于成本的方式应用的，而后者则是以一种试探式的方法完成的。

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

如果子查询在全称量词比较（例如, <>ALL）中带有可空列，则无法展开为常规的反连接。Oracle使用了一种反连接的变种，称为“已知空值反连接”（null-aware antijoin），来展开此类子查询。

1.3 窗口函数
SQL 2003标准[文献11] 为SQL扩充了窗口函数（注：在Oracle文档中被称作析函数），不仅在表达上简洁优雅，而且可以使得查询优化更为有效，通过避免大量的自连接、查询块从而提高执行效率。窗口函数被广泛使用在许多分析类的应用系统中。Oracle从8i版本开始支持窗口函数。窗口函数的语法看起来像这样：

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

在一个查询块之中，窗口函数在 WHERE,GROUP-BY,和 HAVING 子句之后被求值。在计算一个窗口函数时，Oracle按分区键和排序键对数据进行排序，并且根据需要遍历数据。我们称之为窗口排序执行。显然，如果窗口函数没有分区键和排序键，则排序就没有必要。在这种情况下，Oracle为了计算窗口函数对数据进行缓存，这称作窗口缓存执行。

如果Oracle的基于成本的优化器选择了一个计划，使得数据能够以分区键和排序键的顺序产生，那么排序就可以去掉。在这种情况下，窗口缓存执行被使用，Oracle仅仅是把数据缓存并多次访问来计算窗口函数。可是，对于像RANK, ROW_NUMBER, 累计窗口聚合函数(译者注：例如SUM,COUNT等)这些窗口函数来说，假如数据是按顺序产生的，那么连缓存也没有必要。只要保留某些上下文信息（窗口函数值和分区键值），这些函数就能够在处理输入数据的同时被计算。

1.3.1 报表类窗口函数
本文展示的子查询优化利用了窗口函数中被称为报表窗口函数的一类。这些窗口函数，根据它们的定义，对于每行返回相应分区(按照分区键的定义)中所有行的聚合值。如果一个窗口函数没有排序键和WINDOW子句，或者当每行的WINDOW包含了它所属分区的每一行，则可称为报表窗口函数。在本文中，我们有时候也把这些函数成为窗口汇总。

报表窗口函数在比较型分析时很有用，你可以用它来比较一行数据的某个等级的值和更高等级的值。例如，为了计算一只股票的每日交易量对总交易量的比率，每一行（在一天的等级）必须具有横跨所有天数的总计SUM。获得所有天数的总计SUM窗口函数和输出看起来像这样子：

Q1 
SELECT ticker, day, volume, 
   SUM(volume) OVER (PARTITION   BY   ticker)  AS "Reporting SUM" 
FROM   stocks; 

Table 1. Reporting Window SUM Example 

Ticker   Day           Volume      Reporting SUM 
-------------------------------------------------
GOOG     02-Feb-09     5           18            
GOOG     03-Feb-09     6           18            
GOOG     04-Feb-09     7           18            
YHOO     02-Feb-09     21          62            
YHOO     03-Feb-09     19          62            
YHOO     04-Feb-09     22          62            

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

2. 子查询合并
子查询合并指的是这样一种技术，即两个子查询在某些条件下能够合并成一个，从而把多次表扫描、多次连接匹配减少为单次表扫描和单次连接匹配。虽然子查询合并被定义为二元操作（译者注：指合并的子查询数目为两个），它可以被依次应用到任意数目的子查询。子查询合并是可行的，因为一个子查询的作用相当于在外层查询的表之上的一个过滤谓词。

如果两个查询块产生了同样的结果集，则它们被认为是语义等价的。结构或语法相同的两个查询块也可以确定它们的等价性。

如果一个查询块Y的结果是X的结果的子集（不一定是真子集），那么我们就认为查询块X包容查询块Y。X称为包容查询块，而Y则称为被包容查询块。换而言之，假如Y含有一些"与"过滤谓词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) 子查询出现在一个逻辑相连或逻辑分离之中（译者注：指AND或者OR。为方便起见，下文都把conjunctive和disjunctive翻译为AND和OR），Oracle会执行不同的子查询合并。既然ANY和ALL子查询能被相应转换成EXISTS和NOT EXISTS子查询，我们这里不再不讨论ANY/ALL子查询的合并。两个子查询等价而且是同一种类型（即都是EXISTS或都是NOT EXISTS）的情况是微不足道的，因为子查询合并仅仅是把其中一个去除。如果两个等价子查询是不同类型，则合并过程会把两个都去除并代之以FALSE/TRUE谓词，取决于这两个子查询是AND或者OR的关系。

2.1 同类型的子查询合并
如果两个 AND EXISTS 子查询或者两个OR NOT EXISTS子查询满足包容属性，那么它们就能被合并为一个，合并结果是被包容的那个子查询留下，包容子查询去除。如果是OR EXISTS或者 AND NOT EXISTS的情况，则合并结果是包容子查询被留下，被包容子查询去除。

不满足包容属性的子查询也可以被合并，如果它们在去除了一些AND过滤和相关谓词之后是等价的。例如，两个 OR EXISTS 子查询只是在AND过滤和相关谓词不同，其他部分等价，那么就可以被合并为一个EXISTS子查询，带有从两个查询里面取出的不同谓词的OR条件。（译者注：例子：EXISTS (SELECT ... 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   o_orderdate   >=   '1993-07-01'   AND 
        (EXISTS   (SELECT   * 
                     FROM   lineitem 
                    WHERE   l_orderkey   =   o_orderkey   AND 
                            l_returnflag   =   "R')  OR 
         EXISTS   (SELECT   * 
                     FROM   lineitem 
                    WHERE   l_orderkey   =   o_orderkey   AND 
                            l_receiptdate   > l_commitdate) 
         )
GROUP   BY   o_orderpriority; 

我们的子查询合并会把两个EXISTS子查询并成单个带OR过滤谓词的子查询，变成Q3:

Q3 
SELECT   o_orderpriority,   COUNT(*) 
  FROM   orders 
WHERE   o_orderdate   >=   '1993-07-01'   AND 
         EXISTS   (SELECT   * 
                     FROM   lineitem 
                    WHERE   l_orderkey   =   o_orderkey   AND 
                            (l_returnflag   =   "R'   OR 
                             l_receiptdate   >   l_commitdate)) 
GROUP   BY   o_orderpriority; 

2.2 不同类型的子查询的合并
合并两个满足包容属性的不同类型的AND子查询需要不同的技术。请看子查询Q4, 这是一个 TPC-H第21号查询的简化版：

Q4 
SELECT   s_name 
  FROM   supplier,   lineitem   L1 
WHERE   s_suppkey   =   l_suppkey   AND 
         EXISTS   (SELECT   * 
                     FROM   lineitem   L2 
                    WHERE   l_orderkey = L1.l_orderkey 
                            AND   l_suppkey   <>   L1.l_suppkey) 
         AND   NOT   EXISTS 
                  (SELECT   * 
                     FROM   lineitem   L3 
                    WHERE   l_orderkey   =   L1.l_orderkey AND 
                            l_suppkey   <>   L1.l_suppkey   AND 
                            l_receiptdate > l_commitdate); 

Q4中的两个子查询仅仅是类型不同，同时NOT EXISTS子查询多了一个过滤谓词，l_receiptdate > l_commitdate。子查询合并形成了查询Q5, 它只有单个EXISTS子查询，因此去除了一个lineitem表的访问。

Q5 
SELECT   s_name 
  FROM   supplier,   lineitem   L1 
WHERE   s_suppkey   =   l_suppkey   AND 
         EXISTS   (SELECT   1 
                     FROM   lineitem   L2 
                    WHERE   l_orderkey   = 
                            L1.l_orderkey   AND 
                            l_suppkey <> L1.l_suppkey 
                   HAVING   SUM(CASE WHEN l_receiptdate > l_commitdate 
                                     THEN 1   
                                ELSE 0   
                                END)   =   0); 
（译者注：子查询里不带GROUP BY的HAVING很有些趣味）

HAVING子句里的聚合函数返回了满足子查询谓词的行数。合并后的子查询引入了HAVING子句，它带有一个新的过滤谓词检查是否有满足子查询过滤谓词的数据，从而模拟了被去除的NOT EXISTS子查询的动作。

对于每一组相关值（译者注：指来自外层的L1表的值），Q4中的子查询的状态可能为如下的三种之一：
. 当EIXSTS子查询没有返回数据（即结果为假），两个子查询的AND结果为假。在Q5中，HAVING子句被作用在一个空集合之上，合并后的EXISTS同样也返回假。
. 当EIXSTS子查询返回一些数据（即结果为真）并且NOT EIXSTS子查询也返回一些数据（即结果为假），两个子查询的AND结果为假。在Q5中，HAVING子句被作用于一个非空集合，而这个集合含有l_receiptdate > l_commitdate的数据，因此它结果为假。
. 当EIXSTS子查询返回一些数据而NOT EIXSTS子查询没有返回数据，因为它有额外的过滤谓词，那么两个子查询的AND结果为真。在Q5中，HAVING子句被作用于一个非空集合，而这个集合不含有l_receiptdate > l_commitdate的数据，因此它结果为真，从而使得合并后的子查询结果为真。

上面的讨论证明了Q4和Q5是等价的。假如NOT EXISTS子查询是包容查询，而AND EXISTS子查询被包容，合并过程将会去除两个子查询并以FALSE谓词取代之。当EXISTS子查询有返回数据(即结果为真)时NOT EXISTS子查询必定同样也返回数据(即结果为假)。当NOT EXISTS子查询不返回数据，EXISTS子查询同样也不返回。因此，两个子查询的AND结果恒为假。

类似的论证也可适用于两个满足包容属性的EXISTS和NOT EXISTS子查询在OR连接谓词中出现的合并情况。

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

Q6 
SELECT   s_name 
  FROM   supplier,   lineitem   L1, 
      (SELECT   LX.rowid   xrowid 
         FROM   lineitem   L2,   lineitem   LX 
        WHERE   L1.l_suppkey <> LX.l_suppkey   AND 
                L1.l_orderkey   =   LX.l_orderkey 
       GROUP   BY   LX.rowid 
       HAVING   SUM(CASE WHEN L2.l_receiptdate > L2.l_commitdate 
                         THEN   1   
                         ELSE   0   
                    END)   =   0)   V 
WHERE  s_suppkey   =   L1.l_suppkey   AND 
        L1.rowid   =   V.xrowid; 

在视图合并之后，Q6变成了查询Q7，表LX被标记为多余的，既然子合并之后的查询块中LX和L1被用唯一的ROWID列连接起来。因此，LX被去除，所有对它的引用变成对L1的引用。

Q7 
SELECT   s_name 
  FROM   supplier,   lineitem   L1,   lineitem   L2 
WHERE   s_suppkey   =   L1.l_suppkey   AND 
         L1.l_orderkey   =   L2.l_orderkey 
GROUP   BY   L1.rowid,   S.rowid,   S.s_name 
HAVING   SUM(CASE   WHEN   L2.l_receiptdate   > L2.l_commitdate 
                    THEN   1   
                    ELSE   0   
             END)   =   0); 

这里我们至少有了四种不同的查询供取舍。在大部分情况下，这四种中的哪一种是最佳选择并不清楚。在1.1节中讨论的Oracle的基于成本的变换框架可以用于作出选择。

2.4 查询执行的增强
Q7中的HAVING谓词，把那些组中至少含有一条收据日期大于提交日期的数据都排除出去。这个谓词和其他类似谓词，比如MIN(l_receiptdate)>'18-Feb-2001', COUNT(*)<=10, SUM(amount_sold) < 2000 （当amount_sold已知为正数，例如在数据库中有一个可信赖的约束）, 等等, 假如不满足，则整个组立刻可以判断为无用（即不会出现子结果集中），并可被推入分组操作(GROUP-BY)来进行对那一组的聚合短路处理。这样提高了执行效率。例如，在Q7中，有一个输入数据l_receiptdate > l_commitdate 导致那一组的SUM聚合出现了值1, 因此使得整个组的数据不可用。类似地，当谓词是SUM(amount_sold)<2000，而且数据库中有一个amount_sold为正数的可信赖约束条件，那么一当某个组的SUM超过了2000, 整个组立即不可用。GROUP-BY 会跳过那些不可用的组的聚合处理。（译者注：那些坚持把约束拿到数据库之外的应用程序去实现的人，请睁大你的双眼，这可能会导致你的查询失去了优化的机会！）

并行分组执行也从那些用来降低数据传输量的谓词之中获益。Oracle利用并行分组下推技术(parallel group-by pushdown, 缩写GPD), 分组求值被推入到产生输入的进程（生产者子进程，producer slaves），从而降低通讯成本，提高分组操作的可扩展性。生产者子进程把聚合后的数据分发给另一组进程（消费者子进程，consumer slaves），依据的是分组键的HASH值或值范围。消费者子进程于是结束分组处理并产生结果。查询Q7的带GDP的并行查询计划如图1所示。当生产者子进程P1...Pn在分组处理过程中根据HAVING谓词把那些组过滤掉，数据传输量就能够下降。

类似地，那些能够立即使得组被选中的谓词也被推入到分组操作中。一旦一个组被发现为可选，那些不是结果集中所需要的聚合处理就可被跳过（译者注：比如HAVING中用到了COUNT(*)>0谓词，而COUNT(*)没有出现在SELECT中，那么一旦满足了条件，剩下的计数就不必要继续了）。这类谓词的例子有MIN(l_receiptdate)<'18-Feb-2001',  COUNT(*)>10,  SUM(amount_sold)>2000, 当amount_sold已知为正数。

图1 并行分组操作下推

3. 分组视图消除

在这一节，我们讨论一种成为过滤表消除的技术，这是基于过滤连接的思想。一个过滤连接或者是一个半连接(semijoin)或者是一个等值内连接，连接发生在所涉及的两个表之一的一个唯一列。

在这里我们把一个唯一列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.Y
R.X θ= T1.Y AND R.X θ= T2.Y ≡ R.X θ= T1.Y

假设非过滤连接（如果有的话）先发生。过滤连接最多会保持R中的所有结果行，既然一个过滤连接仅仅能够滤掉R中的数据，这与内连接不同，内连接既可滤掉数据也可生成重复数据。过滤连接使得T2表变成多余的，因此可以去除。虽然这种技术看上去和 AND EXISTS 子查询的合并非常相似，接下来我们要展示一下这种技术的不同应用。

3.1 视图消除
请看Q8, 它是一个TPC-H第18号查询的简化版本： 

Q8 
SELECT o_orderkey,c_custkey,   SUM(l_quantity) 
  FROM orders,   lineitem   L1,   customers 
WHERE o_orderkey   =   l_orderkey   AND 
       c_custkey   =   o_custkey   AND 
       o_orderkey   IN (SELECT   l_orderkey 
                          FROM   lineitem   L2 
                        GROUP   BY   l_orderkey 
                        HAVING   SUM(l_quantity)   >   30) 
GROUP   BY   o_orderkey,   o_totalprice; 

查询Q8经过展开变成Q9。Q9中由展开变形产生的内联视图（衍生表）V2 不需要半连接，既然它是一个等值连接，并且V2上的连接列是唯一的，这是V2上对此列做分组操作的结果。

Q9 
SELECT   o_orderkey,c_custkey,   SUM(l_quantity) 
  FROM   orders,   lineitem   L1,   customers, 
        (SELECT   l_orderkey 
           FROM   lineitem   L2 
          GROUP   BY   l_orderkey 
         HAVING   SUM(l_quantity)   >   30)   V2 
WHERE   o_orderkey   =   V2.l_orderkey   AND 
         o_orderkey   =   L1.l_orderkey   AND 
         c_custkey   =   o_custkey 
GROUP   BY   o_orderkey,   c_custkey; 

利用分组和连接置换（即分组置换）[文献5,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 
            FROM   lineitem   L2 
           GROUP   BY   l_orderkey 
          HAVING   SUM(l_quantity)   >   30)   V2, 
         (SELECT   l_orderkey, SUM(l_quantity) qty 
            FROM   lineitem   L1 
          GROUP   BY   l_orderkey)   V1 
WHERE   o_orderkey   =   V1.l_orderkey   AND 
        o_orderkey   =   V2.l_orderkey   AND 
        c_custkey   =   o_custkey 
GROUP   BY   o_orderkey,   c_custkey; 

正如所示，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, 
         (SELECT   o_orderkey,   SUM(l_quantity) 
            FROM   lineitem 
          GROUP   BY   l_orderkey 
          HAVING   SUM(l_quantity)   >   30)   V2, 
WHERE   o_orderkey   =   V2.l_orderkey   AND 
        c_custkey   =   o_custkey 
GROUP   BY   o_orderkey,   c_custkey; 

如果Q9中的视图V2被合并，另外一种利用过滤连接的论证方法也能得到去除外层查询的lineitem表的同样结果。

4. 利用窗口函数的子查询消除
这种技术利用窗口函数代替子查询[文献11]，因此减少了表访问、连接求值的次数并改善查询的性能。这里讨论的子查询消除技术有些在Oracle 9i引入，有些只是作为文献出版[文献13]。在它的简化版中，包含聚合子查询(subsumed aggregation subqueries)被利用窗口函数所消除。

当一个外层查询块如果含有在子查询中出现的所有的表和谓词，它就称为包含一个子查询。外层查询块可能还有额外的表和谓词。显然，包含属性和第二节谈到的包容属性是不同的。这种技术利用了无损连接属性和代数聚合（例如 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 
         T2.z   relop   (SELECT   AGG(T2.w) 
                           FROM   T2 
                          WHERE   T2.y   =   T1.y); 

假设T1和T2的连接是一个无损连接，即T2.y是一个指向主键T1.y的外键，那么引入一个把相关列作为分区键的窗口函数就可消除子查询。如Q13所示。

Q13 
SELECT   V.x 
  FROM   (SELECT   T1.x,   T2.z, 
                   AGG   (T2.w) OVER   (PARTITION   BY   T2.y) 
                         AS   win_agg 
            FROM   T1,   T2 
           WHERE   T1.y   =   T2.y)   V 
WHERE   V.z   relop   win_agg; 

为了利用窗口函数做子查询消除，T1和T2的连接并不要求是无损的。然而，无损连接可以导致另一种变形，允许优化器考虑更多连接置换。

上述Q12的变种形式，如非相关子查询，或者有额外表和谓词，或者没有聚合，或者当子查询和外层查询都有分组，都可以利用子查询消除技术进行变形。我们在随后的章节中会给出例子。

4.1 相关包含子查询
请看查询 Q14, 这是一个TPC-H第二号查询的简化版本。外层查询多了表PARTS和那个表上的谓词。子查询和PARTS表相关并且被外层查询包含。 

Q14 
SELECT   s_name,   n_name,   p_partkey 
  FROM   parts   P,   supplier,   partsupp, 
         nation,   region 
WHERE   p_partkey   =   ps_partkey   AND 
         s_suppkey   =   ps_suppkey   AND 
         s_nationkey   =   n_nationkey   AND 
         n_regionkey   =   r_regionkey   AND 
         p_size   =   36   AND 
         r_name   =   'ASIA'   AND 
         ps_supplycost IN 
                       (SELECT   MIN   (ps_supplycost) 
                          FROM   partsupp,   supplier,   nation, region 
                         WHERE   P.p_partkey   =   ps_partkey   AND 
                                 s_suppkey   =   ps_suppkey   AND 
                                 s_nationkey   =   n_nationkey   AND 
                                 n_regionkey   =   r_regionkey   AND 
                                 r_name   =   'ASIA'); 

子查询消除技术把Q14变换为Q15: 

Q15 
SELECT   s_name,   n_name,   p_partkey 
  FROM   (SELECT ps_supplycost, 
                 MIN   (ps_supplycost) OVER (PARTITION   BY   ps_partkey) 
                       AS   min_ps, 
                 s_name,   n_name,   p_partkey 
            FROM parts,   supplier,   partsupp, 
                 nation,   region 
           WHERE   p_partkey   =   ps_partkey   AND 
                   s_suppkey   =   ps_suppkey   AND 
                   s_nationkey   =   n_nationkey   AND 
                   n_regionkey   =   r_regionkey   AND 
                   p_size   =   36   AND 
                   r_name   =   'ASIA')   V 
WHERE    V.ps_supplycost   =   V.min_ps; 

由PARTSUPP和PARTS表的连接产生的重复行，如果有的话，在这里无关紧要，因为聚合函数是MIN。如果聚合函数不是MIN/MAX，或者对额外表的连接（在本例中是PARTS表）不是无损的，那么窗口函数的计算必须在视图中完成，然后才和附加表连接。这正是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)) 
                         THEN l_extprice 
                         ELSE   NULL 
                    END)   avg_extprice, 
                    l_partkey 
            FROM   lineitem)   V 
WHERE   p_partkey   =   V.l_partkey   AND 
         V.avg_extprice   IS   NOT   NULL   AND 
         P_brand   =   'Brand#23'   AND 
         p_container   =   'MED  BOX'; 

4.2 非相关包含子查询

请看查询Q17, 这是TPC-H第15号查询的一个简化版。Q17有一个和外层查询不相关的聚合子查询，子查询和外查询同时引用一个相同的分组视图（衍生表）V.

Q17 
WITH  V  AS  (SELECT l_suppkey, 
                     SUM(l_extprice) revenue 
                FROM lineitem 
               WHERE   l_shipdate   >=   '1996-01-01' 
              GROUP   BY   l_suppkey) 
SELECT   s_suppkey,   s_name,   V.revenue 
  FROM   supplier,   V 
  WHERE s_suppkey = V.s_suppkey AND 
        V.revenue  = (SELECT   MAX(V.revenue) 
                        FROM   V); 

上述查询能够被变换为Q18, 这里一个窗口函数被引入，子查询被消除。

Q18 
SELECT   s_suppkey,   s_name,   V.revenue 
  FROM   supplier, 
        (SELECT l_suppkey, 
                SUM(l_extprice) revenue, 
                MAX(SUM(l_extprice)) OVER() gt_rev 
           FROM lineitem 
           WHERE   l_shipdate   >=   '1996-01-01' 
          GROUP   BY   l_suppkey)   V 
WHERE   s_suppkey   =   V.l_suppkey   AND 
        V.revenue   =   V.gt_rev; 

在本例中，一个总计窗口函数MAX(利用空白的OVER()子句)在聚合列SUM(l_extprice)上被引入，消除了子查询。没有必要在此窗口函数加入分区键，因为Q17的子查询是非相关的，必须作用在整个数据集。我们为总计型窗口函数使用了一种新型的并行技术，见第五节的描述，所以转换后的查询Q18能够高效、可扩展地执行。

4.3 Having子句中的包含子查询

当外层查询含有分组操作(GROUP-BY)时，子查询消除技术也可以适用。例如Q19, 这是一个TPC-H第11号查询的简化版本。Q19里面的子查询是非相关的，而且被外层查询所包含。实际上，子查询和外层查询具有相同的表和谓词。

Q19 
SELECT   ps_partkey, 
         SUM(ps_supplycost   *   ps_availqty)   AS   value 
  FROM   partsupp,   supplier,   nation 
WHERE   ps_suppkey   =   s_suppkey   AND 
         s_nationkey   =   n_nationkey   AND 
         n_name   =   'FRANCE' 
GROUP   BY     ps_partkey 
HAVING  SUM(ps_supplycost*ps_availqty)>(SELECT SUM(ps_supplycost*ps_availqty)*0.0001 
                                          FROM partsupp,   supplier,   nation 
                                         WHERE ps_suppkey   =   s_suppkey   AND 
                                               s_nationkey   =   n_nationkey   AND 
                                               n_name   =   'FRANCE'); 

Q19可以被变换为Q20。正如Q17, 这里引入的窗口函数是一个没有分区键的总计函数，因为Q19的子查询是非相关的。

Q20 
SELECT   V.ps_partkey,   V.gb_sum 
  FROM  (SELECT   ps_partkey, 
                  SUM(ps_supplycost*ps_availqty) value, 
                  SUM(SUM(ps_supplycost*ps_availqty)) OVER () gt_value 
           FROM   partsupp,   supplier,   nation 
          WHERE   ps_suppkey   =   s_suppkey   AND 
                  s_nationkey   =   n_nationkey   AND 
                  n_name   =   'FRANCE' 
          GROUP   BY   ps_partkey)   V 
WHERE   V.value   >   V.gt_value   *   0.0001; 

4.4 产生多重结果集的子查询

为了消除子查询，它并不一定要有一个聚合并产生单元素集。请看查询21, 它的子查询产生了一个多重结果集并执行一个“ALL”子查询谓词。

Q21 
SELECT  ps_partkey,   s_name, 
        SUM(ps_supplycost   *   ps_availqty)   as   VALUE 
FROM   partsupp,   supplier,   nation 
WHERE   ps_suppkey   =   s_suppkey   AND 
        s_nationkey   =   n_nationkey   AND 
        n_name   =   "GERMANY' 
GROUP   BY   s_name,   ps_partkey 
HAVING SUM(ps_supplycost*ps_availqty)
           > ALL (SELECT ps_supplycost*ps_availqty*0.01 
                   FROM partsupp,   supplier,   nation 
                  WHERE n_name   =   "GERMANY'   AND 
                        ps_suppkey   =   s_suppkey   AND 
                        s_nationkey   =   n_nationkey); 


我们把这个查询转换为Q22: 

Q22 
SELECT   ps_partkey,   s_name,   VALUE 
  FROM   (SELECT   ps_partkey,   s_name,   VALUE, 
                   SUM(ps_supplycost*ps_availqty) as   VALUE, 
                   MAX(MAX(ps_supplycost*ps_availqty)) OVER() VAL_pkey 
            FROM   partsupp,   supplier,   nation 
           WHERE   n_name   =   "GERMANY'   AND 
                   ps_suppkey   =   s_suppkey   AND 
                   s_nationkey   =   n_nationkey 
           GROUP   BY   s_name,   ps_partkey)   V 
WHERE   V.VALUE   >   V.VAL_pkey   *   0.01; 

如果子查询的谓词是 “>ANY”，那么窗口函数将会是MIN(MIN(ps_supplycost*ps_availqty)) OVER()。利用不同的窗口函数，“=ALL”“=ANY”谓词也能被处理。

5. 可扩展的并行执行

为了使得查询执行更可扩展, Oracle对窗口函数的并行化进行了强化处理。在通常情况下，ORACLE中的窗口函数被并行化，方法是根据HASH值或值范围对数据进行分散，基于分区键进行多进程处理。类似的并行方法被用作SQL的MODEL子句[文献7]。每个进程独立于其他进程工作，计算它所收到的分区。例如，Q16的子查询消除所引入的窗口函数就是这样并行化的。Q16的并行查询计划看起来像这样：

图2 典型的窗口函数并行化

生产者子进程P1至Pn, 根据l_partkey上的Hash值产生parts表和lineitem表的连接结果，并分发给消费者子进程C1至Cn, 后者进行窗口排序。每个消费者子进程会计算窗口函数AVG在它收到的那个分区（由分区键l_partkey定义）上的值。可以看到，窗口函数的这个常规并行化的可扩展性是由分区键l_partkey的基数所控制的。如果窗口函数的分区键具有较低的基数（例如地区，性别）或者根本没有分区键，则可扩展性就很有限，或者根本不存在。为了使得第4节提到的利用窗口函数进行子查询消除的变换获得高收益，这类窗口函数的可扩展性就变得至关重要。 为了那个目的，我们提出一种新型的并行化技术，现在就来把它描绘一下。

请看4.3节中的查询Q20，它有一个不带分区键的窗口函数SUM(SUM(ps_supplycost*ps_availqty)) OVER()。这是一个总计报表窗口函数，因为它作用于整个结果集并为每一行产生一个总计。总计函数本质上是不可并行化的，因为没有一个分区键可以用来为子进程划分工作量。如果这个总计函数不被并行化，它就削弱了子查询消除变换所带来的好处。通常情况下，总计窗口函数的查询计划如图3所示。


图3 不是很并行化的总计函数计划

很显然，图3的并行计划不能很好地扩展，因为总计函数的计算是由查询协调(QC)进程完成的。子进程C1至Cn把分组操作的结果送给QC进程，它独自利用窗口缓存操作完成总计函数的计算。正如1.3小节中所言，在这种情况下Oracle选择了“窗口缓存”，既然它只需要缓存这些行来计算总计函数。（译者注：即没有窗口排序的操作）当数据行一边被缓存，窗口汇总一边被计算出来。如果输入用完了，即所有输入行都被缓存了，我们就完成了总计函数值的计算。这些缓存的行就被和总计一起输出。

在我们的新的总计窗口函数并行化方案中，很大一部分的总计窗口函数计算被推到子进程而不是QC进程来完成。它在子进程和QC进程之间有一个很小的协调步骤来完成总计函数的计算。利用这种模型，总计的求值变得高度可扩展。新的总计窗口函数并行化计划如图4所示。

图4 总计函数的并行化

现在，窗口缓存处理被推入到子进程C1至Cn。每一个子进程计算出它本地的总计，并传给查询协调进程。QC协调它从所有子进程收到的结果，并把总计值送回子进程。于是子进程把它们的缓存数据行连同总计值一起输出。在这个并行计划中，子进程并发地完成了行处理的主要工作。串行化（或者QC协调）可以忽略不计，因为QC进程处理的数据量很小（最多为1000），取决于并行度（在一个任务中并发工作的进程数目）。 

这种窗口下推技术照样可以推广到非总计型的报表窗口函数。它对那些具有低基数的分区键的窗口函数的改善特别有用。虽然概念是一样的，子进程和QC之间需要更多的信息交换，而两边也需要更多的处理。

子进程C1至Cn在本地计算为每个分区计算窗口汇总，然后和QC进程沟通，发出一个本地窗口汇总和相应分区键的数组。QC进程为每个分区完成报表汇总的计算，并把结果（最终窗口汇总和分区键）发回给子进程。为了产生结果，子进程中的窗口执行将本地数据和QC送来的数据做了个连接。因为子进程的本地数据和QC送来的数据都是按分区键排序的，这个连接看起来像一个排序-合并连接。我们将展示窗口函数扩展性的实验结果显示在第7节中。

6. 已知空值反连接（NULL-AWARE ANTI JOIN, 缩写NAAJ）

在这一节中，我们讨论一个反连接的变种，叫做已知空值反连接（NAAJ），这是Oracle 11g引入的。在大多数的应用中，<>ALL (即NOT IN)子查询很常见，因为应用开发人员发现 <>ALL的语法比起和它近乎等价的NOT EXISTS更为直观。带NOT EXISTS的子查询被展开为反连接。反连接的语义和内连接恰好相反，因为仅当一个来自左表的行不能连接到右表中的任意一行时，它才被返回。我们把这称作常规反连接。通常来说，商业数据库只有在量化比较涉及的所有列都保证为非空时，才能够把<>ALL子查询展开为常规反连接。文献[9]中使用到的另一种策略引入了一个复制表，和一个额外的反连接来处理空值。在SQL中，<>ALL操作符可以被处理为不等比较的AND。操作符<ALL, <=ALL, >ALL和>=ALL也能被类似处理。SQL标准支持三值逻辑，所以对空值的任意关系比较运算总是被返回“未知”。例如，谓词 7=NULL, 7 <> NULL, NULL = NULL, NULL <> NULL都返回未知，这和“假”不同，因为未知的相反值仍然是未知。如果WHERE子句的最终结果是为假或者未知，这一行就被滤掉了。请看查询23, 它带有一个 <>ALL子查询。

Q23 
SELECT   T1.c 
  FROM   T1 
WHERE   T1.x <> ALL (SELECT   T2.y 
                        FROM   T2 
                       WHERE   T2.z > 10); 

假设子查询返回下面这些值的集合{7,  11, NULL} 而 T1.x 有这些值: {NULL,  5,  11}。<>ALL操作可以被表达为T1.x <> 7 AND T1.x <>11 AND T1.x <> NULL。这会返回未知，因为不管T1.x取什么值，T1.x <> NULL总是返回未知。因此，对这样的一组值，Q23会返回空集。如果在这个例子中使用常规的反连接，它将会错误地返回{NULL, 5}。

6.1 已知空值反连接算法

Q23中的子查询能被利用NAAJ展开为查询Q24。我们用下列非标准的记法来表示NAAJ：T1.x NA= T2.y，这里T1和T2分别是NAAJ的左右两端的表。

Q24 
SELECT   T1.c 
  FROM   T1,  T2 
WHERE   T1.x NA= T2.y  and  T2.z > 10; 

我们用Q24作为一个例子来解释NAAJ的语义。NAAJ 在右表的所有过滤谓词结束之后开始起作用。所以，当我们在下面的解释中提到T2时，它的隐含意思是在原T2表上运行了过滤谓词T2.z>10得到的数据集。

1.假如T2没有数据，则返回T1中所有的行并结束。
2.假如T2中的任何一行在NAAJ条件中涉及的列中有一个空值，则返回空集并结束。
3.如果T1中有一行在NAAJ条件中涉及的列都为空，则这一行不被返回。
4.对于T1中的每一行，假如和T2中的任意一行的NAAJ条件返回真或未知，则不返回那个T1行；否则返回那个T1行。

步骤1和常规反连接相同；就是说，如果右表为空，那么左表中的所有行，包括那些子反连接条件中具有空值的行，都被返回。可以看到步骤2和3被步骤4所包含，但是它们在当左表或右表在连接列上有空值的时候，提供了更高效的执行方法。步骤4是NAAJ和常规反连接的根本区别。在常规反连接中，如果反连接条件返回未知，则左表的行被返回，而在NAAJ中则不被返回。下面我们展示NAAJ的策略。当反连接涉及了多个列，这些策略变得很复杂，我们用这个查询来演示：

Q25 
SELECT   c1,   c2,   c3 
  FROM   L 
WHERE   (c1, c2, c3) <> ALL (SELECT c1, c2, c3 
                                FROM R); 

6.2 NAAJ的执行策略

在NAAJ语义中，一个左边的行可以连接或匹配到右边的多个不同的行。例如，一个左边的行在一个连接列上有个空值，它会匹配到右边的表在此列上有任意值的行。在这种情况下NAAJ连接条件返回未知，因此这一行不被返回。请看Q25中的左表L中的行(null, 3, null)。假设右表R中有两行={(1, 3, 1), (2, 3, 2)}。虽然在右表中没有一个(null, 3, null)这样的行，L表中的行同时匹配到R表中的两行，因为非空列C2有值3, 因此(null, 3, null)不被返回。

常规的排序-合并反连接和HASH反连接方法也被按此扩展，它们构建数据结构（排序或HASH表）的时候也会收集信息，看看哪一个连接列具有空值。

在此之后我们执行6.1小节中给出的步骤1和2，看看是否可能提早返回所有行或者空集作为连接结果。否则，为左边的每一行，我们执行如下的步骤来找一个匹配行，除非6.1小节中给出的步骤3去除了这一行。如果按照如下三个步骤中的任意一个找到了匹配，那么左边的行就被丢弃，正如在常规反连接中一样：
1. 搜索右边的排序或HASH访问结构（译者注：即利用右表在内存中建立的探测表），找一个完全匹配。
2. 利用已收集的空值的信息，查找另外的可能匹配。例如，假设有三个连接列C1,C2和C3, 但是只有C1和C2在右边有空值。如果下一个左边的行具有值(1, 2, 3), 那么我们就在右边的访问结构中搜索(1, null, 3), (null, 2, ), 和 (null, null, 3)。
3. 如果来自左边的行在一个或多个连接列上具有空值，那么我们就在非空连接列上建立第二个访问结构，如果还未建立的话。例如，假设来自左边的行具有值(null,2,3)。我们就在右边的C2列和C3列建立第二个排序或HASH表，然后我们在新建的访问结构中(x, 2, 3), (x, 2, null), (x, null, 3),和(x, null, null)。

更多优化工作还可以进行，如果我们跟踪所有可能在右边出现空值的模式。利用上述的步骤3给出的例子，假设我们知道右边有一个行在C2和C3同时为空。这一行可以匹配左边出现的C1为空的任意行。在这种情况下，来自左边的(null, 2, 3)行可以被立即去除，也没有必要再建立第二个访问结构。这个信息同样也可以用来去除步骤2中的一些访问。

单键列优化：如果在NAAJ条件中只涉及到一个列（例如Q24），执行策略就简单得多。来自左边的在连接列上为空的那些行可以被跳过或者返回，取决于右边有没有数据，而不必去查找匹配。

7. 性能研究

我们在一个30G的TPC-H schema上进行了一些性能实验。我们用一台带有8个双核400MHz处理器和32G主内存的Linux机器。这台机器连接到一个被Oracle自动存储管理器(ASM)所管理的共享存储。连接到这个共享存储的I/O带宽相比较CPU的能力而言有些紧张，正如我们的并行实验结果所示。所有的查询，除非特别注明，都使用了CPU能提供的所有并行度。

7.1 子查询合并

请看我们的Q4, 这是一个TPC-H 第21号查询的简化版。相比较Q4, 原始的 TPC-H 第21号查询多了两张表: ORDERS和NATION, 还有一个过滤谓词用于把数据限制在一个特定的国家。在SCHEMA中总共有25个国家，它们的数据是同样平均分布的。2.2小节的子查询合并把Q4变换为Q5。图5将变换后的Q5和未变换的Q4的运行时间，作为国家数量的函数显示。平均而言性能改善是27%。

图 5. TPC-H q21, 子查询合并


7.2 分组查询消除

在这个实验中，我们使用了Q8, 这是一个TPC-H第18号查询的简化版。我们在3.1节提及的分组视图消除技术去除了分组视图，避免了对lineitem表的不必要的连接，结果形成了Q11。我们给HAVING子句的谓词赋予30到300的不同的值，子查询返回大约2,000至34,000,000行的数据（如X轴所示）。图6显示了这个实验的结果。

图6，TPC-H第18号查询，视图消除

到Q11的转换对于优化器而言可能是个挑战，因为HAVING子句的谓词是基于聚合运算的结果，优化器很难对其基数作出估算。如果估算值低了，优化器可能选择了嵌套循环而不是HASH连接，因此使得性能下降。

7.3 利用窗口函数的子查询消除

为了演示这个优化，我们执行了TPC-H的第2,11,15和17号查询。图7显示了优化后的查询（用“opt”标记）和未经优化的查询的使用时间。转换自TPC-H的第2,17号查询的Q15和Q16，显示出显著的改善。对TPC-H第2号查询而言，执行时间下降了24倍，原因是窗口转换去除了对表的多次访问和连接。

图7, 利用窗口函数的子查询消除

图8显示出为TPC-H的15号查询进行的窗口变换从而去除相关子查询获得的改善，这个量化作为扫描lineitem表的月份数的函数显示。这个优化我们在4.2小节解释为Q17（原查询）到Q18（转换后的查询）的变换。图8中的平均改善是执行时间平均下降了8.4倍。

图8. TPC-H第q15查询, 去除相关子查询

我们注意到利用窗口函数的子查询消除对应TPC-H查询非常有效。对TPC-H的查询q2, q11, q15, 和q17 的平均收益超过10倍。

7.4 可扩展的窗口函数执行

为了演示对不带分区键的窗口函数的并行化的改善，我们使用了lineitem表上的如下查询：

SELECT  SUM(l_extprice)) OVER() W 
  FROM  lineitem; 

这个查询被分别用带/不带第5节中的增强并行化技术的方法执行，并行度(即DOP)从2到16不等。图9显示出这个实验的结果。
  
图9. 不带分区键的窗口函数的并行化

请注意，即使窗口函数没有被并行化，对表的扫描仍然是并行的。扫描子进程把它们的数据发给查询协调进程，后者随后计算窗口函数。所以，对于并行度2, 改善稍低于2倍。图9同时还显示了对于DOP>8的并行度，我们的系统无法线性扩展，这是由于我们使用的共享磁盘系统的带宽有限。

7.5 已知空值的反连接

我们进行了两组实验来显示已知空值反连接所带来的好处。第一组使用查询Q26，从特定的供应商清单中，查找那些在特定月份（1996年1月）中没有订货的供应商。在此模式下，L_SUPPKEY可能有空值，但是S_SUPPKEY没有空值。

Q26 
SELECT   s_name   FROM   supplier 
WHERE   s_suppkey   in   (<supplier_list>)   AND 
        s_suppkey   <>   ALL 
           (SELECT   l_suppkey   FROM   lineitem 
           WHERE   l_shipdate   >=   '1996-01-01'   AND 
           l_shipdate   <   '1996-02-01') 

在没有NAAJ的情况下，Q26中的子查询无法被展开，这导致了相关执行，我们不得不在supplier表的每一行执行这个子查询。性能慢得令人郁闷，因为在相关谓词上没有索引探测可用。这是因为Oracle把ALL子查询转化为NOT EXISTS子查询并且使用了等价的相关谓词（l_suppkey IS NULL OR l_suppkey = s_suppkey）。非NAAJ的唯一可取之处是lineitem表按照l_shipdate的分区，这使得我们可以只扫描一个分区。图10演示了多至40个供应商的情况下的性能改善。一旦被展开，查询在supplier和lineitem表之间的已知空值的HASH反连接下执行。

第二个实验是基于真实的负载——Oracle应用发出的241,000个查询，这个SCHEMA含有大约14,000个表，表示供应链，人力资源，财务，订单，客户管理，以及很多其他的东西。一个查询涉及的表数目在1到159之间，平均每个查询8个表。

图 10. 非NAAJ vs. NAAJ 

总共有72个带有<>ALL子查询的查询，它们适用于NAAJ执行。68个查询在CPU时间的使用方面, 平均获得736730倍的提高！其中有一个查询在没有NAAJ的情况下用了350秒，而NAAJ只用了0.001秒。性能下降的另外4个查询平均下降了100倍，最差的一个的CPU时间从0.000001变为0.016。

8. 相关著作

不同类型的子查询展开在以前已经有广泛研究[文献1,2,3,4,9]。我们的系统支持所有这些类型的展开技术，同时使用了试探式和基于成本的策略。有一篇早期著作[文献2]提出了一种在操作树中提升聚合和分组操作的技术。Oracle的早期版本（8.1和9i）使用了类似的算法。变换是在查询转换阶段被探索式触发的。在Oracle 11g版，基于成本的框架被用于置换distinct和分组操作，并把它们转换为连接[文献5,6]。有一些利用窗口函数消除子查询的技术已在[文献13]中发表。[文献12]的非正式引用提到 有一种分组子查询消除技术已经在其他地方讨论过。我们展示了一种类似技术如何能被集成到Oracle优化器中。关于子查询合并，已知空值反连接，可扩展的窗口函数的计算等相关内容则未在文献中讨论过。

9. 结论
子查询是SQL或者类SQL查询语言的强大组件，扩展了它们的声明性和表达能力。本文描绘了Oracle关系数据库中的增强版子查询优化，这优化得益于Oracle的基于成本的查询转换框架。本文的重要贡献（这里讨论的一些内容正在申请美国专利）在于描述了一系列技术——子查询合并，利用窗口函数的子查询消除，分组查询的视图消除，已知空值的反连接和并行执行技术。我们的性能研究表明这些优化为复杂查询的执行时间提供了显著改善。

10. 鸣谢
作者们感谢Thierry  Cruanes的查询协调进程和子进程(QC-Slave)之间的通讯框架，Nathan Folkert和Sankar Subramanian利用此框架为某一特定类别的窗口函数所作的并行化。Susy Fan为性能实验作了辅助工作，也一并致谢。

11. 引用文献
[1]    W. Kim. —On Optimizing an SQL-Like Nested Query", ACM 
TODS, September 1982. 
[2]    U. Dayal, —Of Nests and Trees: A Unified Approach to 
Processing Queries that Contain Nested Subqueries, 
Aggregates, and Quantifiers", Proceedings of the 13th VLDB 
Conference, Brighton, U.K., 1987. 
[3]    M. Muralikrishna, —Improved Unnesting Algorithms for Join 
Aggregate SQL Queries", Proceedings of the 18th VLDB 
Conference, Vancouver, Canada, 1992. 
[4]    H. Pirahesh, J.M. Hellerstein, and W. Hasan, —Extensible 
Rule Based Query Rewrite Optimizations in Starburst". 
Proc. of ACM SIGMOD, San Diego, U.S.A., 1992. 
[5]    S. Chaudhuri and K. Shim, —Including Group-By in Query 
Optimization", Proceedings of the 20th VLDB Conference, 
Santiago, Chile, 1994. 
[6]    W.P. Yan and A.P. Larson, —Eager Aggregation and Lazy 
Aggregation", Proceedings of the 21th VLDB Conference, 
Zurich, Switzerland, 1995. 
[7]    A. Witkowski, et al, —Spreadsheets in RDBMS for OLAP", 
Proceedings of ACM SIGMOD, San Diego, USA, 2003. 
[8]    R. Ahmed, et al, —Cost-Based Query Transformation in 
Oracle", Proceedings of the 32nd VLDB Conference, Seoul, 
S. Korea, 2006. 
[9]    M. Elhemali, C. Galindo-Legaria, et al, —Execution 
Strategies for SQL Subqueries", Proceedings of ACM 
SIGMOD, Beijing, China, 2007. 
[10] A. Eisenberg, K. Kulkarni, et al, —SQL: 2003 Has Been 
Published", SIGMOD Record, March 2004. 
[11]    F. Zemke. "Rank, Moving and reporting functions for 
OLAP," 99/01/22 proposal for ANSI-NCITS. 
[12] C. Zuzarte et al, —Method for Aggregation Subquery Join 
Elimination", 
http://www.freepatentsonline.com/7454416.html 
[13] C. Zuzarte, H. Pirahesh, et al, —WinMagic: Subquery 
Elimination Using Window Aggregation", Proceedings of 
ACM SIGMOD, San Diego, USA, 2003. 
[14] TPC Benchmark H (Decision Support), Standard 
Specification Rev 2.8, http://tpc.org/tpch/spec/tpch2.8.0.pdf 
[15] TPC-DS Specification Draft, Rev 32, 
http://www.tpc.org/tpcds/spec/tpcds32.pdf 



[ 本帖最后由 newkid 于 2010-11-10 22:54 编辑 ]

figure1.png (23.25 KB, 下载次数: 5)

 

figure2.png (40.51 KB, 下载次数: 0)

 

figure3.png (31.09 KB, 下载次数: 0)

 

figure4.png (32.92 KB, 下载次数: 0)

 

 

 

最近论坛很多人提的问题都与行列转换有关系，所以我对行列转换的相关知识做了一个总结，
希望对大家有所帮助，同时有何错疏，恳请大家指出，
我也是在写作过程中学习，算是一起和大家学习吧。

行列转换包括以下六种情况：
*列转行
*行转列
*多列转换成字符串
*多行转换成字符串
*字符串转换成多列
*字符串转换成多行

下面分别进行举例介绍。

首先声明一点，有些例子需要如下10g及以后才有的知识：
a。掌握model子句，
b。正则表达式
c。加强的层次查询

讨论的适用范围只包括8i,9i,10g及以后版本。begin：

1、列转行
CREATE TABLE t_col_row(
ID INT,
c1 VARCHAR2(10),
c2 VARCHAR2(10),
c3 VARCHAR2(10));

INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31');
INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL);
INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33');
INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34');
INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL);
INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35');
INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL);
COMMIT;

SELECT * FROM t_col_row;

1)UNION ALL
适用范围：8i,9i,10g及以后版本
SELECT id, 'c1' cn, c1 cv
  FROM t_col_row
UNION ALL
SELECT id, 'c2' cn, c2 cv
  FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;

若空行不需要转换，只需加一个where条件，
WHERE COLUMN IS NOT NULL 即可。

2)MODEL
适用范围：10g及以后
SELECT id, cn, cv FROM t_col_row
MODEL
RETURN UPDATED ROWS
PARTITION BY (ID)
DIMENSION BY (0 AS n)
MEASURES ('xx' AS cn,'yyy' AS cv,c1,c2,c3)
RULES UPSERT ALL
(
  cn[1] = 'c1',
  cn[2] = 'c2',
  cn[3] = 'c3',
  cv[1] = c1[0],
  cv[2] = c2[0],
  cv[3] = c3[0]
  )
ORDER BY ID,cn;

3)collection
适用范围：8i,9i,10g及以后版本
要创建一个对象和一个集合：
CREATE TYPE cv_pair AS OBJECT(cn VARCHAR2(10),cv VARCHAR2(10));

CREATE TYPE cv_varr AS VARRAY(8) OF cv_pair;

SELECT id, t.cn AS cn, t.cv AS cv
  FROM t_col_row,
       TABLE(cv_varr(cv_pair('c1', t_col_row.c1),
                     cv_pair('c2', t_col_row.c2),
                     cv_pair('c3', t_col_row.c3))) t
ORDER BY 1, 2;

 

2、行转列

CREATE TABLE t_row_col AS
SELECT id, 'c1' cn, c1 cv
  FROM t_col_row
UNION ALL
SELECT id, 'c2' cn, c2 cv
  FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;

SELECT * FROM t_row_col ORDER BY 1,2;

1)AGGREGATE FUNCTION
适用范围：8i,9i,10g及以后版本
SELECT id,
       MAX(decode(cn, 'c1', cv, NULL)) AS c1,
       MAX(decode(cn, 'c2', cv, NULL)) AS c2,
       MAX(decode(cn, 'c3', cv, NULL)) AS c3
  FROM t_row_col
GROUP BY id
ORDER BY 1;

MAX聚集函数也可以用sum、min、avg等其他聚集函数替代。

被指定的转置列只能有一列，但固定的列可以有多列，请看下面的例子：

SELECT mgr, deptno, empno, ename FROM emp ORDER BY 1, 2;

SELECT mgr,
       deptno,
       MAX(decode(empno, '7788', ename, NULL)) "7788",
       MAX(decode(empno, '7902', ename, NULL)) "7902",
       MAX(decode(empno, '7844', ename, NULL)) "7844",
       MAX(decode(empno, '7521', ename, NULL)) "7521",
       MAX(decode(empno, '7900', ename, NULL)) "7900",
       MAX(decode(empno, '7499', ename, NULL)) "7499",
       MAX(decode(empno, '7654', ename, NULL)) "7654"
  FROM emp
WHERE mgr IN (7566, 7698)
   AND deptno IN (20, 30)
GROUP BY mgr, deptno
ORDER BY 1, 2;

这里转置列为empno，固定列为mgr，deptno。

还有一种行转列的方式，就是相同组中的行值变为单个列值，但转置的行值不变为列名：

ID        CN_1        CV_1        CN_2        CV_2        CN_3        CV_3
1                c1                v11                c2                v21                c3                v31
2                c1                v12                c2                v22                c3                
3                c1                v13                c2                                        c3                v33
4                c1                                        c2                v24                c3                v34
5                c1                v15                c2                                        c3                
6                c1                                        c2                                        c3                v35
7                c1                                        c2                                        c3

这种情况可以用分析函数实现：

SELECT id,
       MAX(decode(rn, 1, cn, NULL)) cn_1,
       MAX(decode(rn, 1, cv, NULL)) cv_1,
       MAX(decode(rn, 2, cn, NULL)) cn_2,
       MAX(decode(rn, 2, cv, NULL)) cv_2,
       MAX(decode(rn, 3, cn, NULL)) cn_3,
       MAX(decode(rn, 3, cv, NULL)) cv_3
  FROM (SELECT id,
               cn,
               cv,
               row_number() over(PARTITION BY id ORDER BY cn, cv) rn
          FROM t_row_col)
GROUP BY ID;

2)PL/SQL
适用范围：8i,9i,10g及以后版本 
这种对于行值不固定的情况可以使用。
下面是我写的一个包，包中
p_rows_column_real用于前述的第一种不限定列的转换；
p_rows_column用于前述的第二种不限定列的转换。

CREATE OR REPLACE PACKAGE pkg_dynamic_rows_column AS
  TYPE refc IS REF CURSOR;

  PROCEDURE p_print_sql(p_txt VARCHAR2);

  FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
    RETURN VARCHAR2;

  PROCEDURE p_rows_column(p_table      IN VARCHAR2,
                          p_keep_cols  IN VARCHAR2,
                          p_pivot_cols IN VARCHAR2,
                          p_where      IN VARCHAR2 DEFAULT NULL,
                          p_refc       IN OUT refc);

  PROCEDURE p_rows_column_real(p_table     IN VARCHAR2,
                               p_keep_cols IN VARCHAR2,
                               p_pivot_col IN VARCHAR2,
                               p_pivot_val IN VARCHAR2,
                               p_where     IN VARCHAR2 DEFAULT NULL,
                               p_refc      IN OUT refc);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_dynamic_rows_column AS

  PROCEDURE p_print_sql(p_txt VARCHAR2) IS
    v_len INT;
  BEGIN
    v_len := length(p_txt);
    FOR i IN 1 .. v_len / 250 + 1 LOOP
      dbms_output.put_line(substrb(p_txt, (i - 1) * 250 + 1, 250));
    END LOOP;
  END;

  FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
    RETURN VARCHAR2 IS
    v_first INT;
    v_last  INT;
  BEGIN
    IF p_seq < 1 THEN
      RETURN NULL;
    END IF;
    IF p_seq = 1 THEN
      IF instr(p_str, p_division, 1, p_seq) = 0 THEN
        RETURN p_str;
      ELSE
        RETURN substr(p_str, 1, instr(p_str, p_division, 1) - 1);
      END IF;
    ELSE
      v_first := instr(p_str, p_division, 1, p_seq - 1);
      v_last  := instr(p_str, p_division, 1, p_seq);
      IF (v_last = 0) THEN
        IF (v_first > 0) THEN
          RETURN substr(p_str, v_first + 1);
        ELSE
          RETURN NULL;
        END IF;
      ELSE
        RETURN substr(p_str, v_first + 1, v_last - v_first - 1);
      END IF;
    END IF;
  END f_split_str;

  PROCEDURE p_rows_column(p_table      IN VARCHAR2,
                          p_keep_cols  IN VARCHAR2,
                          p_pivot_cols IN VARCHAR2,
                          p_where      IN VARCHAR2 DEFAULT NULL,
                          p_refc       IN OUT refc) IS
    v_sql VARCHAR2(4000);
    TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    v_keep v_keep_ind_by;
  
    TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    v_pivot v_pivot_ind_by;
  
    v_keep_cnt   INT;
    v_pivot_cnt  INT;
    v_max_cols   INT;
    v_partition  VARCHAR2(4000);
    v_partition1 VARCHAR2(4000);
    v_partition2 VARCHAR2(4000);
  BEGIN
    v_keep_cnt  := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;
    v_pivot_cnt := length(p_pivot_cols) -
                   length(REPLACE(p_pivot_cols, ',')) + 1;
    FOR i IN 1 .. v_keep_cnt LOOP
      v_keep(i) := f_split_str(p_keep_cols, ',', i);
    END LOOP;
    FOR j IN 1 .. v_pivot_cnt LOOP
      v_pivot(j) := f_split_str(p_pivot_cols, ',', j);
    END LOOP;
    v_sql := 'select max(count(*)) from ' || p_table || ' group by ';
    FOR i IN 1 .. v_keep.LAST LOOP
      v_sql := v_sql || v_keep(i) || ',';
    END LOOP;
    v_sql := rtrim(v_sql, ',');
    EXECUTE IMMEDIATE v_sql
      INTO v_max_cols;
    v_partition := 'select ';
    FOR x IN 1 .. v_keep.COUNT LOOP
      v_partition1 := v_partition1 || v_keep(x) || ',';
    END LOOP;
    FOR y IN 1 .. v_pivot.COUNT LOOP
      v_partition2 := v_partition2 || v_pivot(y) || ',';
    END LOOP;
    v_partition1 := rtrim(v_partition1, ',');
    v_partition2 := rtrim(v_partition2, ',');
    v_partition  := v_partition || v_partition1 || ',' || v_partition2 ||
                    ', row_number() over (partition by ' || v_partition1 ||
                    ' order by ' || v_partition2 || ') rn from ' || p_table;
    v_partition  := rtrim(v_partition, ',');
    v_sql        := 'select ';
    FOR i IN 1 .. v_keep.COUNT LOOP
      v_sql := v_sql || v_keep(i) || ',';
    END LOOP;
    FOR i IN 1 .. v_max_cols LOOP
      FOR j IN 1 .. v_pivot.COUNT LOOP
        v_sql := v_sql || ' max(decode(rn,' || i || ',' || v_pivot(j) ||
                 ',null))' || v_pivot(j) || '_' || i || ',';
      END LOOP;
    END LOOP;
    IF p_where IS NOT NULL THEN
      v_sql := rtrim(v_sql, ',') || ' from (' || v_partition || ' ' ||
               p_where || ') group by ';
    ELSE
      v_sql := rtrim(v_sql, ',') || ' from (' || v_partition ||
               ') group by ';
    END IF;
    FOR i IN 1 .. v_keep.COUNT LOOP
      v_sql := v_sql || v_keep(i) || ',';
    END LOOP;
    v_sql := rtrim(v_sql, ',');
    p_print_sql(v_sql);
    OPEN p_refc FOR v_sql;
  EXCEPTION
    WHEN OTHERS THEN
      OPEN p_refc FOR
        SELECT 'x' FROM dual WHERE 0 = 1;
  END;

  PROCEDURE p_rows_column_real(p_table     IN VARCHAR2,
                               p_keep_cols IN VARCHAR2,
                               p_pivot_col IN VARCHAR2,
                               p_pivot_val IN VARCHAR2,
                               p_where     IN VARCHAR2 DEFAULT NULL,
                               p_refc      IN OUT refc) IS
    v_sql VARCHAR2(4000);
    TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    v_keep v_keep_ind_by;
    TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    v_pivot    v_pivot_ind_by;
    v_keep_cnt INT;
    v_group_by VARCHAR2(2000);
  BEGIN
    v_keep_cnt := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;
    FOR i IN 1 .. v_keep_cnt LOOP
      v_keep(i) := f_split_str(p_keep_cols, ',', i);
    END LOOP;
    v_sql := 'select ' || 'cast(' || p_pivot_col ||
             ' as varchar2(200)) as ' || p_pivot_col || ' from ' || p_table ||
             ' group by ' || p_pivot_col;
    EXECUTE IMMEDIATE v_sql BULK COLLECT
      INTO v_pivot;
    FOR i IN 1 .. v_keep.COUNT LOOP
      v_group_by := v_group_by || v_keep(i) || ',';
    END LOOP;
    v_group_by := rtrim(v_group_by, ',');
    v_sql      := 'select ' || v_group_by || ',';
  
    FOR x IN 1 .. v_pivot.COUNT LOOP
      v_sql := v_sql || ' max(decode(' || p_pivot_col || ',' || chr(39) ||
               v_pivot(x) || chr(39) || ',' || p_pivot_val ||
               ',null)) as "' || v_pivot(x) || '",';
    END LOOP;
    v_sql := rtrim(v_sql, ',');
    IF p_where IS NOT NULL THEN
      v_sql := v_sql || ' from ' || p_table || p_where || ' group by ' ||
               v_group_by;
    ELSE
      v_sql := v_sql || ' from ' || p_table || ' group by ' || v_group_by;
    END IF;
    p_print_sql(v_sql);
    OPEN p_refc FOR v_sql;
  EXCEPTION
    WHEN OTHERS THEN
      OPEN p_refc FOR
        SELECT 'x' FROM dual WHERE 0 = 1;
  END;

END;
/

 

3.多列转换成字符串
CREATE TABLE t_col_str AS
SELECT * FROM t_col_row;

这个比较简单，用||或concat函数可以实现：
SELECT concat('a','b') FROM dual;

1)|| OR concat
适用范围：8i,9i,10g及以后版本
SELECT * FROM t_col_str;

SELECT ID,c1||','||c2||','||c3 AS c123
FROM t_col_str;

4.多行转换成字符串
CREATE TABLE t_row_str(
ID INT,
col VARCHAR2(10));

INSERT INTO t_row_str VALUES(1,'a');
INSERT INTO t_row_str VALUES(1,'b');
INSERT INTO t_row_str VALUES(1,'c');
INSERT INTO t_row_str VALUES(2,'a');
INSERT INTO t_row_str VALUES(2,'d');
INSERT INTO t_row_str VALUES(2,'e');
INSERT INTO t_row_str VALUES(3,'c');
COMMIT;

SELECT * FROM t_row_str;

1)MAX + decode
适用范围：8i,9i,10g及以后版本
SELECT id,
       MAX(decode(rn, 1, col, NULL)) ||
       MAX(decode(rn, 2, ',' || col, NULL)) ||
       MAX(decode(rn, 3, ',' || col, NULL)) str
  FROM (SELECT id,
               col,
               row_number() over(PARTITION BY id ORDER BY col) AS rn
          FROM t_row_str) t
GROUP BY id
ORDER BY 1;

2)row_number + lead
适用范围：8i,9i,10g及以后版本
SELECT id, str
  FROM (SELECT id,
               row_number() over(PARTITION BY id ORDER BY col) AS rn,
               col || lead(',' || col, 1) over(PARTITION BY id ORDER BY col) || 
               lead(',' || col, 2) over(PARTITION BY id ORDER BY col) || 
               lead(',' || col, 3) over(PARTITION BY id ORDER BY col) AS str
          FROM t_row_str)
WHERE rn = 1
ORDER BY 1;

3)MODEL
适用范围：10g及以后版本
SELECT id, substr(str, 2) str FROM t_row_str
MODEL
RETURN UPDATED ROWS
PARTITION BY(ID)
DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)
MEASURES (CAST(col AS VARCHAR2(20)) AS str)
RULES UPSERT 
ITERATE(3) UNTIL( presentv(str[iteration_number+2],1,0)=0)
           (str[0] = str[0] || ',' || str[iteration_number+1])
ORDER BY 1;           

4)sys_connect_by_path
适用范围：8i,9i,10g及以后版本
SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
  FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
          FROM t_row_str) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
       AND id = PRIOR id
GROUP BY t.id;

适用范围：10g及以后版本
SELECT t.id id, substr(sys_connect_by_path(t.col, ','), 2) str
  FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
          FROM t_row_str) t
WHERE connect_by_isleaf = 1
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
       AND id = PRIOR id;

5)wmsys.wm_concat
适用范围：10g及以后版本
这个函数预定义按','分隔字符串，若要用其他符号分隔可以用，replace将','替换。

SELECT id, REPLACE(wmsys.wm_concat(col), ',', '/')
  FROM t_row_str
GROUP BY id;

 

 

5.字符串转换成多列
其实际上就是一个字符串拆分的问题。

CREATE TABLE t_str_col AS
SELECT ID,c1||','||c2||','||c3 AS c123
FROM t_col_str;

SELECT * FROM t_str_col;

1)substr + instr
适用范围：8i,9i,10g及以后版本
SELECT id,
       c123,
       substr(c123, 1, instr(c123 || ',', ',', 1, 1) - 1) c1,
       substr(c123,
              instr(c123 || ',', ',', 1, 1) + 1,
              instr(c123 || ',', ',', 1, 2) - instr(c123 || ',', ',', 1, 1) - 1) c2,
       substr(c123,
              instr(c123 || ',', ',', 1, 2) + 1,
              instr(c123 || ',', ',', 1, 3) - instr(c123 || ',', ',', 1, 2) - 1) c3
  FROM t_str_col
ORDER BY 1;

2)regexp_substr
适用范围：10g及以后版本
SELECT id,
       c123,
       rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 1), ',') AS c1,
       rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 2), ',') AS c2,
       rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 3), ',') AS c3
  FROM t_str_col
ORDER BY 1;

6.字符串转换成多行
CREATE TABLE t_str_row AS
SELECT id,
       MAX(decode(rn, 1, col, NULL)) ||
       MAX(decode(rn, 2, ',' || col, NULL)) ||
       MAX(decode(rn, 3, ',' || col, NULL)) str
  FROM (SELECT id,
               col,
               row_number() over(PARTITION BY id ORDER BY col) AS rn
          FROM t_row_str) t
GROUP BY id
ORDER BY 1;

SELECT * FROM t_str_row;

1)UNION ALL
适用范围：8i,9i,10g及以后版本
SELECT id, 1 AS p, substr(str, 1, instr(str || ',', ',', 1, 1) - 1) AS cv
  FROM t_str_row
UNION ALL
SELECT id,
       2 AS p,
       substr(str,
              instr(str || ',', ',', 1, 1) + 1,
              instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv
  FROM t_str_row
UNION ALL
SELECT id,
       3 AS p,
       substr(str,
              instr(str || ',', ',', 1, 1) + 1,
              instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv
  FROM t_str_row
ORDER BY 1, 2;

适用范围：10g及以后版本
SELECT id, 1 AS p, rtrim(regexp_substr(str||',', '.*?' || ',', 1, 1), ',') AS cv
  FROM t_str_row
UNION ALL
SELECT id, 2 AS p, rtrim(regexp_substr(str||',', '.*?' || ',', 1, 2), ',') AS cv
  FROM t_str_row
UNION ALL
SELECT id, 3 AS p, rtrim(regexp_substr(str||',', '.*?' || ',',1,3), ',') AS cv
  FROM t_str_row
ORDER BY 1, 2;

2)VARRAY
适用范围：8i,9i,10g及以后版本
要创建一个可变数组：
CREATE OR REPLACE TYPE ins_seq_type IS VARRAY(8) OF NUMBER;

SELECT * FROM TABLE(ins_seq_type(1, 2, 3, 4, 5));

SELECT t.id,
       c.column_value AS p,
       substr(t.ca,
              instr(t.ca, ',', 1, c.column_value) + 1,
              instr(t.ca, ',', 1, c.column_value + 1) -
              (instr(t.ca, ',', 1, c.column_value) + 1)) AS cv
  FROM (SELECT id,
               ',' || str || ',' AS ca,
               length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt
          FROM t_str_row) t
INNER JOIN TABLE(ins_seq_type(1, 2, 3)) c ON c.column_value <=
                                                     t.cnt
ORDER BY 1, 2;

3)SEQUENCE series
这类方法主要是要产生一个连续的整数列，产生连续整数列的方法有很多，主要有：
CONNECT BY,ROWNUM+all_objects,CUBE等。
适用范围：8i,9i,10g及以后版本
SELECT t.id,
       c.lv AS p,
       substr(t.ca,
              instr(t.ca, ',', 1, c.lv) + 1,
              instr(t.ca, ',', 1, c.lv + 1) -
              (instr(t.ca, ',', 1, c.lv) + 1)) AS cv
  FROM (SELECT id,
               ',' || str || ',' AS ca,
               length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt
          FROM t_str_row) t,
       (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) c
WHERE c.lv <= t.cnt
ORDER BY 1, 2;

SELECT t.id,
       c.rn AS p,
       substr(t.ca,
              instr(t.ca, ',', 1, c.rn) + 1,
              instr(t.ca, ',', 1, c.rn + 1) -
              (instr(t.ca, ',', 1, c.rn) + 1)) AS cv
  FROM (SELECT id,
               ',' || str || ',' AS ca,
               length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt
          FROM t_str_row) t,
       (SELECT rownum rn FROM all_objects WHERE rownum <= 5) c
WHERE c.rn <= t.cnt
ORDER BY 1, 2;

SELECT t.id,
       c.cb AS p,
       substr(t.ca,
              instr(t.ca, ',', 1, c.cb) + 1,
              instr(t.ca, ',', 1, c.cb + 1) -
              (instr(t.ca, ',', 1, c.cb) + 1)) AS cv
  FROM (SELECT id,
               ',' || str || ',' AS ca,
               length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt
          FROM t_str_row) t,
       (SELECT rownum cb FROM (SELECT 1 FROM dual GROUP BY CUBE(1, 2))) c
WHERE c.cb <= t.cnt
ORDER BY 1, 2;

适用范围：10g及以后版本
SELECT t.id,
       c.lv AS p,
       rtrim(regexp_substr(t.str || ',', '.*?' || ',', 1, c.lv), ',') AS cv
  FROM (SELECT id,
               str,
               length(regexp_replace(str || ',', '[^' || ',' || ']', NULL)) AS cnt
          FROM t_str_row) t
INNER JOIN (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) c ON c.lv <=
                                                                   t.cnt
ORDER BY 1, 2;

4)Hierarchical + DBMS_RANDOM
适用范围：10g及以后版本
SELECT id,
       LEVEL AS p,
       rtrim(regexp_substr(str || ',', '.*?' || ',', 1, LEVEL), ',') AS cv
  FROM t_str_row
CONNECT BY id = PRIOR id
       AND PRIOR dbms_random.VALUE IS NOT NULL
       AND LEVEL <=
           length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))
ORDER BY 1, 2;

5)Hierarchical + CONNECT_BY_ROOT
适用范围：10g及以后版本
SELECT id,
       LEVEL AS p,
       rtrim(regexp_substr(str || ',', '.*?' || ',', 1, LEVEL), ',') AS cv
  FROM t_str_row
CONNECT BY id = connect_by_root id
       AND LEVEL <=
           length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))
ORDER BY 1, 2;

6)MODEL
适用范围：10g及以后版本
SELECT id, p, cv FROM t_str_row
MODEL
RETURN UPDATED ROWS
PARTITION BY(ID)
DIMENSION BY( 0 AS p)
MEASURES( str||',' AS cv)
RULES UPSERT
  (cv 
   [ FOR p
        FROM 1 TO length(regexp_replace(cv[0],'[^'||','||']',null))
        INCREMENT 1
   ] = rtrim(regexp_substr( cv[0],'.*?'||',',1,cv(p)),','))
ORDER BY 1,2;

end.