Examples of optimization guidelines with XML data
Example optimization profiles contain general request, access method, and join order guidelines to control how optimization is performed on queries that access XML data.
For information about optimization guidelines and using optimization profiles, see the related links at the end of this topic.
A guideline to move XML documents as references
In the following
optimization profile, the DPFXMLMOVEMENT general request element in
the guideline specifies that references to XML documents are moved
through the TQ operator in the access plan.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<STMTPROFILE ID="Security Tables">
<STMTKEY SCHEMA="ST">
SELECT *
FROM security
WHERE XMLEXISTS('$SDOC/Security/SecurityInfo
/StockInfo[Industry= "OfficeSupplies"]')
</STMTKEY>
<OPTGUIDELINES>
<DPFXMLMOVEMENT VALUE="REFERENCE"/>
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
A guideline to use a specific index over XML data
In
the following optimization profile, the XISCAN access element in the
guideline specifies that the table SECURITY should be accessed using
the index SEC_INDUSTRY. If the XISCAN element did not specify an index
name with the INDEX attribute, the optimizer accesses the table SECURITY
using the index over XML data with the least cost.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<STMTPROFILE ID="Security Tables">
<STMTKEY SCHEMA="ST">
SELECT *
FROM security
WHERE XMLEXISTS('$SDOC/Security/SecurityInfo
/StockInfo[Industry= "OfficeSupplies"]')
</STMTKEY>
<OPTGUIDELINES>
<XISCAN TABLE='SECURITY' INDEX='SEC_INDUSTRY'/>
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
A guideline to use indexes over XML data with XANDOR access
In
the guideline in the following optimization profile, the XANDOR element
specifies that the table SECURITY should be accessed using an XANDOR
plan of all applicable indexes over XML data. Relational indexes are
not used because a relational index cannot be used by an XANDOR plan.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<STMTPROFILE ID="Security Tables by Date">
<STMTKEY SCHEMA="STBD">
SELECT *
FROM security
WHERE trans_date = CURRENT DATE
AND XMLEXISTS('$SDOC/Security/SecurityInfo
/StockInfo[Industry= "Software"]')
AND XMLEXISTS('$SDOC/Security/Symbol[.="IBM"]')
</STMTKEY>
<OPTGUIDELINES>
<XANDOR TABLE='SECURITY' />
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
A guideline to use specified multiple indexes over XML data with IXAND
In the following optimization profile,
the IXAND element in the optimization guideline specifies that the
table SECURITY should be accessed using two indexes over XML data,
SEC_INDUSTRY and SEC_SYMBOL. The optimizer generates an IXAND plan
with the two indexes as legs of the IXAND plan in the listed order.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<STMTPROFILE ID="Security Tables by Date">
<STMTKEY SCHEMA="STBD">
SELECT *
FROM security
WHERE trans_date = CURRENT DATE
AND XMLEXISTS('$SDOC/Security/SecurityInfo
/StockInfo[Industry= "Software"]')
AND XMLEXISTS('$SDOC/Security/Symbol[.="IBM"]')
</STMTKEY>
<OPTGUIDELINES>
<IXAND TABLE='SECURITY' TYPE='XMLINDEX'>
<INDEX IXNAME='SEC_INDUSTRY'/>
<INDEX IXNAME='SEC_SYMBOL'/>
</IXAND>
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
A guideline to use all indexes over XML data with IXAND
In the following optimization profile, the IXAND element
in the optimization guideline specifies that the table SECURITY should
be accessed using all applicable relational indexes and indexes over
XML data. Assuming there is a relational index on TRANS_DATE and indexes
over XML data on SEC_INDUSTRY and SEC_SYMBOL, all three indexes are
ANDed together in an order chosen by the optimizer.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<STMTPROFILE ID="Security Tables by Date">
<STMTKEY SCHEMA="STBD">
SELECT *
FROM security
WHERE trans_date = CURRENT DATE
AND XMLEXISTS('$SDOC/Security/SecurityInfo
/StockInfo[Industry= "Software"]')
AND XMLEXISTS('$SDOC/Security/Symbol[.="IBM"]')
</STMTKEY>
<OPTGUIDELINES>
<IXAND TABLE='SECURITY' TYPE='XMLINDEX' ALLINDEXES='TRUE' />
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
A guideline to use a specific leading index over XML data with IXAND
In the following optimization profile, the
IXAND element in the optimization guideline specifies that the table
SECURITY should be accessed using an IXAND plan and the index over
XML data SEC_INDUSTRY must be the first index in the IXAND. The optimizer
picks additional indexes for the IXAND plan in a cost-based fashion.
If a relational index is available on the column TRANS_DATE and an
index over XML data is available on the path SYMBOL, one or both of
these indexes appear as additional legs of the IXAND plan if deemed
beneficial by the optimizer.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<STMTPROFILE ID="Security Tables by Date">
<STMTKEY SCHEMA="STBD">
SELECT *
FROM security
WHERE trans_date = CURRENT DATE
AND XMLEXISTS('$SDOC/Security/SecurityInfo
/StockInfo[Industry= "Software"]')
AND XMLEXISTS('$SDOC/Security/Symbol[.="IBM"]')
</STMTKEY>
<OPTGUIDELINES>
<IXAND TABLE='SECURITY' TYPE='XMLINDEX' INDEX='SEC_INDUSTRY' />
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
A guideline to use some XML index access
In
the following optimization profile, the guideline specifies only that
some index over XML data be used to access the table SECURITY. The
optimizer uses a XISCAN, IXAND, XANDOR, or IXOR plan using a cost-based
analysis.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<STMTPROFILE ID="Security Tables">
<STMTKEY SCHEMA="ST">
SELECT *
FROM security
WHERE XMLEXISTS('$SDOC/Security/SecurityInfo
/StockInfo[Industry= "OfficeSupplies"]')
</STMTKEY>
<OPTGUIDELINES>
<ACCESS TABLE='SECURITY' TYPE='XMLINDEX' />
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
A guideline to use all applicable indexes over XML data access
In the following optimization profile, the guideline
specifies that all applicable indexes on the SECURITY table are used.
The choice of the method is left to the optimizer. Assuming two indexes
over XML data, SEC_INDUSTRY and SEC_SYMBOL have been created that
match the two predicates in XMLEXISTS. The optimizer has the choice
to either use an XANDOR or IXAND plan. The optimizer chooses between
the two access plans using a cost-based analysis.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<STMTPROFILE ID="Security Tables 2">
<STMTKEY SCHEMA="ST2">
SELECT *
FROM security
WHERE XMLEXISTS('$SDOC/Security/SecurityInfo
/StockInfo[Industry= "Software"]')
AND XMLEXISTS('$SDOC/Security/Symbol[.="IBM"]')
</STMTKEY>
<OPTGUIDELINES>
<ACCESS TABLE='SECURITY' TYPE='XMLINDEX' ALLINDEXES='TRUE' />
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
A guideline specifying index over XML data access using a specific index
In the following optimization profile,
the guideline specifies that the table SECURITY is accessed using
at least the SEC_INDUSTRY index. The optimizer picks one of the following
access plans using a cost-based analysis:
- An XISCAN plan using SEC_INDUSTRY index.
- An IXAND plan with the given index as the first leg of the IXAND plan. The optimizer might use more indexes in the IXAND plan based on a cost-based analysis. In the example, if a relational index is available on the column TRANS_DATE, that index appears as an additional leg of the IXAND plan if it was deemed beneficial by the optimizer.
- A XANDOR plan with the given index and other applicable indexes over XML data.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<STMTPROFILE ID="Security Tables by Date">
<STMTKEY SCHEMA="STBD">
SELECT *
FROM security
WHERE trans_date = CURRENT DATE
AND XMLEXISTS('$SDOC/Security/SecurityInfo
/StockInfo[Industry= "Software"]')
AND XMLEXISTS('$SDOC/Security/Symbol[.="IBM"]')
</STMTKEY>
<OPTGUIDELINES>
<ACCESS TABLE='SECURITY' TYPE='XMLINDEX' INDEX='SEC_INDUSTRY' />
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
The following optimization guideline specifies
using one of the following access plans using a cost-based analysis:
- Use an IXAND plan with the SEC_INDUSTRY and SEC_SYMBOL indexes in the specified order.
- Use XANDOR plan with all applicable XML indexes.
<OPTGUIDELINES>
<ACCESS TABLE='SECURITY' TYPE='XMLINDEX'>
<INDEX IXNAME='SEC_INDUSTRY'/>
<INDEX IXNAME='SEC_SYMBOL'/>
</ACCESS>
</OPTGUIDELINES>
A guideline controlling join order and specifying index over XML data access
In the following optimization
profile, the optimization guidelines contain two elements. The first
guideline element specifies that the table CUSTACC must appear as
the outermost table when the tables in the FROM clause are joined
and some index over XML data access must be used to access the table
CUSTACC. The second guideline element specifies that the table ORDER
should be accessed using a XANDOR plan. The optimizer uses all applicable
indexes over XML data in the XANDOR plan. The order of indexes is
chosen by the optimizer.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<STMTPROFILE ID="Order and Security Tables">
<STMTKEY SCHEMA="OST">
SELECT ordqty, orddate, ordid, security, lasttrade
FROM order, security, custacc,
XMLTABLE('$ODOC/FIXML/Order'
COLUMNS ordid VARCHAR(10) PATH '@ID',
orddate date PATH '@TrdDt',
ordqty float PATH 'OrdQty/@Qty') AS T1,
XMLTABLE(' $SDOC/Security'
COLUMNS security varchar(50) PATH 'Name',
lasttrade float PATH 'Price/LastTrade') AS T2
WHERE XMLEXISTS('
$SDOC/Security[Symbol/fn:string(.)
= $ODOC/FIXML/Order/Instrmt/@Sym/fn:string(.)]')
and XMLEXISTS(
'$ODOC/FIXML/Order[@Acct/fn:string(.)
= $CADOC/Customer/Accounts/Account/@id/fn:string(.)]')
and XMLEXISTS('$CADOC/Customer[@id = 1011]')
ORDER BY ordqty desc
</STMTKEY>
<OPTGUIDELINES>
<ACCESS TABLE='CUSTACC' TYPE='XMLINDEX' FIRST='TRUE' />
<XANDOR TABLE='ORDER' />
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
Guidelines for an XQuery expression
The
following optimization profile contains an XQuery expression that
returns stock information of companies that deal in Software from
the table SECURITY1. The guideline specifies that the table should
be accessed using the single XML index XI1.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<STMTPROFILE ID="Security Tables by Industry">
<STMTKEY SCHEMA="STBD">
xquery
for $sinfo1 in db2-fn:xmlcolumn("SECURITY1.SDOC")/Security/SecurityInfo
/StockInfo[Industry="Software"]
return $sinfo1
</STMTKEY>
<OPTGUIDELINES>
<XISCAN TABLE='SECURITY1' INDEX='XI1'/>
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
The following optimization profile contains an XQuery expression that returns stock information of a company that deals in Software as well as Electronics.
The guideline specifies
that table SECURITY2 should be the outer table in the join and the
index over XML data XI2 should be used to access the SECURITY2 table.
The guideline also specifies that the table SECURITY1 should be the
inner table of the join and the index over XML data XI1 should be
used to access the SECURITY1 table.
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.7.0.0">
<STMTPROFILE ID="Security Tables by Industry">
<STMTKEY SCHEMA="STBD">
<![CDATA[ xquery
for $sinfo1 in db2-fn:xmlcolumn("SECURITY1.SDOC")/Security
/SecurityInfo/StockInfo[Industry="Software"]
for $sinfo2 in db2-fn:xmlcolumn("SECURITY2.SDOC")/Security
/SecurityInfo/StockInfo[Industry="Electronics"]
where $sinfo1 = $sinfo2
return <stock> {$sinfo1} </stock> ]]>
</STMTKEY>
<OPTGUIDELINES>
<JOIN>
<ACCESS TABLE='SECURITY2' TYPE='XMLINDEX' INDEX='XI2'/>
<ACCESS TABLE='SECURITY1' TYPE='XMLINDEX' INDEX='XI1'/>
</JOIN>
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>
A CDATA section starting, with <![CDATA[ and ending
with ]]>, encloses the statement key in the STMTKEY element because
the statement key contains the special XML characters < and >.
The profile parser ignores the XML tags in the CDATA section, but
the optimizer still uses the entire statement key to match a statement
profile to a corresponding statement in an application.