内容


在 Informix Dynamic Server 中使用 MERGE 语句

免费下载:IBM® Informix® 11.7 试用版(包括 Ultimate Edition、Developer Edition 和 Innovator-C Edition)
下载更多的 IBM 软件试用版,并加入 IBM 软件下载与技术交流群组,参与在线交流。

简介

merge 语句用于基于 on 子句中指定的条件,来更新、插入或者删除特定目标表行。 IBM Informix Dynamic Server 中实现的 merge 语句遵循 SQL 2003 标准。 Merge 在数据仓库环境(ETL)中特别有用,将其中的事务表合并到更大的仓库表中。 Merge 还替换了执行条件更新与插入客户端逻辑 upsert。 merge 语句在有效性与性能方面都有提高,因为整个操作都通过来自服务器的单条语句执行。

用户场景简介

本文中的场景阐明了需要使用 merge 语句的情形。

场景 1

考虑来自事务系统的数据必须移动到数据仓库中的情形。数据仓库的模式与事务性模式不同。这需要在将来自事务系统的数据加载到数据仓库表中之前,对其进行转换。来自事务系统的新记录必须插入到数据仓库表中。经过正确的转换后,现有记录被更新。

完成这一任务的最佳方法是什么?目前有的很多 ETL 工具,可在将数据加载到数据仓库之前对数据进行提取与转换。然而,可利用 merge SQL 语句来更方便地完成转换与清理流程。

例如,假设在事务数据库中存在名为 customer_trans 的表。当此表加载到目标数据仓库表 customer 中,该表位于数据库 stores_demo 中,目标表可能会具有比表 customer_trans 更多或者更少的列。即使列数匹配,在相同列名及类型之间还可能存在语义上的区别。 清单 1 展示了用于本场景的 merge 语句的例子。

清单 1. merge 语句示例
MERGE INTO customer t
USING customer_trans s
ON s.customer_num=t.customer_num
WHEN MATCHED THEN
UPDATE SET t.address1 = s.address1 AND t.address2 = s.address2 AND 
t.city=s.city AND t.zipcode=s.zipcode AND t.phone=s.phone
WHEN NOT MATCHED THEN
INSERT (t.fname, t.lname, t.company, t.address1, t.address2, t.city, 
t.zipcode, t.phone) VALUES
 (s.fname, s.lname, s.company, s.address1, s.address2, s.city, 
 s.zipcode, s.phone);

场景 2

考虑另一个情形,在应用程序开发过程中,想要在条件满足时更新特定表的行,并且想在条件不满足时向同一个表中插入行。您可在客户端应用程序中或者作为存储过程来实现此类逻辑。在客户端应用中实现这一逻辑将对性能造成负面影响。在存储过程中实现这一逻辑需要开发及维护成本,因为存储过程语言支持会因为供应商的不同而不同。利用本场景中的 merge 语句将会简化开发工作并能提高性能。

从 SQL 的角度看,merge 语句是与其他 DML 语句具有相同语义的 DML SQL 语句,包括 insertupdate、以及 delete。虽然 merge 语句执行条件插入、更新与删除,但它本质上是原子性的。这意味着,当事务日志启动时,要么应用整个 merge 语句,要么什么都不应用, merge 语句的使用有一些限制,本文会对其做一些概述。

从概念上说,merge 函数通过应用 on 子句过滤,将源表行与目标表行进行联接,所有联接的行都在目标中进行更新或者删除。没有与目标联接的源表行被插入到目标中。此外,在 merge 语句中,源表不必是表。正如本文中所描述的,它可以是复杂查询或者视图。

理解语法

清单 2 -- 利用 stores_demo 数据库,展示了 merge 语句典型的使用示例。利用事务表 customer_trans 来将行合并到事实表 customer 中,如果客户表中已有记录,则对客户电话号码进行更新,否则,向客户表中插入新的客户记录。

清单 2. Merge 使用
CREATE TABLE customer_trans(
        customer_num            serial(101),
        fname                   char(15),
        lname                   char(15),
        company                 char(20),
        address1                char(20),
        address2                char(20),
        city                    char(15),
        state                   char(2),
        zipcode                 char(5),
        phone                   char(18)
        );


INSERT INTO customer_trans SELECT * FROM CUSTOMER WHERE 
customer_num = 101;

UPDATE customer_trans SET phone = '408-222-2345' WHERE 
customer_num = 101;

INSERT INTO customer_trans VALUES (129,'Mike','Plaza',
'Sporting Goods','430 Walnut Dr',NULL, 'Fremont','CA','94538',
'510-236-8892');

