Sheryl M. Larsen
Sheryl M. Larsen, Inc., 2004 年 3 月
2004 年 3 月 SQL 专家 Sheryl Larsen 使用例子揭示了表表达式在灵活性和性能方面的实际威力。
简介
关于 SQL 技巧我有一个惊世骇俗的言论 —— 一般来讲，大部分 DB2® 开发人员都 落后了 7 年。我之所以这样说，是因为在过去 12 个月的时间里，我周游了全世界（大约 100,000 英里），并与 5,000 多名 DB2 开发人员谈到了有关高级 SQL 的话题。在与这些人的接触当中我发现到，大家都至少学过我演讲或讲座中涉及的一项新的 SQL 功能，或者旧的 SQL 功能的某种用法。然而，我发现没有得到充分利用的一项功能是 表表达式（table expressions）。它们在非 OS/390® 平台上被称为内联视图（in-line view），这些功能存在都已经有 7 年了。其实，这些不过是嵌入在 FROM 子句中用圆括号括起来的 SELECT 语句，但如果适当地应用这些表表达式，是可以产生很大威力的。我并不责怪开发人员缺乏有关这一功能的知识。我相信，这只是因为它们已经被发送到一两个 Java TM类，而没有发送到 DB2 Version xTransition 类。
虽然表表达式从语法上看像是一项简单的功能，但是它们却可以执行许多实用而强大的过程，这些过程包括：
- 预过滤全外连接（full outer joins）。
- 预过滤左/右连接中的替换 NULL 的表（null-supplying table）。
- 分离 GROUP BY 操作。
- 从已有的源生成数据。
- 使用表表达式帮助调优查询。
在这些表表达式的用途当中，有些您可能是熟悉的，而有些可能就不熟悉了。我将分别讨论表表达式每种用途的实例，以展示其强大性。
我应邀在全球范围内宣传 SQL 技巧。我相信您将至少学到表表达式的一种用法，而您所学到的东西将对您的应用产生一定的影响，所以还请继续读下去。
预过滤全外连接
最近几年来，随着工作文件的移除和并行性的加入，全外连接在性能方面已得到了增强。然而，其语法并没有得到像左连接和右连接所得到的那样的魔力，例如自动谓语下推和谓语传递闭包。 图 1中所显示的全外连接没有产生预期的结果，例如，给出在指定日期范围内的所有订单，不管这些订单是否具有少于 40 个字符的描述，以及给出所有少于 40 个字符的描述，而不管是否有与之对应的订单：
图 1. 该语句不会产生预期的结果
SELECT COALESCE(O.ORDER_NO, 'Order Number not Available')
, COALESCE(D.DESC, 'Description not Available')
FROM ORDER O
FULL JOIN
DESCRIPTIONS D
ON O.ORDER_NO = D.ORDER_NO
WHERE O.ORDER_DATE BETWEEN '1999-01-01' AND CURRENT DATE
AND LENGTH(D.DESC < 40)
|
相反，以上 SQL 的结果看上去像是一个内连接（inner join），因为 图 1中所使用的语法迫使局部过滤在第二阶段才应用 —— 换句话说，是在连接 之后才应用。这意味着很多将要参与连接操作的行其实在后面的阶段就要被删除。为了强制性地在连接（第一阶段） 之前应用局部过滤，可以使用表表达式，如 图 2中所示。
图 2. 在连接之前强制执行过滤，以获得正确结果
SELECT COALESCE(O.ORDER_NO, 'Order Number not Available')
, COALESCE(D.DESC, 'Description not Available')
FROM
(SELECT O.ORDER
FROM ORDER O
WHERE O.ORDER_DATE BETWEEN '1999-01-01' AND CURRENT DATE) AS O
FULL JOIN
(SELECT D.DESC, D.ORDER_NO
FROM DESCRIPTIONS D
WHERE LENGTH(D.DESC < 40) AS D
ON O.ORDER_NO = D.ORDER_NO
|
'O' 表表达式显式地预过滤外部表，'D' 表表达式则显式地预过滤内部表。该语法将给出指定日期范围内的所有订单，不管这些订单是否具有少于 40 个字符的描述，以及给出所有少于 40 个字符的描述，不管是否有与之对应的订单。连接前条件（before join condition）可以确保只有最后的结果行才会在连接操作中被处理。
预过滤左连接和右连接的替换 null 的表
对于左连接和右连接，如果替换 null 的表放在 WHERE 子句中过滤，那么就是一个真正的内连接，而这可能并不是您在编写连接时所想要的。如果您真的想要一个左连接或者右连接，那么有两种选择：
- 将替换 null 的表的局部过滤放在 ON 子句中。
这样将把过滤放入到一个连接中条件（during-join condition）。然而，这并不会消除最后的结果行。对于替换 null 的表的连接中条件，它们只对“null out”行（保留下来的但是以 null 填充的行）有作用，而最后的结果行，包括来自受保护表的值，是由连接前条件预先决定的。请参阅 Terry Purcell 的前一期 专家会谈专栏，以查看有关连接中条件的例子。
- 将替换 null 的表的局部过滤放到一个表表达式中。
这将只对替换 null 的表中的“null out”行有作用。 图 3中的查询演示了表表达式的使用，很可能，这正是开发人员所需的：
图 3. 使用表表达式来强制在连接之前应用局部过滤
SELECT O.ORDER_NO
, COALESCE(D.DESC, 'Short Description not Available')
FROM ORDER O
LEFT JOIN
(SELECT D.DESC, D.ORDER_NO
FROM DESCRIPTIONS D
WHERE LENGTH(D.DESC) < 40) AS D
ON O.ORDER_NO = D.ORDER_NO
WHERE O.ORDER_DATE BETWEEN '1999-01-01' AND CURRENT DATE
|
图 3中的查询返回指定日期范围内的所有订单，不管这些订单是否具有少于 40 个字符的描述。
分离 GROUP BY 操作
如果您是在 DB2 平台、而不是 0S/390 和 z/0S 平台上开发，那么可以略过这一节，因为要么优化器会自动重写 GROUP BY 语句来分离 GROUP BY 操作并消除排序，要么您可以构造自动总结表（automatic summary table，AST），这种表可以消除排序。对于所有的大型主机（mainframe），您需要考虑重写自己的查询。
一般的报告往往必须提供详细信息，以及总结信息。GROUP BY 在 DB2 中是非常严格的子句：
- GROUP BY 的第一条规则是，必须首先 SELECT 要分组的行。
- 第二条规则是必须用内置的列函数聚集（aggregate）所有其他的行。
这两条规则的组合通常迫使您去连接表，以便将详细信息和总结信息融合成一个结果行，如以下查询所示：
SELECT C.CUST_ID, MIN(C.CUST_NAME) AS CUST_NAME,
MIN(C.CUST_PHONE)AS CUST_PHONE, SUM(S.SALES)AS TOTAL_SALES
FROM CUSTOMER C, SALES S
WHERE C.CUST_ID = S.CUST_ID
AND S.SALES_DATE BETWEEN :date-lo AND :date-hi
GROUP BY C.CUST_ID
|
这通常意味着在处理 GROUP BY 操作之前，来自每个表的符合标准的详细行要预先进行连接。假设在这个例子中，有 1000 个来自 CUSTOMER 的行与 200,000 个来自 SALES 的行相连接，然后再处理 GROUP BY 操作。相反，您可以使用表表达式来迫使优化器一个表一个表地进行聚集操作。 图 4中所示的查询显式地使优化器提早了 GROUP BY 处理：
图 4. 将 GROUP BY 移入表表达式以减少要连接的行数
SELECT C.CUST_NAME, C.CUST_PHONE, S.TOTAL_SALES
FROM CUSTOMER C,
, (SELECT S.CUST_ID, SUM(S.SALES) AS TOTAL_SALES
FROM SALES S
WHERE S.SALES_DATE BETWEEN :date-lo AND :date-hi
GROUP BY S.CUST_ID
) AS S
WHERE C.CUST_ID = S.CUST_ID
|
例子中表表达式 'S' 显式地分离了 GROUP BY 操作。这样往往只需在一两个表上进行聚集操作，详细行来自不同的表。该语法允许优化器搜索 SALES 表 CUST_ID 列上的索引支持，以执行 GROUP BY。可能的话，排序将避免。不管是否执行了排序，这都大大减少了参与连接的行数。这里不再像上一个例子中那样将 1000 个来自 CUSTOMER 的行与 200,000 个来自 SALES 的行相连接，现在是将 1000 个来自 CUSTOMER 的行只与 10,000 个 sales summary行相连接。GROUP BY 处理将表表达式结果压缩到一些总结行。
从已有源生成数据
至此，表表达式最强大的一个用途可以引用一位客户的话来解释：“当我们需要 DB2 中没有提供的数据时，我们用表表达式造出这样的数据来。” 图 5中所示的查询包含一个表表达式，它计算出对应于在该表表达式中生成的分组的销售额。
图 5. 在表表达式中生成子类（subcategory）数据
SELECT C.
SUBCATEGORY, SUM(S.SALES) AS S.TOTAL_SALES
FROM SALES S ,
(SELECT C.CUST_ID,
SUBSTR(C.COLX, :hvstart, :hvlngth) AS SUBCATEGORY
FROM CUSTOMER C
WHERE C.CUST_REGION BETWEEN :reg-lo AND :reg-high) AS C
WHERE C.CUST_ID = S.CUST_ID
GROUP BY C.
SUBCATEGORY
|
该查询对于周期性的报告很有效，即使需要排序，它也可以胜任。当需要弄清楚在列值中是否存在隐含意义时，该查询也同样很棒。然而，如果终端用户要频繁地请求一个公共起始位置和长度，即 SUBSTR(C.COLX, 2, 4)，那么应该将这一部分添加到表中，作为表自己的列。这将允许为这个新列提供一个索引，以避免排序。
图 6是使用表表达式为季度报表生成正确季度的例子。
图 6. 为季度报表生成子类数据
SELECT C.
SUBCATEGORY, SUM(S.SALES) AS S.TOTAL_SALES
FROM SALES S ,
(SELECT C.CUST_ID,
(CASE WHEN MONTH(SALE_DT) BETWEEN 2 AND 4 THEN 'Q1'
WHEN MONTH(SALE_DT) BETWEEN 5 AND 7 THEN 'Q2'
WHEN MONTH(SALE_DT) BETWEEN 8 AND 10 THEN 'Q3'
ELSE 'Q4' END) AS
SUBCATEGORY
FROM CUSTOMER C
WHERE C.CUST_REGION BETWEEN :reg-lo AND :reg-high) AS C
WHERE C.CUST_ID = S.CUST_ID
GROUP BY C.
SUBCATEGORY
|
只要在表表达式内使用了 AS 标识符命名所生成的数据，就可以在表表达式外的任何地方引用生成的数据。这意味着您也可以按照那个标识符排序，可以将其放入计算中，还可以将它放在表达式可以出现的任何地方。我们可以扩展 图 6中的例子，使其通过使用主机变量（host variable）处理更多的选项，如 图 7所示。
图 7. 使用主机变量以获得扩展的灵活性
SELECT C.
SUBCATEGORY, SUM(S.SALES) AS S.TOTAL_SALES
FROM SALES S ,
(SELECT C.CUST_ID,
(CASE WHEN MONTH(SALE_DT) BETWEEN :hv1a AND :hv1b THEN :hv1
WHEN MONTH(SALE_DT) BETWEEN :hv2a AND :hv2b THEN :hv2
WHEN MONTH(SALE_DT) BETWEEN :hv3a AND :hv3b THEN :hv3
WHEN MONTH(SALE_DT) BETWEEN :hv4a AND :hv4b THEN :hv4
WHEN MONTH(SALE_DT) BETWEEN :hv5a AND :hv5b THEN :hv5
WHEN MONTH(SALE_DT) BETWEEN :hv6a AND :hv6b THEN :hv6
WHEN MONTH(SALE_DT) BETWEEN :hv7a AND :hv7b THEN :hv7
WHEN MONTH(SALE_DT) BETWEEN :hv8a AND :hv8b THEN :hv8
WHEN MONTH(SALE_DT) BETWEEN :hv9a AND :hv9b THEN :hv9
WHEN MONTH(SALE_DT) BETWEEN :hv10a AND :hv10b THEN :hv10
WHEN MONTH(SALE_DT) BETWEEN :hv11a AND :hv11b THEN :hv11
ELSE :hv12 END) AS
SUBCATEGORY
FROM CUSTOMER C
WHERE C.CUST_REGION BETWEEN :reg-lo AND :reg-high) AS C
WHERE C.CUST_ID = S.CUST_ID
GROUP BY C.
SUBCATEGORY
|
现在该查询可以处理对于按月分组的无止境的请求 —— 如今您想要 GROUP BY 什么呢？前 4 个月作为一段时期，接下来的两个月归为另一时期，第 6 个月加上第 12 个月一起作为第三段时期，剩下的月份作为第四段时期，这样如何？在构建应用程序时一定要记住这一点。模拟相同的选项需要用到几百个视图。这就是为什么在我的大多数客户机上，视图都从生产应用程序中消声匿迹了，而只允许出现在终端用户环境中。
为获得更大的威力和灵活性，可添加一个用户定义函数，如 图 8所示。
图 8. 使用 UDF 来允许访问非关系数据
SELECT C.
SUBCATEGORY, SUM(S.SALES) AS S.TOTAL_SALES
FROM SALES S ,
(SELECT C.CUST_ID,
UDFUNC2(:hvparm1, :hvparm2) AS SUBCATEGORY
FROM CUSTOMER C
WHERE C.CUST_REGION BETWEEN :reg-lo AND :reg-high) AS C
WHERE C.CUST_ID = S.CUST_ID
GROUP BY C.
SUBCATEGORY
|
现在，该查询可以访问大型主机平台上任何可用源中的数据。例如，UDFUNC2 可以访问存储在分布式环境中的平面文件（flat file）中的数据，在表表达式 'C' 中引用它，将其称为 SUBCATEGORY，还可以对其使用 GROUP BY。现在您真正想要对什么使用 GROUP BY 呢？每当您需要获得或创建 DB2 环境本地没有的数据，但是又需要在关系操作（join、union、subquery、ORDER BY、GROUP BY，等等）中包括这种数据并且该数据只需用于查询的一次执行的时候，就应该想起使用表表达式。对于多次执行或者迭代，我建议使用全局临时表（这就是以后某天要讲的话题了）。
使用表表达式来帮助调优查询
查询调优是一个很大的主题，所以这里我只讨论表表达式在调优中发挥的作用。首先，将视图从 SYSIBM.SYSVIEWS 中移出并放入到 FROM 子句中，这样有助于查询调优器更加清楚该查询将要达到的目标。如果您往往凌晨时还在工作，那么这一点就很重要了。移除视图的间接好处是，使用主机变量具有更大的可扩展性。
为了演示使用表表达式进行前瞻性调优，我将向您展示一个查询，并一一介绍一些性能问题，之后再给出问题的解决方案。第一个查询是以下 5 个表的连接：
SELECT
Columns ?.
FROM TABX, TABY, TABZ, TAB1, TAB2
WHERE
join conditions
|
上面查询中的问题是表连接顺序。优化器分析每个表中参与连接的行数，估计不同顺序和连接方法所需的代价，并保持以 TABX、TABY 和 TABZ 的组合开始。期望的连接顺序从 TAB1 和 TAB2 开始。对于静态查询，影响连接顺序的方法有很多。但是，动态查询就没那么幸运了。还好有表表达式这个救星！表表达式通过使用 DISTINCT 关键字，迫使优化器预先连接表（也要求进行一次排序），从而发挥了其作用。如下所示：
SELECT
Columns?
FROM TABX, TABY, TABZ
,(SELECT DISTINCT COL1, COL2 ?.
FROM TAB1, TAB2
WHERE join conditions) AS PREJOIN
WHERE
remaining join conditions
|
现在该查询：
- 连接 TAB1 和 TAB2。
- 排序以除去可能存在的重复行。
- 处理其他表。
这种技术惟一的缺点就是排序以及创建和扫描一个逻辑工作文件会带来额外的开销。但是大多数情况下，这一缺点可以用优化的连接顺序来弥补。
下面的场景更可能出现在非 OS/390 平台上，但是这一解决方案对于所有平台都是适用的。在下面的例子中，优化器选择物化（materialize）整个表表达式 T2，然后使用合并扫描连接技术与 T1 相连接：
SELECT
Columns from either set of data
FROM table1 T1,
(SELECT T2.STUDENT_YEAR, SUM(C8) AS SUM8, MAX(C9) AS MAX9
FROM table2 T2
GROUP BY T2.STUDENT_YEAR) AS T2
WHERE T1.MAJOR_ID = T2.MAJOR_ID
|
该解决方案要求在表表达式之前插入关键字 TABLE。这使得表表达式可以与外部表相互关联。这种相互关联会影响优化器倾向于嵌套循环连接，并大大减少物化。在下面的例子中，通过将连接条件移入到表表达式里面来，可以使表表达式与外部表相互关联：
SELECT
Columns from either set of data
FROM table1 T1,
TABLE(SELECT T2.STUDENT_YEAR, SUM(C8) AS SUM8, MAX(C9) AS MAX9
FROM table2 T2
WHERE T1.MAJOR_ID = T2.MAJOR_ID
GROUP BY T2.STUDENT_YEAR) AS T2
|
该查询现在一次处理一个 T1.MAJOR_ID。只有符合那个 MAJOR_ID 的那些行才会在逻辑工作文件中物化。优化器认为工作文件越小，就越会选择嵌套循环连接。如果是在 OS/390 或 z/OS TM平台下的 DB2 上尝试这一解决方案，就要小心了，因为该解决方案并不总能提高性能。请检查 EXPLAIN 输出，使用评测工具，或者运行基准测试来验证这一调优行动是否成功。
结束语
如果您的目标是减少程序代码和增加处理的吞吐量，那么强大而又实用的表表达式的确很有帮助。如果可以使用表表达式来生成新的数据，并且将表表达式保留在 DB2 引擎中以执行连接、分组、排序和计算，就能够减少很多程序代码，因为不再需要声明多个游标。如果将主机变量添加到表表达式中，要增加一个查询的可扩展性就比较容易。调优查询、分离 GROUP BY 操作以及为外连接而预过滤表，这些都可以帮助提高处理的吞吐量。随您挑选吧。这项技术存在已经有一段时间了，这还得您去好好利用。我希望您在当地传播您的新发现，以帮助我更新全球范围的 SQL 技巧。
关于作者
Sheryl Larsen 是专门研究 DB2 的国际知名学者、顾问及讲师，以其在 SQL 方面渊博的专业知识而著称。Sheryl 在 DB2 领域已经有超过 16 年的经验，发表了许多文章和一些受欢迎的 DB2 帖子，并与人合写了一本书： DB2 Answers，Osborne-McGraw-Hill 出版。在 1999 & 2001 IDUG 会议上，她被投票选举为 Best Overall Speaker，并在 1997-2000 年担任 IDUG Solutions Journal 杂志的执行总编（Executive Editor）。目前，她是 Midwest Database Users Group ( mwdug.org)的主席，是 IBM DB2 Gold Consultants 计划的成员之一，同时还是 Sheryl M. Larsen 公司( www.smlsql.com) 的总裁，这是一家专门从事高级 SQL 咨询和教育的公司。可以通过 SherylMLarsen@cs.com与她联系。 |
|
