Examples of optimization guidelines with XML data

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>

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>

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>

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>

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>

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>

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>

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>

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:

1. An XISCAN plan using SEC_INDUSTRY index.
2. 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.
3. 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>

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>

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 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.