MERGE INTO customer as C
USING customer_trans as CT
ON C.customer_num = CT.customer_num
WHEN MATCHED THEN UPDATE SET C.phone = CT.phone
WHEN NOT MATCHED THEN INSERT VALUES (CT.customer_num, CT.fname, 
CT.lname, CT.company, CT.address1, CT.address2, CT.city, 
CT.state, CT.zipcode, CT.phone)

Listing 2 合并了两行,一个记录被更新,一个记录被插入。可利用 清单 3 其中的 select 语句进行确认。

清单 3. Select 语句的使用
SELECT * FROM customer WHERE customer_num IN (101, 129);

customer_num  101
fname         Ludwig
lname         Pauli
company       All Sports Supplies
address1      213 Erstwild Court
address2
city          Sunnyvale
state         CA
zipcode       94086
phone         408-222-2345

customer_num  129
fname         Mike
lname         Plaza
company       Sporting Goods
address1      430 Walnut Dr
address2
city          Fremont
state         CA
zipcode       94538
phone         510-236-8892

2 row(s) retrieved.

清单 4 展示了 merge 语句的使用,其中匹配的行被从目标表中删除,而未匹配的行被插入。对于子句 when matcheddeleteupdate 命令互斥,这意味着在 merge 语句中同时只能指定两者之一。

清单 4. 合并删除
MERGE INTO customer as C
USING customer_trans as CT
ON C.customer_num != CT.customer_num
WHEN MATCHED THEN  DELETE
WHEN NOT MATCHED THEN INSERT VALUES (CT.customer_num, CT.fname, 
CT.lname, CT.company, CT.address1, CT.address2, CT.city, 
CT.state, CT.zipcode, CT.phone);

清单 5 展示了 merge 语句的其他的使用情况,其中 WHEN MATCHED 和 WHEN NOT MATCHED 子句是可选的。当没有指定这些子句之一时,这部分的 merge 将被忽略。在第一个 merge 语句中,所有未匹配的行将被忽略,而销售表中匹配的行将被更新。类似地,在第二个 merge 语句中,只有未匹配的行将被插入到客户表中,并且所有匹配的行将被忽略。

清单 5. 独占更新、插入或者删除
MERGE INTO customer AS C
USING customer_trans AS CT
ON C.customer_num = CT.customer_num
WHEN MATCHED THEN UPDATE SET C.phone = CT.phone;

MERGE INTO customer as C
USING customer_trans as CT
ON C.customer_num = CT.customer_num
WHEN NOT MATCHED THEN INSERT VALUES (CT.customer_num, CT.fname, 
CT.lname, CT.company, CT.address1, CT.address2, CT.city, CT.state,
CT.zipcode, CT.phone);

merge 语句的 Informix 实现允许灵活地对 WHEN MATCHED 和 WHEN NOT MATCHED 子句进行排序,如清单 6 所示。

清单 6. 更新、插入、以及删除子句的灵活排序
MERGE INTO customer as C
USING customer_trans as CT
ON C.customer_num = CT.customer_num
WHEN NOT MATCHED THEN INSERT VALUES (CT.customer_num, CT.fname, 
CT.lname, CT.company, CT.address1, CT.address2, CT.city, CT.state,
CT.zipcode, CT.phone)
WHEN MATCHED THEN UPDATE SET C.phone = CT.phone;

了解 merge 规则

merge 语句强制以下基于关系原则的一致性检查。

  • 目标表中的任何行都不会被更新或者删除多次。
  • 插入的行不能被更新或者删除。

要明确 merge 语句会在被合并的数据上进行特定固有控制。如果一个行已被更新或者删除,那么不能针对该行再次更新与删除,再次与任何源表联接。如果该情形无法避免,Informix 会抛出一个 -26095 错误消息。例如,如果复制已被更新的、与相同目标表行匹配的源表行,那么您将会看到错误消息。类似地,插入到目标表中的行将不会再次考虑与源表行进行联接。

图 1 展示了一个例子,其中源表与目标表合并了。 当 Informix 合并源表 customer_trans 到目标表 customer 中,目标表的第一行获得更新资格,因为它与 customer_num 中具有相同值 10 的源行匹配。还有其他行中列 customer_num 的值为 10,其与目标表中的相同行联接。因为目标行已经被更新一次,当这种情形发生时,Informix 会抛出 -26095 错误。

