调优联邦查询

Server: MIDASP (DB2/UDB 7.1)
SQL Statement:
SELECT A0."DSSSCA-NO", A0."AC-EOM-AST-AM", A0."DSSAS-TY-CD", A1."MLP-ST- CD", A1."IMLP-1ST-NM", A1."IMLP-LST-NM"
FROM "MIDS"."TBACCT" A0, "MIDS"."TBACCT-HLDR" A1
WHERE (1000000 < A0."AC-EOM-AST-AM") AND (A1."MLP-ST-CD" ='NY') AND (A0."DSSSCA-NO" =A1."DSSSCA-NO") FOR READ ONLY
Nicknames Referenced:
MIDS.TBACCT-HLDR ID = 33125
Base = MIDS.TBACCT-HLDR
MIDS.TBACCT ID = 33124
Base = MIDS.TBACCT

如果将两个远程表（a.dsssca-no =ah.dsssca-no）之间的连接谓词去掉，那么将得到一个笛卡儿乘积。如果在做出这样的更改之后发出 EXPLAIN 命令，则 DB2 II 优化器会把数据取到它的服务器上，并在本地执行连接操作，而不是把连接发送到远程服务器，这样可以减少网络传输。清单 4 展示了这个 SQL 例子，并给出了与之相关的 Explain 输出的一部分（完整的输出在网上有提供）。

清单 4. 重写的查询，将数据取到 DB2 II 服务器进行本地处理

| Ship Distributed Subquery #1
| | #Columns = 3
| Nested Loop Join
| | Access Table Name = HJG.IITBL ID = 62,24
| | | #Columns = 1
| | | Relation Scan
| | | | Prefetch: Eligible
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Predicate(s)
| | | | #Predicates = 1
| | | | Process Probe Table for Hash Join
Return Data to Application
| #Columns = 6
Distributed Substatement #1:
Server: MIDASP (DB2/UDB 7.1)
SQL Statement:
SELECT A0."AC_EOM_AST_AM", A0."DSSSCA_NO",
A0."DSSAS_TY_CD"
FROM "MIDS"."TBACCT" A0
WHERE (1000000 < A0."AC_EOM_AST_AM")
FOR READ ONLY
Nicknames Referenced:
MIDS.TBACCT ID = 33124
Base = MIDS.TBACCT
#Output Columns = 3
Distributed Substatement #2:
Server: MIDASP (DB2/UDB 7.1)
SQL Statement:
SELECT A0."MLP_ST_CD", A0."IMLP_1ST_NM",
A0."IMLP_LST_NM"
FROM "MIDS"."TBACCT_HLDR" A0
WHERE (A0."MLP_ST_CD" ='NY')
FOR READ ONLY
Nicknames Referenced:
MIDS.TBACCT_HLDR ID = 33125
Base = MIDS.TBACCT_HLDR
#Output Columns = 3
End of section