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.
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
- 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.
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
- The INDEX, TYPE, and ALLINDEXES attributes cannot be specified
- INDEX elements cannot be specified
- The only supported value for the STARJOIN attribute is TRUE
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. <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>