图 1. Merge 规则
具有值 10 的行 Customer_trans 映射到具有值 10 的行 Customer_num,清单 7 中展示了附加代码
具有值 10 的行 Customer_trans 映射到具有值 10 的行 Customer_num,清单 7 中展示了附加代码

merge 语句中,更新操作遵循 update 语句规则,而插入操作遵循 insert 语句规则。

理解内部处理

merge 语句首先进行(外部联接或者内部联接,取决于 merge 语句)源与目标表行之间的联接。在 merge 语句的 on 子句中指定了联接条件。

可以很方便地利用清单 7 中展示的 explain 输出来描述其细节, 可以对源表进行顺序扫描,并且在目标表上完成索引查找。而且,嵌套循环联接方法用于联接源表与目标表。注意,所执行的联接是个外部联接。匹配与未匹配行都被送入 upsert 迭代器中。迭代器 upsert 执行实际的更新或者插入及删除操作。

清单 7 还展示了用于存储中间连接结果的临时文件。在某些情况下,由于是由优化器决定的,就不需要中间临时表,这会反映在计划中。

清单 7. 样例 explain 输出
QUERY: (OPTIMIZATION TIMESTAMP: 05-05-2010 16:32:39)
------
MERGE INTO customer as C
USING customer_trans as CT
ON C.customer_num = CT.customer_num
WHEN MATCHED THEN UPDATE SET C.phone = CT.phone
WHEN NOT MATCHED THEN INSERT VALUES (CT.customer_num, CT.fname, 
CT.lname, CT.company, CT.address1, CT.address2, CT.city, 
CT.state, CT.zipcode, CT.phone)


Estimated Cost: 4
Estimated # of Rows Returned: 1
Temporary Files Required For: MERGE

  1) informix.ct: SEQUENTIAL SCAN

  2) informix.c: INDEX PATH

    (1) Index Name: rcpachip. 100_1
        Index Keys: customer_num   (Serial, fragments: ALL)
        Lower Index Filter: 
informix.c.customer_num = informix.ct.customer_num NESTED LOOP JOIN

对于同时包含插入和更新子句的 merge 语句,外部联接会在源表及具有目标表空扩展的目标表之间执行。然后,upsert 迭代器将更新已经连接的行,并基于 insert 子句规范将空扩展行插入到目标表中。

对于仅包含更新子句或删除子句的 merge 语句, 内部联接会在源表与目标表之间执行。对于目标表中匹配的行,将依据更新或者删除子句规范来进行更新或者删除,如图 2 所示。

图 2. merge 语句的处理
目标及源表送入 Join,它送入 Upsert,并在此循环

on 子句条件完全在联接级别应用。on 子句中的过滤器能够包含复杂查询或者简单表达式。

在某些条件下,会在 merge 语句执行过程中创建内部临时表,如清单 7 所示。

merge 语句还可内嵌到存储过程或者处于 C 或者 Java® UDR 中。此过程可被引用来执行合并。 merge 语句还可在 MACH-11 集群环境中的可更新备用节点上执行。

对于不同的隔离级别,merge 语句遵循与更新或者插入语句相同的语义。在目标表行上保持更新锁定。在执行实际更新时,这些锁定被转换为独占锁定。当合并过程中出现错误时,整个事务回滚。

约束与冲突探索

Merge 语句遵循目标表或源表上的约束。 其行为与插入、更新或者删除语句相似。 因此,对于将其他表的主键作为外键、并且没有相应的数据字段的目标表执行合并时,会出现错误 691: Missing key in referenced table for referential constraint。如果对目标表定义了冲突,冲突表与诊断表将保留没有满足约束的行。在执行由 merge 语句所引起的插入或者更新操作时,表将保持惟一索引,如清单 8 所示。

