对 pureXML 应用程序应用 DB2 优化准则
2010-03-22 00:00:00 来源:WEB开发网根据表名识别表
考虑以下的简单查询:
SELECT * FROM security WHERE XMLEXISTS('$SDOC/Security/SecurityInformation/StockInformation[Industry= "OfficeSupplies"]')
本例中的公开的表名为 “SECURITY”。该查询的准则应当指定这个公开的表名。例如,一个使用索引 “SEC_INDUSTRY” 的 XML 索引访问可以这样指定:
<XISCAN TABLE='SECURITY' INDEX='SEC_INDUSTRY'/>
现在考虑下面的查询,将对表名使用 “sec” 作为别名:
SELECT * FROM security sec WHERE XMLEXISTS('$SDOC/Security/SecurityInformation/StockInformation[Industry= "OfficeSupplies"]')
公开的表名为 “SEC”,并且需要修改索引准则,使其引用这个公开的表名,如下所示:
<XISCAN TABLE='SEC' INDEX='SEC_INDUSTRY'/>
根据 TABID 识别表
当查询被提交到 DB2 后,将以一种最适合优化的方式重写它。重写后的查询也称为 “Optimized SQL”,它将在使用 db2exfmt 命令解释语句时出现。优化后的 SQL 位于 db2exfmt 输出的 “Optimized Statement” 部分。例如,对于清单 4 中的查询:
清单 4. 查询
SELECT *
FROM order, security
WHERE order.security_id = security.security_id
AND XMLEXISTS('$SDOC/Security/Symbol[.="IBM"]');
优化后的语句可能类似如下所示:
清单 5. 优化后的语句
SELECT Q3.ID AS "ID", Q3.SECURITY_ID AS "SECURITY_ID", Q3.ODOC AS "ODOC", Q2.ID
AS "ID", Q2.SECURITY_ID AS "SECURITY_ID", Q2.SDOC AS "SDOC"
FROM $INTERNAL_FOR$ ((TABLE ($INTERNAL_XPATH$
('(($INTERNAL_XMLTOXML_NIEO$(Q2.SDOC))[
$INTERNAL_EBV_BOOLEAN$(Security/Symbol[(. = "IBM")])])(:-->$C0:)')))
AS Q1), TPOX.SECURITY AS Q2, TPOX.ORDER AS Q3
WHERE (Q3.SECURITY_ID = Q2.SECURITY_ID)
更多精彩
赞助商链接