|
Problem IC64326
|
Status: Closed |
PROJECT OUT UNNEEDED XML COLUMNS AND ENABLE INDEX ACCESS FOR XMLTABLE JOIN PREDICATE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| 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. |
| Problem Summary: |
****************************************************************
* 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: |
|
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
DB2 V97FP1 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 03.11.2009
17.02.2010
17.02.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