清单 8. 带约束和冲突的 merge 语句
     1  DATABASE stores_demo;
     2  CREATE TABLE stock_br1
     3          (
     4          stock_num               smallint,
     5          manu_code               char(3),
     6          description             char(15),
     7          unit_price              money(6),
     8          unit                    char(4),
     9          unit_descr              char(15),
    10         primary key (stock_num, manu_code)
    11           );
    12
    13  ALTER TABLE stock ADD CONSTRAINT CHECK (unit_price > 1) 
    14         CONSTRAINT  chk_uprice FILTERING;
    15
    16  START VIOLATIONS TABLE FOR stock;
    17
    18  INSERT INTO stock_br1 VALUES(414,"HRO","football shoes",
    19                          '$1',"each","case");
    20
    21  MERGE INTO stock T
    22  USING (SELECT * FROM stock_br1) AS S
    23  ON(T.stock_num=S.stock_num) 
    24  WHEN NOT MATCHED THEN
    25  INSERT(T.stock_num,T.manu_code,T.description,T.unit_price,
    26         T.unit,T.unit_descr)
    27  VALUES(S.stock_num,S.manu_code,S.description,S.unit_price,
    28         S.unit,S.unit_descr);
    29
    30  SELECT * FROM stock_vio;
    31  SELECT * FROM stock_dia;
    32
    33  UPDATE stock_br1 SET unit_price='$100' WHERE unit_price='$1';
    34
    35  MERGE INTO stock T
    36  USING (SELECT * FROM stock_br1) AS S
    37  ON(T.stock_num=S.stock_num)
    38  WHEN NOT MATCHED THEN
    39  INSERT(T.stock_num,T.manu_code,T.description,T.unit_price,
    40         T.unit,T.unit_descr)
    41  VALUES(S.stock_num,S.manu_code,S.description,S.unit_price,
    42         S.unit,S.unit_descr);
    43
    44	SELECT * FROM stock WHERE stock_num=414;
    45	STOP VIOLATIONS TABLE FOR stock;

清单 8 中的代码创建了一个与 stock 相同的表 stock_br1。对表 stock 进行修改,向 unit_price 增加了一个约束。冲突与诊断表启用了。当想在第 21 行进行合并时,会得到结果 0 rows merged 因为检查约束发生了冲突。这在冲突与诊断表中有记录。在第 35 行上的合并成功了,因为该处没有发生任何约束冲突。

定义触发器

可在源表及目标表上定义多个触发器。但是在合并操作期间,仅可操作插入、更新、及删除触发器。选择触发器将被忽略。目标视图上的 instead of 触发器返回一个 26096 错误。

因为合并是插入、更新以及删除语句的组合,所以可在事件前后激活插入、更新以及删除触发器。 对于每一个插入、更新或者删除的行都激活 for-each-row 触发器。 因此,在 merge-insert 操作过程中, 仅激活目标表上的 for-each-row insert 触发器。 类似地,在 merge-update 和 merge-delete 操作过程中,仅激活目标表上的 for-each-row update 或者 for-each-row delete 触发器。清单 9 展示了带有触发器的 merge 语句。

清单 9. 带有触发器的 Merge
 DATABASE stores_demo;
 CREATE TABLE stock_br1
          (
          stock_num               smallint,
          manu_code               char(3),
          description             char(15),
          unit_price              money(6),
          unit                    char(4),
          unit_descr              char(15),
         primary key (stock_num, manu_code)
           );
  create table tmp_tb(col1 char(50));

  CREATE TRIGGER upd_up UPDATE OF unit_price
    ON stock
  FOR EACH ROW(insert into tmp_tb values('UPDATE FOR EACH ROW'));

  CREATE TRIGGER upsert2 UPDATE OF unit_price
  ON stock
  BEFORE(insert into tmp_tb values('UPDATE BEFORE'))
  AFTER(insert into tmp_tb values('UPDATE AFTER'));

  CREATE TRIGGER ins_up INSERT
  ON stock
  FOR EACH ROW(insert into tmp_tb values('INSERT FOR EACH ROW'));

  CREATE TRIGGER upsert1 INSERT
  ON stock
  BEFORE(insert into tmp_tb values('INSERT BEFORE'))
  AFTER(insert into tmp_tb values('INSERT AFTER'));


  INSERT INTO stock_br1 VALUES(414,"HRO","football shoes",'$100',
         "each","case");

  INSERT INTO stock_br1 VALUES(415,"HRO","football jsey",'$150',
         "each","case");

  MERGE INTO stock T
  USING (SELECT * FROM stock_br1) as S
  ON(T.stock_num=S.stock_num)
  WHEN MATCHED THEN UPDATE SET T.unit_price='$1000'
  WHEN NOT MATCHED THEN
  INSERT(T.stock_num,T.manu_code,T.description,T.unit_price,
         T.unit,T.unit_descr)
  VALUES(S.stock_num,S.manu_code,S.description,S.unit_price,
         S.unit,S.unit_descr);

  SELECT * FROM tmp_tb;

清单 9 中的代码与清单 8 中的一样,创建了一个相同的源表 stock_br1。 在 清单 9 中,为 stock 目标表上每个行创建四个触发器:

  • Update
  • Insert before
  • Insert after
  • Insert update

merge 语句仅向 stock 表插入两行, 但是触发了 3 个事件:upsert1、upsert2、和 ins_up。即使仅发生插入,所有更新事件也会被触发。

