Index ANDing access requests

The IXAND access request element can be used to specify that the optimizer is to use the index ANDing data access method to access a local table. It is defined by the complex type indexAndingType.

The IXAND access request element can be used to specify that the optimizer is to use the index ANDing data access method to access a local table. It is defined by the complex type indexAndingType.
XML Schema

   <xs:complexType name="indexAndingType">
      <xs:complexContent>
         <xs:extension base="extendedAccessType">
            <xs:sequence minOccurs="0">
               <xs:element name="NLJOIN" type="nestedLoopJoinType" minOccurs="1"
                 maxOccurs="unbounded"/>
            </xs:sequence>
            <xs:attribute name="STARJOIN" type="boolType" use="optional"/>
         </xs:extension>
      </xs:complexContent>
   </xs:complexType>

Description

The complex type indexAndingType is an extension of extendedAccessType. When the STARJOIN attribute and NLJOIN elements are not specified, indexAndingType becomes a simple extension of extendedAccessType. The extendedAccessType type extends the abstract type accessType by adding an optional INDEX attribute, optional INDEX sub-elements, an optional TYPE attribute, and an optional ALLINDEXES attribute. The INDEX attribute can be used to specify the first index that is to be used in the index ANDing operation. If the INDEX attribute is used, the optimizer chooses additional indexes and the access sequence in a cost-based fashion. The INDEX sub-elements can be used to specify the exact set of indexes and access sequence. The order in which the INDEX sub-elements appear indicates the order in which the individual index scans should be performed. The specification of INDEX sub-elements supersedes the specification of the INDEX attribute.
  • If no indexes are specified, the optimizer chooses both the indexes and the access sequence in a cost-based fashion.
  • If indexes are specified using either the attribute or sub-elements, these indexes must be defined on the table that is identified by the TABLE or TABID attribute.
  • If there are no indexes defined on the table, the access request is ignored and an error is returned.

The TYPE attribute, whose only supported value is XMLINDEX, indicates to the optimizer that the table must be accessed using one or more indexes over XML data.

The optional INDEX attribute can be used to specify an XML index name only if the TYPE attribute has a value of XMLINDEX. A relational index can be specified in the optional INDEX attribute regardless of the TYPE attribute specification. The specified index is used by the optimizer as the leading index of an IXAND plan. The optimizer will add more indexes to the IXAND plan in a cost-based fashion.

The optional INDEX element can be used to specify two or more names of indexes over XML data as index elements only if the TYPE attribute has a value of XMLINDEX. Relational indexes can be specified in the optional INDEX elements regardless of the TYPE attribute specification. The specified indexes are used by the optimizer as the indexes of an IXAND plan in the specified order.

If the TYPE attribute is not present, INDEX attributes and INDEX elements are still valid for relational indexes.

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 in an IXAND plan to access the specified table, regardless of cost.

If the TYPE attribute is specified, but neither INDEX attribute, INDEX element, nor ALLINDEXES attribute is specified, the optimizer will choose an IXAND plan with at least one index over XML data. Other indexes in the plan can be either relational indexes or indexes over XML data. The order and choice of indexes is determined by the optimizer in a cost-based fashion.

Block indexes must appear before record indexes in an index ANDing access request. If this requirement is not met, an error is returned. The index ANDing access method requires that at least one predicate is able to be indexed for each index. If index ANDing is not eligible because the required predicate does not exist, the access request is ignored and an error is returned. If the index ANDing data access method is not in the search space that is in effect for the statement, the access request is ignored and an error is returned.

You can use the IXAND access request element to request a star join index ANDing plan. The optional STARJOIN attribute on the IXAND element specifies that the IXAND is for a star join index ANDing plan. NLJOINs can be sub-elements of the IXAND, and must be properly constructed star join semi-joins. STARJOIN="FALSE" specifies a request for a regular base access index ANDing plan. STARJOIN="TRUE" specifies a request for a star join index ANDing plan. The default value is determined by context: If the IXAND has one or more semi-join child elements, the default is TRUE; otherwise, the default is FALSE. If STARJOIN="TRUE" is specified:
  • The INDEX, TYPE, and ALLINDEXES attributes cannot be specified
  • INDEX elements cannot be specified
If NLJOIN elements are specified:
  • The INDEX, TYPE, and ALLINDEXES attributes cannot be specified
  • INDEX elements cannot be specified
  • The only supported value for the STARJOIN attribute is TRUE
The following example illustrates an index ANDing access request:
SQL statement:

   select s.s_name, s.s_address, s.s_phone, s.s_comment
     from "Samp".parts, "Samp".suppliers s, "Samp".partsupp ps
     where p_partkey = ps.ps_partkey and
       s.s_suppkey = ps.ps_suppkey and
       p_size = 39 and
       p_type = 'BRASS' and
       s.s_nation in ('MOROCCO', 'SPAIN') and
       ps.ps_supplycost = (select min(ps1.ps_supplycost)
                             from "Samp".partsupp ps1, "Samp".suppliers s1
                             where "Samp".parts.p_partkey = ps1.ps_partkey and
                               s1.s_suppkey = ps1.ps_suppkey and
                               s1.s_nation = s.s_nation)
     order by s.s_name
     optimize for 1 row

Optimization guideline:

   <OPTGUIDELINES>
     <IXAND TABLE='"Samp".PARTS' FIRST='TRUE'>
       <INDEX IXNAME='ISIZE'/>
       <INDEX IXNAME='ITYPE'/>
     </IXAND>
   </OPTGUIDELINES>
The index ANDing request specifies that the PARTS table in the main subselect is to be satisfied using an index ANDing data access method. The first index scan will use the ISIZE index, and the second index scan will use the ITYPE index. The indexes are specified by the IXNAME attribute of the INDEX element. The FIRST attribute setting specifies that the PARTS table is to be the first table in the join sequence with the SUPPLIERS, PARTSUPP, and derived tables in the same FROM clause.
The following example illustrates a star join index ANDing guideline that specifies the first semi-join but lets the optimizer choose the remaining ones. It also lets the optimizer choose the specific access method for the outer table and the index for the inner table in the specified semi-join.
   <IXAND TABLE="F">
     <NLJOIN>
       <ACCESS TABLE="D1"/>
       <IXSCAN TABLE="F"/>
     </NLJOIN>
   </IXAND>
The following guideline specifies all of the semi-joins, including details, leaving the optimizer with no choices for the plan at and after the IXAND.
   <IXAND TABLE="F" STARJOIN="TRUE">
     <NLJOIN>
       <TBSCAN TABLE="D1"/>
       <IXSCAN TABLE="F" INDEX="FX1"/>
     </NLJOIN>
     <NLJOIN>
       <TBSCAN TABLE="D4"/>
       <IXSCAN TABLE="F" INDEX="FX4"/>
     </NLJOIN>
     <NLJOIN>
       <TBSCAN TABLE="D3"/>
       <IXSCAN TABLE="F" INDEX="FX3"/>
     </NLJOIN>
   </IXAND>