Any access requests

The ACCESS access request element can be used to specify that the optimizer is to choose an appropriate method for accessing a table, based on cost, and must be used when referencing a derived table. A derived table is the result of another subselect. This access request element is defined by the complex type anyAccessType.

XML Schema

   <xs:complexType name="anyAccessType">
      <xs:complexContent>
         <xs:extension base="extendedAccessType"/>
      </xs:complexContent>
   </xs:complexType>

Description

The complex type anyAccessType is a simple extension of the abstract type extendedAccessType. No new elements or attributes are added.

The TYPE attribute, whose only supported value is XMLINDEX, indicates to the optimizer that the table must be accessed using one of the XML index access methods, such as IXAND, IXOR, XANDOR, or XISCAN. If this attribute is not specified, the optimizer makes a cost-based decision when selecting an access plan for the specified table.

The optional INDEX attribute can be used to specify an index name only if the TYPE attribute has a value of XMLINDEX. If this attribute is specified, the optimizer might choose one of the following plans:
  • An XISCAN plan using the specified index over XML data
  • An XANDOR plan, such that the specified index over XML data is one of the indexes under XANDOR; the optimizer will use all applicable indexes over XML data in the XANDOR plan
  • An IXAND plan, such that the specified index is the leading index of IXAND; the optimizer will add more indexes to the IXAND plan in a cost-based fashion
  • A cost-based IXOR plan
The optional INDEX element can be used to specify two or more names of indexes as index elements only if the TYPE attribute has a value of XMLINDEX. If this element is specified, the optimizer might choose one of the following plans:
  • An XANDOR plan, such that the specified indexes over XML data appear under XANDOR; the optimizer will use all applicable indexes over XML data in the XANDOR plan
  • An IXAND plan, such that the specified indexes are the indexes of IXAND, in the specified order
  • A cost-based IXOR plan
If the INDEX attribute and the INDEX element are both specified, the INDEX attribute is ignored.
The optional ALLINDEXES attribute, whose only supported value is TRUE, can only be specified if the TYPE attribute has a value of XMLINDEX. If this attribute is specified, the optimizer must use all applicable relational indexes and indexes over XML data to access the specified table, regardless of cost. The optimizer chooses one of the following plans:
  • An XANDOR plan with all applicable indexes over XML data appearing under the XANDOR operator
  • An IXAND plan with all applicable relational indexes and indexes over XML data appearing under the IXAND operator
  • An IXOR plan
  • An XISCAN plan if only a single index is defined on the table and that index is of type XML

Examples

The following guideline is an example of an any access request:
   <OPTGUIDELINES>
     <HSJOIN>
       <ACCESS TABLE='S1'/>
       <IXSCAN TABLE='PS1'/>
     </HSJOIN>
   </OPTGUIDELINES>
The following example shows an ACCESS guideline specifying that some XML index access to the SECURITY table should be used. The optimizer might pick any XML index plan, such as an XISCAN, IXAND, XANDOR, or IXOR plan.
SELECT * FROM security
  WHERE XMLEXISTS('$SDOC/Security/SecurityInformation/
    StockInformation[Industry= "OfficeSupplies"]')

<OPTGUIDELINES>
  <ACCESS TABLE='SECURITY' TYPE='XMLINDEX'/>
</OPTGUIDELINES>
The following example shows an ACCESS guideline specifying that all possible index access to the SECURITY table should be used. The choice of method is left to the optimizer. Assume that two XML indexes, SEC_INDUSTRY and SEC_SYMBOL, match the two XML predicates. The optimizer chooses either the XANDOR or the IXAND access method using a cost-based decision.
SELECT * FROM security
  WHERE XMLEXISTS('$SDOC/Security/SecurityInformation/
      StockInformation[Industry= "Software"]') AND
    XMLEXISTS('$SDOC/Security/Symbol[.="IBM"]')

<OPTGUIDELINES>
  <ACCESS TABLE='SECURITY' TYPE='XMLINDEX' ALLINDEXES='TRUE'/>
</OPTGUIDELINES>
The following example shows an ACCESS guideline specifying that the SECURITY table should be accessed using at least the SEC_INDUSTRY XML index. The optimizer chooses one of the following access plans in a cost-based fashion:
  • An XISCAN plan using the SEC_INDUSTRY XML index
  • An IXAND plan with the SEC_INDUSTRY index as the first leg of the IXAND. The optimizer is free to use more relational or XML indexes in the IXAND plan following cost-based analysis. If a relational index were available on the TRANS_DATE column, for example, that index might appear as an additional leg of the IXAND if that were deemed to be beneficial by the optimizer.
  • A XANDOR plan using the SEC_INDUSTRY index and other applicable XML indexes
SELECT * FROM security
  WHERE trans_date = CURRENT DATE AND
    XMLEXISTS('$SDOC/Security/SecurityInformation/
      StockInformation[Industry= "Software"]') AND
    XMLEXISTS('$SDOC/Security/Symbol[.="IBM"]')

<OPTGUIDELINES>
  <ACCESS TABLE='SECURITY' TYPE='XMLINDEX' INDEX='SEC_INDUSTRY'/>
</OPTGUIDELINES>