清单 10 展示了最后一行中 SQL 语句的输出。

清单 10. SQL 语句的输出
INSERT BEFORE
UPDATE BEFORE
INSERT FOR EACH ROW
INSERT FOR EACH ROW
INSERT AFTER
UPDATE AFTER

使用视图

视图可以作为 merge 语句的源或者目标,但是对于插入、更新或者删除,则需要所需的特权。还可在 merge 语句中 using 子句的内部使用视图。如果目标表是视图,则触发器 instead of 不支持视图。

清单 11 展示了具有视图的 merge 语句。

清单 11. 具有视图的 merge 语句
DATABASE stores_demo;
CREATE VIEW
        custview
          (firstname, lastname, company, city)
    AS
    SELECT fname, lname, company, city
    FROM customer
    WHERE city = 'Redwood City'
    WITH CHECK OPTION;

GRANT DELETE, INSERT, SELECT, UPDATE
    ON custview
    TO PUBLIC;

CREATE TABLE s_view(
    firstname   char(15),
    lastname    char(15),
    company char(20),
    city    char(15)
    );

INSERT INTO s_view VALUES ("Brian","Cahill","NIKE","Redwood City");
INSERT INTO s_view VALUES ("Chris","Hong","ADIDAS","Redwood City");
INSERT INTO s_view VALUES ("Kevin","Mills","REEBOK","Redwood City");
INSERT INTO s_view VALUES ("Anthony","Higgins","Play Ball!",
                           "Redwood City");
INSERT INTO s_view VALUES ("Donald","Quinn","Quinn's Sports",
                           "Redwood City");

MERGE INTO custview T
USING (select * from s_view) as S
ON(T.firstname=S.firstname)
WHEN MATCHED THEN UPDATE
SET company="NIKE"
WHEN NOT MATCHED THEN
INSERT(T.firstname,T.lastname,T.company,T.city)
VALUES(S.firstname,S.lastname,S.company,S.city);

SELECT * from custview;

在清单 11 中,创建了一个视图和表,用于合并数据。 merge 语句更新两行(对于 Anthony 和 Donald,因为它们匹配更新条件)并插入目标表中没有的三行。运行清单 11 的输出展示于清单 12 中。

清单 12. 运行具有视图的 merge 语句的输出
firstname       lastname        company              city            

Anthony         Higgins         NIKE                 Redwood City   
Donald          Quinn           NIKE                 Redwood City   
Roy             Jaeger          AA Athletics         Redwood City   
Frank           Albertson       Sporting Place       Redwood City   
Arnold          Sipes           Kids Korner          Redwood City   
Brian           Cahill          NIKE                 Redwood City   
Chris           Hong            ADIDAS               Redwood City   
Kevin           Mills           REEBOK               Redwood City   

8 row(s) retrieved.

分布式场景支持

merge 语句支持分布式查询,但仅限于目标表不是远程表。可在访问处于多个服务器实例中的数据库的子句中包含分布式查询。merge 语句还支持在 set 子句的子查询,以及 values 子句中的子查询中采用远程表、同义词以及视图。

在服务器实例 r_server 上,创建源表并插入数据,如清单 13 所示。

清单 13. 分布式场景中的 Merge 语句
DATABASE stores_demo;
CREATE TABLE stock_br1
        (
        stock_num               smallint,
        manu_code               char(3),
        description             char(15),
        unit_price              money(6),
        unit                    char(4),
        unit_descr              char(15),
       primary key (stock_num, manu_code)
         );

INSERT INTO stock_br1 VALUES(414,"HRO","football shoes",
'$100',"each","case");

在目标表所在的服务器上,运行 merge 语句,如清单 14 所示。

清单 14. 在分布式场景中运行 merge 语句
DATABASE stores_demo;
MERGE INTO stock T
USING (SELECT * FROM r_server@stores_demo:stock_br1) as S
ON(T.stock_num=S.stock_num)
WHEN NOT MATCHED THEN
INSERT(T.stock_num,T.manu_code,T.description,
T.unit_price,T.unit,T.unit_descr)
VALUES(S.stock_num,S.manu_code,S.description,
S.unit_price,S.unit,S.unit_descr);

清单 14 中的 merge 语句将来自远程表中的数据作为源表数据来插入。

处理基于标签的访问控制

merge 语句可被应用到基于标签的访问控制(LBAC)表中。LBAC 编写的访问规则会在合并到目标表的过程中执行。在从源表及目标表读取数据的过程中,LBAC 读取访问规则被执行。以下是在 LBAC 表上使用 merge 语句的不同方法。

