DB2 - Problembeschreibung
Problem IC64326 | Status: Geschlossen |
PROJECT OUT UNNEEDED XML COLUMNS AND ENABLE INDEX ACCESS FOR XMLTABLE JOIN PREDICATE | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
There are two issues that we solved: 1. Eliminate unnecessary XML columns that are not used in any expression together with the corresponding operations/xpath that produces them. Typical customer scenario: CREATE VIEW CUSTSUPERVIEW AS SELECT T.* FROM CUSTACC, XMLTABLE(' declare namespace c=http://tpox-benchmark.com/custacc; $CADOC/c:Customer/c:Accounts/c:Account/c:Holdings/c:Position' COLUMNS HOLDACCTID VARCHAR(12) PATH '../../@id', HOLDCUSTID DOUBLE PATH '../../../../@id', HOLDSYM VARCHAR(20) PATH '*:Symbol', HOLDNAME VARCHAR(50) PATH '*:Name', HOLDTYPE VARCHAR(20) PATH '*:Type', HOLDQUANTITY FLOAT PATH '*:Quantity', ACCTID VARCHAR(12) PATH '../../@id', ACCTTITLE VARCHAR(50) PATH '../../*:AccountTitle', ACCTCURRENCY VARCHAR(5) PATH '../../*:Currency', ACCTONLINEBALANCE INTEGER PATH '../../*:Balance/*:OnlineActualBal', CUSTID DOUBLE PATH '../../../../@id', CUSTSHORTNAME VARCHAR(30) PATH '../../../../*:ShortNames/*:ShortName', CUSTDATEOFBIRTH DATE PATH '../../../../*:DateOfBirth', CUSTGENDER VARCHAR(10) PATH '../../../../*:Gender', CUSTNATIONALITY VARCHAR(30) PATH '../../../../*:Nationality', CUSTCOUNTRY VARCHAR(30) PATH '../../../../*:CountryOfResidence') AS T; select custid from custsuperview; Only the xpath that defines custid column will be accessed. The other columns/xpaths are pruned by DB2 Rewrite component. 2. Enable index usage for relational join predicates that references columns computed by XPath expressions, in particular XMLTABLE columns, by generating equivalent XML join predicates that Optimizer can use during index planning. Consider the following example: SELECT * FROM XMLTABLE('db2-fn:xmlcolumn("TEST1.DATA")' columns col1 double path 'xmldata/a/b') as t1, XMLTABLE('db2-fn:xmlcolumn("TEST2.DATA")' columns col1 double path 'xmldata/a/c', col2 double path 'xmldata/a/d') as t2 WHERE t1.col1 = t2.col1; CREATE INDEX IX1 ON TEST1 (DATA ASC) GENERATE KEY USING XMLPATTERN '/xmldata/a/b' AS SQL double; CREATE INDEX IX2 ON TEST2 (DATA ASC) GENERATE KEY USING XMLPATTERN '/xmldata/a/c' AS SQL double; This work will enable index access IX1 or IX2 for the join predicate t1.col1 = t2.col2. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * XML feature in DB2; all platforms * **************************************************************** * PROBLEM DESCRIPTION: * * There are two issues that we solved: * * * * 1. Eliminate unnecessary XML columns that are not used in * * any * * expression together with the corresponding operations/xpath * * that * * produces them. * * * * Typical customer scenario: * * * * CREATE VIEW CUSTSUPERVIEW AS * * SELECT T.* * * FROM CUSTACC, * * XMLTABLE(' declare namespace * * c=http://tpox-benchmark.com/custacc; * * * * $CADOC/c:Customer/c:Accounts/c:Account/c:Holdings/c:Position' * * * COLUMNS * * HOLDACCTID VARCHAR(12) PATH '../../@id', * * HOLDCUSTID DOUBLE PATH '../../../../@id', * * HOLDSYM VARCHAR(20) PATH '*:Symbol', * * HOLDNAME VARCHAR(50) PATH '*:Name', * * HOLDTYPE VARCHAR(20) PATH '*:Type', * * HOLDQUANTITY FLOAT PATH '*:Quantity', * * ACCTID VARCHAR(12) PATH '../../@id', * * ACCTTITLE VARCHAR(50) PATH '../../*:AccountTitle', * * ACCTCURRENCY VARCHAR(5) PATH '../../*:Currency', * * ACCTONLINEBALANCE INTEGER PATH * * '../../*:Balance/*:OnlineActualBal', * * CUSTID DOUBLE PATH '../../../../@id', * * CUSTSHORTNAME VARCHAR(30) PATH * * '../../../../*:ShortNames/*:ShortName', * * CUSTDATEOFBIRTH DATE PATH * * '../../../../*:DateOfBirth', * * CUSTGENDER VARCHAR(10) PATH '../../../../*:Gender', * * CUSTNATIONALITY VARCHAR(30) PATH * * '../../../../*:Nationality', * * CUSTCOUNTRY VARCHAR(30) PATH * * '../../../../*:CountryOfResidence') AS T; * * * * select custid from custsuperview; * * * * Only the xpath that defines custid column will be accessed. * * The * * other columns/xpaths are pruned by DB2 Rewrite component. * * * * 2. Enable index usage for relational join predicates that * * references columns computed by XPath expressions, in * * particular * * XMLTABLE columns, by generating equivalent XML join * * predicates * * that Optimizer can use during index planning. * * * * Consider the following example: * * * * SELECT * * * FROM XMLTABLE('db2-fn:xmlcolumn("TEST1.DATA")' * * columns col1 double path 'xmldata/a/b') as t1, * * XMLTABLE('db2-fn:xmlcolumn("TEST2.DATA")' * * columns col1 double path 'xmldata/a/c', * * col2 double path 'xmldata/a/d') as t2 * * WHERE t1.col1 = t2.col1; * * * * * * CREATE INDEX IX1 ON TEST1 (DATA ASC) GENERATE KEY USING * * XMLPATTERN '/xmldata/a/b' AS SQL double; * * CREATE INDEX IX2 ON TEST2 (DATA ASC) GENERATE KEY USING * * XMLPATTERN '/xmldata/a/c' AS SQL double; * * * * This work will enable index access IX1 or IX2 for the join * * predicate t1.col1 = t2.col2. * **************************************************************** * RECOMMENDATION: * * N/A * **************************************************************** | |
Local-Fix: | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows | |
Lösung | |
DB2 V97FP1 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 03.11.2009 17.02.2010 17.02.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP1 | |
Problem behoben lt. FixList in der Version | |
9.7.0.1 |