 | 级别： 初级 Serge Rielau, DB2 SQL 查询编译器开发, IBM 多伦多实验室
2002 年 9 月 01 日 对 UNION ALL 视图进行分区通常用于简化数据维护和克服单个表的可伸缩性限制。本文描述了 DB2 Universal Database 版本 8 的 Linux、UNIX 和 Windows 版怎样通过用 UNION ALL 定义的视图来允许您进行插入、更新和删除操作。它详细说明了用于插入处理的专利算法。
简介
对 UNION ALL 视图进行分区通常用于简化数据维护和克服单个表的可伸缩性限制。本文描述了 DB2® Universal Database™ 版本 8 的 Linux、UNIX® 和 Windows® 版怎样通过用 UNION ALL 定义的视图来允许您进行插入、更新和删除操作。它详细说明了用于插入处理的专利算法。
UNION ALL 视图的多种用途
DB2 从版本 2 就开始支持基于 UNION ALL 的视图。从那时起，这种视图就相当常见。UNION ALL 视图的绝大部分用途都属于下列类别之一：
-
不同但语义相关的表的合并。
这些类型的构造通常用作面向对象的层次结构的关系映射，并且表示对数据的
水平分区；也就是说，每一种特定的“类”都用一个表表示。
一个典型的示例是由人员、雇员和经理这些类组成的层次结构，其中人员、雇员和经理都是单独的关系表。要创建一个所有人员的视图，可以用 UNION ALL 视图来合并这些表。
-
相似表的合并。
相似表的 UNION ALL 构造经常用于隐藏对整体数据集的一个范围分区。每个分区都是一个单独的表，这简化了维护，增加了大小的限制，而且有些情况下甚至可以改善对整体数据的查询性能。
典型的示例是把一年的销售额数据分成四个表，每个表都包含了一个季度的数据。
-
联合数据源的集成。
有时那些必须合并的数据散布在本地和远程数据库中。这种情况下，除了将数据复制到本地，UNION ALL 是能获得所有数据的透明视图的唯一选择。
DB2 中远程对象用别名表示，视图可以构造成使它们和本地表或其它别名相关。例如，请参阅示例，
用关系连接和数据库视图构建联合系统（PDF 格式）。
动手示例
作为一个运行示例，让我们像上面讨论的那样把相似表合并起来。
- 首先我们需要创建其中的一个表：
CREATE TABLE Q1(product_no INT, sales INT, date DATE);
|
- 现在按此派生其它季度的表；
CREATE TABLE Q2 LIKE Q1;
CREATE TABLE Q3 LIKE Q1;
CREATE TABLE Q4 LIKE Q1; |
- 每个表必须只包括这个季度的数据。表 Q1 包括一月份（1）到三月份（3）的数据，表 Q2 包括四月份（4）到六月份（6）的数据，依此类推。让我们添加适当的约束。
ALTER TABLE Q1 ADD CONSTRAINT Q1_CHK_DATE
CHECK (MONTH(date) IN (1, 2, 3));
ALTER TABLE Q2 ADD CONSTRAINT Q2_CHK_DATE
CHECK (MONTH(date) IN (4, 5, 6));
ALTER TABLE Q3 ADD CONSTRAINT Q3_CHK_DATE
CHECK (MONTH(date) IN (7, 8, 9));
ALTER TABLE Q4 ADD CONSTRAINT Q4_CHK_DATE
CHECK (MONTH(date) IN (10, 11, 12)); |
- 现在我们开始把“销售数据”填入表中：
INSERT INTO Q1 VALUES (5, 6, '2001-01-02'),
(8, 100, '2001-02-28');
INSERT INTO Q2 VALUES (3, 10, '2001-04-11'),
(5, 15, '2001-05-19');
INSERT INTO Q3 VALUES (1, 12, '2001-08-27');
INSERT INTO Q4 VALUES (3, 14, '2001-12-29'),
(2, 21, '2001-12-12'); |
- 最后，通过合并所有季度表创建全年数据的视图。
CREATE VIEW FY AS
SELECT product_no, sales, date FROM Q1
UNION ALL
SELECT product_no, sales, date FROM Q2
UNION ALL
SELECT product_no, sales, date FROM Q3
UNION ALL
SELECT product_no, sales, date FROM Q4;
|
视图内容如下所示：
SELECT * FROM FY ORDER BY date, product_no;
PRODUCT_NO SALES DATE
----------- ----------- ----------
5 6 01/02/2001
8 100 02/28/2001
3 10 04/11/2001
5 15 05/19/2001
1 12 08/27/2001
2 21 12/12/2001
3 14 12/29/2001
7 record(s) selected. |
由于 UNION ALL 视图很常用，所以 DB2 具有特别的规则针对优化 union all 中的查询，以提高性能。详细信息，请查阅
在DB2 中使用 UNION ALL 视图进行分区（PDF 格式）。
例如，下面的语句只从 Q1 和 Q2 表进行选择，因为 DB2 的法则验证器能用表中的 CHECK 约束去证明 Q3 和 Q4 不受影响：
SELECT sum(sales) AS total FROM FY
WHERE MONTH(date) BETWEEN 3 AND 5;
TOTAL
-----------
25
1 record(s) selected.
|
对 FY 视图的更新和删除是简单的，因为 DB2 知道在哪个表中的行要被更新或要被删除，因此可以轻易更改。
下列语句更新 Q3 中的一行：
UPDATE FY SET sales = 20
WHERE product_no = 1 AND date = '2001-08-27';
|
也可从 Q3 中删除同样一行：
DELETE FROM FY
WHERE product_no = 1 AND date = '2001-08-27';
|
要重新插入已删除的行：
INSERT INTO FY VALUES (1, 20, '2001-08-27'); |
简介插入
请稍等！在文章的开头我们对基本表进行了插入操作。为什么？在 DB2 V8 之前，基本表是不能通过 UNION ALL 视图插入的。因此，应用程序需要清楚视图的结构，并插入到适当的基本表中。
现在非常灵活的全新逻辑使 DB2 能通过 UNION ALL 视图接受插入操作：
INSERT INTO FY VALUES (1, 20, '2001-06-03'),
(2, 30, '2001-03-21'),
(2, 25, '2001-08-30');
|
语义上，DB2 尝试着把任意给定的行插入到 UNION ALL 视图基于的每个表中。然后计算有多少个表接受了该行：
- 如果正好仅有一个表接受该行，那么插入被接受。
- 如果没有表接受该行，那么发出“无目标（no target）”的错误信息。
- 如果有多个表接受该行，那么发出“不明确的目标（ambiguous target）”的错误信息。
在上面的视图中，由于每个季度表中的约束涵盖了所有有效月份的值，因此始终只有一个表接受插入。
考虑一下，如果
Q1_CHK_DATE 中有一个输入错误，将使得四月份数据而不是一月份数据插入到 Q1 基本表中，这样将会发生什么呢：
ALTER TABLE Q1 DROP CONSTRAINT Q1_CHK_DATE;
ALTER TABLE Q1 ADD CONSTRAINT Q1_CHK_DATE
CHECK (MONTH(date) IN (
4, 2, 3));
|
现在不可能插入一月份数据了，因为没有能接受数据的目标表。
INSERT INTO FY VALUES (5, 35, '2001-01-14');
SQL20154N The requested insert operation into view "SRIELAU.FY"
is not allowed because no target table can be determined for a row.
Reason code = "1". SQLSTATE=23513
|
另一方面，插入四月份数据会引起冲突，因为 Q1 和 Q2 都可以接受这行。
INSERT INTO FY VALUES (3, 30, '2001-04-21');
SQL20154N The requested insert operation into view "SRIELAU.FY"
is not allowed because no target table can be determined for a row.
Reason code = "2". SQLSTATE=23513
|
注：已定义的约束不包含对空日期的显式处理。因为约束只拒绝
错误的内容，所以所有表都允许日期为空的行，从而生成一个“不明确的目标”的错误信息。
然而在上面的情况中，问题很明显在于有缺陷的约束和由此产生的不正确的分区，但不是所有的 UNION ALL 视图都进行分区，因此 DB2 很重要的一点是不过分讲究约束的结构。重要的不是对基本表中数据进行的分区，而是对给定了基本表约束的输入数据进行的分区。
但是插入到 UNION ALL 视图究竟是怎样工作的呢？插入到每个表中不是代价很高吗？一个有多个数据库分区的系统会怎么样呢？这种特性可以伸缩吗？
深入研究
让我们仔细研究通过 UNION ALL 工作插入的内部原理。为了有助于理解，每个步骤都用 SQL 进行说明。然而 DB2 的内部表示是 SQL 所能表达的内容的超集，因此这种转换只能是近似的。
但是在开始之前，让我们添加一些有趣的逻辑：声明 Q1 知道 9 号产品为 6。一个简单的 BEFORE 触发器就可以做到这个小伎俩：
CREATE TRIGGER I_Q1_PNO NO CASCADE BEFORE INSERT ON Q1
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
SET n.product_no =
CASE WHEN n.product_no = 9 THEN 6
ELSE n.product_no END;
|
接着我们定义：空日期被解释成当前日期。这里，我们同样使用 BEFORE 触发器：
CREATE TRIGGER I_Q1_NULL NO CASCADE BEFORE INSERT ON Q1
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
SET n.date
= COALESCE(n.date, CURRENT DATE);
CREATE TRIGGER I_Q2_NULL NO CASCADE BEFORE INSERT ON Q2
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
SET n.date
= COALESCE(n.date, CURRENT DATE);
CREATE TRIGGER I_Q3_NULL NO CASCADE BEFORE INSERT ON Q3
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
SET n.date
= COALESCE(n.date, CURRENT DATE);
CREATE TRIGGER I_Q4_NULL NO CASCADE BEFORE INSERT ON Q4
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
SET n.date
= COALESCE(n.date, CURRENT DATE); |
现在假设我们想要把下列三行插入到 FY 中：
INSERT INTO FY VALUES (7, 20, NULL),
(9, 30, '2001-03-21'),
(9, 25, '2001-04-30');
Input:
PRODUCT_NO SALES DATE
---------- ----- ----------
7 20 -
9 30 2001-03-21
9 25 2001-04-30
|
现在，让我们看看 DB2 做了些什么。
- 首先，算法（如
图 1）唯一地识别每一行：
TABLE(SELECT ROWNUMBER() OVER() AS RN,
product_no, sales, date
FROM Input) AS Step1
Step1:
RN PRODUCT_NO SALES DATE
-- ---------- ----- ----------
1 7 20 -
2 9 30 2001-03-21
3 9 25 2001-04-30
|
- DB2 编译器构建一个在内存中的表，表中包含了所有可能的目标表标识符再加上我们称之为
代表行（或者，亲切地称其为“caboose”（即货车的最后一节守车），因为它总是跟在常规表标识符后面）的一行。
TABLE(VALUES ('Q1')
('Q2')
('Q3')
('Q4')
('ZZ') /* Caboose */
) AS Step2(table_id)
Step2:
TABLE_ID
--------
Q1
Q2
Q3
Q4
ZZ
|
注：Caboose 始终存在。
图 1. 通过 UNION ALL 视图进行插入操作的 DB2 算法
- 现在我们使用嵌套循环连接（join）来构建输入表和在内存中的表的交叉乘积，它保留了表的顺序并提供很漂亮的组：
TABLE(SELECT rn, table_id, product_no, sales, date
FROM Step1 INNER JOIN Step2) AS Step3
Step3:
RN TABLE_ID PRODUCT_NO SALES DATE
-- -------- ---------- ----- ----------
1 Q1 7 20 -
1 Q2 7 20 -
1 Q3 7 20 -
1 Q4 7 20 -
1 ZZ 7 20 -
2 Q1 9 30 2001-03-21
2 Q2 9 30 2001-03-21
2 Q3 9 30 2001-03-21
2 Q4 9 30 2001-03-21
2 ZZ 9 30 2001-03-21
3 Q1 9 25 2001-04-30
3 Q2 9 25 2001-04-30
3 Q3 9 25 2001-04-30
3 Q4 9 25 2001-04-30
3 ZZ 9 25 2001-04-30
|
为什么我们要复制这些行呢？很简单，因为 SQL 要求 BEFORE 触发器在执行 CHECK 约束前触发。由于不同表中的 BEFORE 触发器会对新的转换变量作不同的更改，因此必须为每个可能的目标表创建专用副本。
- 所举示例中，在触发器 I_Q1_PNO、I_Q1_NULL 到 I_Q4_NULL 触发后，中间的结果如下所示：
Step4:
RN TABLE_ID PRODUCT_NO SALES DATE
-- -------- ---------- ----- ----------
1 Q1 7 20 2002-09-17
1 Q2 7 20 2002-09-17
1 Q3 7 20 2001-09-17
1 Q4 7 20 2001-09-17
1 ZZ 7 20 -
2 Q1 6 30 2001-03-21
2 Q2 9 30 2001-03-21
2 Q3 9 30 2001-03-21
2 Q4 9 30 2001-03-21
2 ZZ 9 30 2001-03-21
3 Q1 9 25 2001-04-30
3 Q2 9 25 2001-04-30
3 Q3 9 25 2001-04-30
3 Q4 9 25 2001-04-30
3 ZZ 9 25 2001-04-30
|
注：由于定义语义的方式，每个表的 BEFORE 触发器不得不触发，即使给定的行最终不会放入触发器的对象表中。BEFORE 触发器有副作用，比如发送电子邮件或执行 NEXTVAL 序列表达式所要触发的次数会比触发器定义语句中可能预计的要多。
- 既然已经考虑了 BEFORE 触发器，那么 CHECK 约束就可用于滤掉所有的不符合的行。为了有趣点，我们假设仍然使用 Q1_CHK_DATE 中有错的 CHECK 约束，该约束将（1，2，3）月份错用成了（4，2，3）月份：
TABLE(SELECT rn, table_id, product_no, ...
FROM Step4
WHERE (table_id = 'Q1'
AND month(date) IN (4, 2, 3))
OR (table_id = 'Q2'
AND month(date) IN (4, 5, 6))
OR (table_id = 'Q3'
AND month(date) IN (7, 8, 9))
OR (table_id = 'Q4'
AND month(date) IN (10, 11, 12))
OR (table_id = 'ZZ'))
AS Step5
Step5:
RN TABLE_ID PRODUCT_NO SALES DATE
-- -------- ---------- ----- ----------
1 ZZ 7 20 -
2 Q1 6 30 2001-03-21
2 ZZ 9 30 2001-03-21
3 Q1 9 25 2001-04-30
3 Q2 9 25 2001-04-30
3 ZZ 9 25 2001-04-30
|
由于 Caboose 没有 CHECK 约束，所有它永远不会被滤掉。因此我们可以这样结束：
- 只有第一行的 Caboose（漏掉的一月份）。
- 一个常规行（ Q1 加上留给三月份行的 Caboose）。
- 用在 Q1、Q2 中的四月份行，当然还有 Caboose。
- 现在所要做的就是给每个组编号：
TABLE(SELECT rn, table_id,
rownumber() over(partition by rn) as rank,
product_no, ...
FROM Step5) AS Step6
Step6:
RN TABLE_ID RANK PRODUCT_NO SALES DATE
-- -------- ---- ---------- ----- ----------
1
ZZ 1 7 20 2001-01-03
2 Q1 1 9 30 2001-03-21
2
ZZ 2 9 30 2001-03-21
3 Q1 1 9 25 2001-04-30
3 Q2 2 9 25 2001-04-30
3
ZZ 3 9 25 2001-04-30
|
- 现在，对 Caboose 等级的一个简单测试告诉我们该做什么：
- 'ZZ' 和 1 意味着该行被所有表的约束滤掉。这是“无目标”错误。
- 'ZZ' 和 2 意味着仅有一个表的约束接受了该行。这是成功的。
- 'ZZ' 和任何比 2 大的数意味着不止一个表的约束接受了该行。这是“不明确的目标”错误。
TABLE(SELECT rn, ....
FROM Step6
WHERE 1 = CASE WHEN table_id <> 'ZZ' THEN 1
WHEN rank = 2 THEN 1
WHEN rank = 1
THEN raise_error('no target')
ELSE raise_error('ambiguous target')
END
AS Step7 |
- 由于原子性的要求，遇到两种错误之一，就回滚整个语句，这样所有的行都被拒绝了。
按照这个测试，Caboose 行就能被滤掉，加入的列（除了目标表标识符）也被滤掉。然后用表标识符对各个表进行插入操作。
那这个算法的要点是什么呢？为什么这个专利算法会这么好呢？
- 当语句是预编译的并可以在每个处理节点处复制时，目标表的内存表就由 SQL 编译器进行排序。
- 对输入编号不用临时表就可以完成，还可以在处理节点上本地进行。
- 嵌套循环连接的结果会把输入数据（外部）保存为组，Caboose 放在每个组的最后，不需要排序或临时表。嵌套循环只需要处理那些对于正执行该操作的节点来说是本地的数据。
- 支持 BEFORE 触发器。
- 在插入操作真正发生前对 CHECK 约束进行处理，不要求对分区进行静态分析。
- 组（分级）中的最后编号不需要排序，可在每个处理节点上本地地完成。
其实，这意味着如果在我们的示例中，用算法处理的每行都源于不同的数据库分区，那么每个分区在发送将要插入到目标表的行之前，都可以独立执行全部算法，并不需要和其它节点通信。这个最后的通信是散列导向的，因此算法提供无瓶颈的可伸缩性。
并且，DB2 V8 也为公共触发器引入了完全并行化和管道化的 BEFORE 触发器处理，因此这个算法真是太棒了:-)。
可能的改进
当我们设法修正特定日期中的错误，而该日期在插入
后作为“分区键”时会发生什么呢？这可能会引起约束违例错误，因为 DB2 只能更新表中的行。
UPDATE FY
SET date = '2001-07-11'
WHERE product_no = 3 AND date = '2001-04-11';
SQL0545N The requested operation is not allowed because a row does
not satisfy the check constraint "SRIELAU.Q2.Q2_CHK_DATE".
SQLSTATE=23513
|
相反，需要删除该行并重新插入：
DELETE FROM FY
WHERE product_no = 3 AND date = '2001-04-11';
INSERT INTO FY VALUES (3, 10, '2001-07-11');
|
尽管看起来 DB2 V8 由于具有了 UNION ALL 视图的更新能力，从而向前迈进了一大步，但是我们还是可以设想一下进一步的改进：
- 正如 DB2 为 DB2 V7 的多个数据库分区组中的表引入分区键更新那样，可以期待有一天 UNION ALL 视图会支持使行从一个基本表迁移到另一个基本表的更新。
例如，下列语句会使一行从表 Q2 移到表 Q3。这意味着会从 Q2 删除该行，然后使用本文里描述的插入算法把更新行插入到 FY。
UPDATE FY
SET date = '2001-07-11'
WHERE product_no = 3 AND date = '2001-04-11'; |
- 而且，如果能对通过 UNION ALL 的插入进行一般化，以利用视图主体的断言从而分派额外的行或代替 CHECK 约束，可能会很令人满意的。那么上面的视图看起来会像这样：
CREATE VIEW FY AS
SELECT product_no, sales, date FROM Q1
WHERE month(date) IN (1, 2, 3)
UNION ALL
SELECT product_no, sales, date FROM Q2
WHERE month(date) IN (4, 5, 6)
UNION ALL
SELECT product_no, sales, date FROM Q3
WHERE month(date) IN (7, 8, 9)
UNION ALL
SELECT product_no, sales, date FROM Q4
WHERE month(date) IN (10, 11, 12)
WITH CASCADED CHECK OPTIONS;
|
注：上面的 CHECK OPTIONS 子句确保插入视图的每一行也都能使用同一个视图进行选择（select）操作。因此这些类型的视图被称为是
对称的视图。如果有人接受了如本文描述的那样通过 UNION ALL 视图进行插入的语义，这儿有一个相当精致的描述：
如果有一行正好能被 UNION ALL 视图中的一个分支接受，那么该行可以插入到 UNION ALL 视图中。
结束语
在这篇文章中我们讨论了哪种情况下 UNION ALL 视图是有用的，以及更新一个基于多个基本表的视图意味着什么。介绍了一种可伸缩的算法，该算法解决了对 UNION ALL 视图的插入操作。DB2 UDB V8 的 Linux、UNIX 和 Windows 版都支持可插入的 UNION ALL 视图，可帮助您保护所有 SQL 数据操作语句的底层数据的完整封装性。
免责声明
本文包含了样本代码。IBM 授予您（“被许可方”）使用这个样本代码的非专有的、版权免费的许可证。然而，样本代码是以“按现状”的基础提供的，没有任何形式的（不论是明示的，还是默示的）保证，包括对适销性、适用于特定用途或非侵权性的默示保证。IBM 及其许可方不对被许可方使用该软件所导致的任何损失负责。任何情况下，无论损失是如何发生的，也不管责任条款怎样，IBM 或其许可方都不对由使用该软件或不能使用该软件所引起的收入的减少、利润的损失或数据的丢失，或者直接的、间接的、特殊的、由此产生的、附带的损失或惩罚性的损失赔偿负责，即使 IBM 已经被明确告知此类损害的可能性，也是如此。
关于作者 | 
| |
Serge Rielau于六年前开始了他的 DB2 研发生涯，头一年他在 Almaden 研究中心从事有关内联 SQL PL 及结构类型的研究实习。接下来的五年里，他一直负责各种 SQL 增强技术；例如，带类型的视图和生成的列。
作为一名 SQL 查询编译器（SQL Query Compiler）的技术经理，如今他不仅推动了 DB2 的语言变化，还推动了 SQL 标准的的语言变化。您可以通过 comp.databases.ibm-db2 或
srielau@ca.ibm.com与他联系。
|
对本文的评价
| |