对于源表及目标表采用相同的安全策略进行保护。
如果在 merge 表中的插入列清单中没有指定 IDSSECURITYLABEL 列,那么用户标签会被隐式插入。如果 IDSSECURITYLABEL 列包含在插入列清单中,那么将向目标表插入显式标签。

由于执行了 LBAC 规则,因此执行 merge 的用户必须具有相应的标签,否则用户必须取得豁免,这样所有的标签行才能成功地更新或者插入。

源表受保护,目标表不受保护。
进入目标的行失去了保护,因为目标表不受保护。然而,在从源表读取数据期间,LBAC 读取访问规则被执行。
目标表受保护,源表不受保护。
会为所有插入目标表的行指定一个默认标签。执行 merge 的用户必须取得豁免,或者具有相应的标签,才能成功执行 merge。

标签可显式或隐式插入。如果执行 merge 的用户获取了 LBAC 写访问规则的豁免,整个 merge 操作可能会成功,因为用户具有向任何标签修改或插入行的豁免。当用户具有豁免时,插入目标表的行可以具有隐式或显式标签。如果没有在插入列值清单中显式指定标签,那么插入的行会带有用户的标签。

另一方面,如果用户不具有任何豁免,但是取得了标签,merge 的成功操作要基于源表中哪些行有资格与目标表行进行联接,以及是否在任何用户标签上有更新或者删除行。如果有些行具有与用户标签不同的标签,以及更新资格,那么 merge 操作可能会由于缺少合适的 LBAC 凭据而失败。清单 15 演示了这一问题。

在清单 15 中,c1 是 IDSSECURITYLABEL 列。用户具有前面所描述的豁免,它未取得标签。所有插入的行将具有与源表行相同的标签。

清单 15. LBAC 表及列上的 Merge
MERGE INTO target t
USING source s
ON s.c2=t.c2
WHEN MATCHED THEN
UPDATE SET t.c3="updated"
WHEN NOT MATCHED THEN
INSERT (t.c1, t.c2, t.c3) VALUES (s.c1, s.c2, "inserted");

如果用户不具有豁免,Informix 会在清单 15 执行后返回错误,如清单 16 所示。

清单 16. 丢失 LBAC 凭据的错误消息 credentials
MERGE INTO target t
USING source s
on s.c2=t.c2
WHEN MATCHED THEN
UPDATE set t.c3="updated"
WHEN NOT MATCHED THEN
INSERT (t.c1, t.c2, t.c3) values (s.c1, s.c2, "inserted");
 8249: User does not have the LBAC credentials 
to perform UPDATE on table (sqlqa.target).
Error in line 12
Near character position 55

考虑另一个例子,其中源表及目标表中的行是 engineermanager 标签。 假设 manager 标签支配 engineer 标签。如果执行 merge 的用户具有 engineer 标签,那么仅具有插入或更新资格的行将处于 engineer 标签级别,并且 merge 操作成功。如果执行 merge 的用户具有 manager 标签,那么一些有资格的行将处于 manager 标签级别,而一些其他行将处于 engineer 标签级别。显而易见,处于 manager 级别的用户不能修改处于 engineer 级别的行,因此 merge 操作失败。

审计 merge 语句

不存在单独的审计助记符用于审计 merge 语句。但是,如果为插入、更新或者删除事件设置审计掩码(INRW、UPRW、以及 DLRW),那么执行 merge 语句时将产生这些事件的日志。

指定指令

所有在 merge 语句中指定的指令都会应用到源表与目标表的联接中。explain 指令是应用到 merge 语句的一个例外。如下是一些适用的指令:

  • USE_NL
  • USE_HASH
  • AVOID_HASH
  • FULL
  • AVOID_FULL
  • INDEX
  • ORDERED

noexecute 指令可用于获取 explain 输出,而不必真正执行 merge 语句。当无法应用某一指令时,相应的消息会出现在 explain 输出中。

Informix 优化器可能会不认同您所指定的一些指令。最常见的是,当在同一 语句中同时指定了更新和插入子句时,外联接会在源表与目标表之间执行。类似地,如果仅指定了插入子句,外联接会在源表与目标表之间执行。在相同的外联接中,源表是主导表,其行受到保护。目标表已经空扩展,是非主导表。Informix 不允许外联接中的非主导表位于哈希联接的 probe 端,或者嵌套循环连接的外端。在清单 17 中,优化器不认同 use_hash 指令,因为源表(主导表)指定到了 build 端。

清单 17. 具有指令的 Merge
MERGE {+ USE_HASH(CT/BUILD) } INTO customer as C
USING customer_trans as CT
ON C.customer_num = CT.customer_num
WHEN MATCHED THEN UPDATE SET C.phone = CT.phone
WHEN NOT MATCHED THEN INSERT VALUES (CT.customer_num,
 CT.fname, CT.lname, CT.company, CT.address1, 
CT.address2, CT.city, CT.state, CT.zipcode, CT.phone)


QUERY: 
------
MERGE {+ USE_HASH(CT/BUILD) } INTO customer as C
USING customer_trans as CT
ON C.customer_num = CT.customer_num
WHEN MATCHED THEN UPDATE SET C.phone = CT.phone
WHEN NOT MATCHED THEN INSERT VALUES (CT.customer_num, 
CT.fname, CT.lname, CT.company, CT.address1, CT.address2,
 CT.city, CT.state, CT.zipcode, CT.phone)


DIRECTIVES FOLLOWED:
DIRECTIVES NOT FOLLOWED:
USE_HASH ( ct/BUILD ) 
Join directives not compatible with Outerjoin nesting.

Estimated Cost: 4
Estimated # of Rows Returned: 1
Temporary Files Required For: MERGE

  1) informix.ct: SEQUENTIAL SCAN

  2) informix.c: INDEX PATH

    (1) Index Name: informix. 100_1
        Index Keys: customer_num   (Serial, fragments: ALL)
        Lower Index Filter: 
informix.c.customer_num = informix.ct.customer_num
NESTED LOOP JOIN

merge 语句仅包含一个更新子句时,没有什么限制。在这种情况下,会在源表与目标表之间执行内部联接,并且匹配的行将被更新。在内部联接中,源表及目标表可以处于哈希联接端或者循环嵌套联接中。

利用 merge 语句来执行更新及删除联接

在更新联接中,会基于联接条件在目标表与其他表之间执行更新。类似地,对于删除联接,基于联接条件删除来自目标表的行。Informix 不支持用于更新或者删除联接的特定语法,但是,可通过 merge 语句的特定用法来实现。

如果 merge 语句仅包含 when matched then 子句,那么目标表中匹配的行将被更新或者删除。来自源表的不匹配行将被忽略。通过仅执行更新或者删除,将完成与更新或者删除联接相同的功能。清单 18 展示了如何在源表与目标表之间执行内部联接,对此类可用计划没有限制。

清单 18. 更新与删除联接
MERGE {+ FULL(customer) } INTO customer as C
USING customer_trans as CT
ON C.customer_num = CT.customer_num
WHEN MATCHED THEN UPDATE SET C.phone = CT.phone
WHEN NOT MATCHED THEN INSERT VALUES 
(CT.customer_num, CT.fname, CT.lname, CT.company, 
CT.address1, CT.address2, CT.city, CT.state, CT.zipcode, CT.phone)
~
QUERY: 
------
MERGE {+ FULL(customer) } INTO customer as C
USING customer_trans as CT
ON C.customer_num = CT.customer_num
WHEN MATCHED THEN UPDATE SET C.phone = CT.phone
WHEN NOT MATCHED THEN INSERT VALUES (CT.customer_num, 
CT.fname, CT.lname, CT.company,
CT.address1, CT.address2, CT.city, 
CT.state, CT.zipcode, CT.phone)


DIRECTIVES FOLLOWED:
FULL ( customer )
DIRECTIVES NOT FOLLOWED:

Estimated Cost: 4
Estimated # of Rows Returned: 1

  1) informix.ct: SEQUENTIAL SCAN

  2) informix.c: SEQUENTIAL SCAN


DYNAMIC HASH JOIN
    Dynamic Hash Filters: informix.c.customer_num = 
informix.ct.customer_num

清单 19 展示了删除联接。在本例中,merge 语句指定了如下内容:

  • Stock 作为目标表
  • Manufact 作为源表
  • on 子句中的联接条件
  • 删除语句用于删除制造商代码错误的 stock 行
清单 19. merge 与 delete 联接
MERGE INTO stock 
USING manufact
ON stock.manu_code != manufact.manu_code
WHEN MATCHED THEN DELETE;

从扩展并行服务器中迁移更新以及删除联接

扩展并行服务器(XPS)使得用户为更新特定列而在表上采用联接。用户可在 set 子句中使用 from 中列出的 、来自任何表的列,来为需要更新的列和行指定值,如清单 20 所示。

清单 20. XPS 更新联接
UPDATE stock SET 
stock.description = stock.description||manufact.manu_code 
FROM stock, manufact 
WHERE stock.manu_code = manufact.manu_code;

您可利用 merge 语句来获取类似的结果,如清单 21 所示。

清单 21. 采用 merge 语句的 Informix 服务器更新联接
MERGE INTO stock 
USING manufact
ON stock.manu_code = manufact.manu_code
WHEN MATCHED THEN UPDATE SET 
stock.description = stock.description||manufact.manu_code ;

清单 22 展示了另一个 XPS 更新联接示例。

清单 22. 另一个 XPS 更新联接
UPDATE stock SET 
stock.description = stock.description||manufact.manu_code  
FROM stock, manufact, items 
WHERE stock.manu_code = manufact.manu_code and 
items.manu_code = stock.manu_code ;

在 Informix 服务器中利用 merge 语句获取与清单 22 中的 XPS 更新联接相同结果的等效方法如清单 23 所示。

清单 23. 在 IDS 中利用与更新联接等效的 merge 语句
MERGE INTO stock 
USING (select manufact.manu_code as manu_code, 
FROM items, manufact 
WHERE items.manu_code = manufact.manu_code) source
ON stock.manu_code = source.manu_code
WHEN MATCHED THEN SET 
stock.description = stock.description||source.manu_code ;

类似地,对于在 XPS 中的 delete 语句,您可基于包含在一个或多个表中的信息,来从表中删除信息。您可指定 using 关键字,或者第二个 from 关键字来引入您想用 where 子句来进行联接的表清单,如清单 24 所示。

清单 24. XPS 删除联接
DELETE FROM stock  USING stockdiscount, stock WHERE
 stockdiscount.unit_discount < 5 and 
stock_discount.stock_num = stock.stock_num;

在 Informix 服务器中利用 merge 语句获取与清单 24 中的 XPS 删除联接相同结果的等效方法如清单 25所示。

清单 25. 在 IDS 中利用与删除联接等效的 merge 语句
MERGE INTO stock 
USING (select stock_num from stockdiscount where 
unit_discount < 5) source
ON stock.stock_num = source.stock_num
WHEN MATCHED THEN DELETE;

记住额外限制

merge 语句执行具有特定限制,如果违反,将会出错。以下是一些限制,假设目标表中将有数据被合并。

  • 目标不能够是与目标表的表结构相同的类型表。
  • 目标表不能是虚拟表接口(VTI)表。
  • 目标不能存在于远程 IDS 实例的数据库中。
  • 目标不能是系统目录表或者外部表。
  • 目标不能是定义了 instead of 触发器的视图。
  • 目标不能是只读视图。
  • 目标不能是 pseudo-table,其为系统数据库中的内存驻留对象,比如 sysmaster 或者 sysadmin 数据库。
  • 目标不能是相同 merge 语句中的子查询数据源,包括以下任何子句的子查询:
    • on
    • set
    • values
    • merge 语句中的 select 语句的任何子句

优化性能

merge 语句完全在服务器中执行, 因此能够比执行相同任务的其他方法性能更好。然而,如果性能没有获得满足,您可捕获 explain 输出,来查找可能的原因。可以利用访问方法来扫描源及目标表,以及执行联接所采用的联接方法可能是不正确的。

如果性能不满足,想选择替代品,指令将是个低成本方法。试着指定指令,来变更访问方法或者联接方法。如果所采用的联接方法是循环嵌套,指定指令 use_hash ,以便选择哈希联接。

为进行说明,客户报告的特定 merge 语句(具有 22000 个源表行和 9 百万个目标表行)执行了大约 4 小时才完成。explain 输出展示了 IDS 正采用哈希联接计划。通过应用指令,并强制 IDS 采用循环嵌套计划(目标表带有索引),仅在 1 分钟内就完成了查询。在大多数情况下,IDS 优化器尝试采用正确的计划。如果遇到优化器生成次级优化计划,指令可被用于调整替代计划的响应时间,来选择正确的计划。

尝试在源表及目标表上生成索引,这样就可以选择不同的计划。

尝试利用完整表扫描或者索引来扫描特定表。

结束语

merge 语句是在基于数据仓库的系统中非常便利的方法。您可很方便地将应用逻辑变更为简单的 merge 语句,来获取相同的结果。本文描述了 merge 语句的基本应用,并描述 Informix 服务器的其他特性,包括约束、触发器、视图以及审计。


相关主题

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=656298
ArticleTitle=在 Informix Dynamic Server 中使用 MERGE 语句
publish-date=05032011